Recent Orders

Our Reviews

Sample Papers

How It Works

Get First 2 Pages Of Your Homework Absolutely Free!

Messages

Welcome to TutorsOnSpot.Com!

World's No. 1 Assignment Writing Market

Post Your Homework

Proposals

Post your homework and get free proposals here!

Post Your Homework

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Get Free Quotes Post Your Requirements

Formats applied to text that include shadows

14/10/2021 Client: muhammad11 Deadline: 2 Day

Computer Assignment

e02L_Palms.xlsx
Sheet1
Rosedale Landscape and Garden
Pasadena Facility Palm Tree Inventory
Quantity in Stock Number of Product Types
Queen
Pygmy Date
King
Sago
Quantity in Stock Item #/Category Product Name Retail Price
54 12398-King King Palm 15 Gallon 47.50
74 13189-Queen Queen 1 Gallon 5.00
98 15688-Queen Queen 30" Box 325.00
12 16555-Pygmy Pygmy Date 24" Box 195.00
15 22189-Pygmy Pygmy Date 36" Box 475.00
124 23371-Pygmy Pygmy Date 10" 15.00
143 23677-King King Palm 1 Gallon 5.50
129 25844-King King Palm 36" Box 450.00
76 26787-Sago Sago Palm 1 Gallon 7.50
15 32544-Sago Sago Palm 10" 30.00
41 34793-Sago Sago Palm 17" 155.00
122 37283-Queen Queen 24" Box 135.00
234 43877-King King Palm 5 Gallon 15.50
98 49293-Pygmy Pygmy Date 14" 52.00
66 65213-Sago Sago Palm 14" 65.00
23 76352-Pygmy Pygmy Date 17" 95.00
56 78324-Queen Queen 5 Gallon 15.00
32 98376-Queen Queen 15 Gallon 45.00
Excel_2_PPT.pptx
GO! with Microsoft Excel 2019 Introductory

2019 Edition

Chapter 2

Using Functions, Creating Tables, and Managing Large Workbooks

GO! with Microsoft Excel 2019 Introductory

In this chapter, you will learn how apply Excel functions, create and modify tables, and how to manage workbook with multiple worksheets.

Learning Objectives (1 of 2)

Use Flash Fill and the SUM, AVERAGE, MEDIAN, MIN, and MAX Functions

Move Data, Resolve Error Messages, and Rotate Text

Use COUNTIF and IF Functions and Apply Conditional Formatting

Use Date & Time Functions and Freeze Panes

Create, Sort, and Filter an Excel Table

View, Format, and Print a Large Worksheet

The objectives of this chapter are:

Use Flash Fill and the SUM, AVERAGE, MEDIAN, MIN, and MAX Functions

Move Data, Resolve Error Messages, and Rotate Text

Use COUNTIF and IF Functions and Apply Conditional Formatting

Use Date & Time Functions and Freeze Panes

Create, Sort, and Filter an Excel Table

View, Format, and Print a Large Worksheet

Learning Objectives (2 of 2)

Navigate a Workbook and Rename Worksheets

Enter Dates, Clear Contents, and Clear Formats

Copy and Paste by Using the Paste Options Gallery

Edit and Format Multiple Worksheets at the Same Time

Create a Summary Sheet with Column Sparklines

Format and Print Multiple Worksheets in a Workbook

Additional objectives of this chapter are:

Navigate a Workbook and Rename Worksheets

Enter Dates, Clear Contents, and Clear Formats

Copy and Paste by Using the Paste Options Gallery

Edit and Format Multiple Worksheets at the Same Time

Create a Summary Sheet with Column Sparklines

Format and Print Multiple Worksheets in a Workbook

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (1 of 6)

Flash Fill recognizes a pattern in your data, and then automatically fills in values when you enter examples of the output that you want.

A function is a predefined formula.

Statistical functions include AVERAGE, MEDIAN, MIN, and MAX functions.

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (2 of 6)

The SUM function is a predefined formula that adds all the numbers in a selected range of cells. You can insert the SUM function from the Home tab, from the Formulas tab, or by using the keyboard shortcut, Alt + =.

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (3 of 6)

The More Functions button on the Formulas tab offers many function options, including the AVERAGE function, as shown here.

The AVERAGE function adds a group of values, and then divides the result by the number of values in the group.

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (4 of 6)

The Function Arguments dialog box allows you to enter the arguments for the function. The AVERAGE function, as shown here, needs a range of cells entered.

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (5 of 6)

The MEDIAN function is a statistical function that describes a group of data; it finds the middle value that has as many values above it in the group as are below it.

Defining the arguments—indicating which cells you want to use in the function’s calculation—is done in the Function Arguments dialog box.

Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (6 of 6)

The MIN function determines the smallest value in a selected range of values.

The MAX function determines the largest value in a selected range of values.

Move Data, Resolve Error Messages, and Rotate Text (1 of 2)

When you move a formula, the cell references within the formula do not change, no matter what type of cell reference you use. An easy way to move a range of cells is to use drag and drop.

If you move cells that are not wide enough to display number values, a message is displayed so you can adjust as necessary.

If a cell width is too narrow to display the entire number, Excel displays the #### message.

Move Data, Resolve Error Messages, and Rotate Text (2 of 2)

Text can be rotated in a worksheet. In this example, in cell c6, the font size and color was changed and bold and italic were added. Range C4:C8 was merged and then rotated in the Format Cells dialog box, which is accessed from the shortcut menu.

Use COUNTIF and IF Functions and Apply Conditional Formatting (1 of 7)

Logical function—tests for specific conditions

Criteria—specific conditions that are true or false.

Count function—counts the number of cells in a range containing numbers

COUNTIF function—counts the number of cells within a range that meet the given criteria

A logical function tests for specific conditions.

Criteria refers the specific conditions that are true or false.

The Count function counts the number of cells in a range that contain numbers.

The COUNTIF function is a statistical function that counts the number of cells within a range that meet the given criteria.

Use COUNTIF and IF Functions and Apply Conditional Formatting (2 of 7)

The slide shows the Function Arguments dialog box for the COUNTIF function.

Use COUNTIF and IF Functions and Apply Conditional Formatting (3 of 7)

Logical test—any value or expression evaluated as true or false

IF function—uses a logical test to check whether a condition is met

Returns one value if true

Another value if false

A logical test is any value or expression that is evaluated as being true or false.

The IF function uses a logical test to check whether a condition is met, and then returns one value if true, and another value if false.

Use COUNTIF and IF Functions and Apply Conditional Formatting (4 of 7)

Comparison Operators Symbol Definition
= Equal to
> Greater than
< Less than
<= Greater than or equal to
>= Less than or equal to
<> Not equal to
Copyright © 2020 Pearson All Rights Reserved.

When forming a logical text, comparison operators can be used.

Use COUNTIF and IF Functions and Apply Conditional Formatting (5 of 7)

The Function Arguments dialog box is used to create the IF function.

Use COUNTIF and IF Functions and Apply Conditional Formatting (6 of 7)

A conditional format changes the appearance of a cell based on a condition—a criteria. If the condition is true, the cell is formatted based on that condition; if the condition is false, the cell is not formatted.

A data bar provides a visual cue to the reader about the value of a cell relative to other cells. The length of the data bar represents the value in the cell.

Use COUNTIF and IF Functions and Apply Conditional Formatting (7 of 7)

The Find and Replace feature searches the cells in a worksheet—or in a selected range—for matches, and then replaces each match with a replacement value of your choice.

Use Date & Time Functions and Freeze Panes (1 of 2)

Excel can obtain the date and time from your computer’s calendar and clock, and display this information on your worksheet.

The NOW function retrieves the date and time from your computer’s calendar and clock and inserts the information into the selected cell.

Use Date & Time Functions and Freeze Panes (2 of 2)

The Freeze Panes command enables you to select one or more rows or columns and then freeze or lock them into place.

A pane is a portion of a worksheet window bounded by and separated from other portions by vertical or horizontal bars.

Create, Sort, and Filter an Excel Table (1 of 2)

In order to analyze a group of related data, you convert a range of cells to an Excel table, by selecting the range of cells that make up the table, including the header row, and clicking Table to display the Create Table dialog box.

An Excel table has a series of rows and columns that contains related data that is managed independently from the data in other rows and columns in the worksheet.

Create, Sort, and Filter an Excel Table (2 of 2)

You can sort tables—arrange all the data in a specific order—in ascending or descending order.

You can filter tables—display only a portion of the data based on matching a specific value—to show only the data that meets the criteria that you specify. When you are finished answering questions about the data in a table, you can clear the filters.

A table can have a style applied as shown in this worksheet.

View, Format, and Print a Large Worksheet (1 of 2)

You can magnify or shrink the view of a worksheet on your screen to either zoom in or zoom out. You can also split a worksheet window into panes. If a worksheet is too wide, too long, or both, to print on one page, Excel’s Print Title and Scale to Fit commands can help you create pages that are attractive and easy to read. As you can see above, vertical and horizontal split bars display when you split a window.

View, Format, and Print a Large Worksheet (2 of 2)

Print settings that help you print a professional-looking, large worksheet include:

Center on page command

Landscape orientation

Fit All Columns on One Page command

Print Titles command

Navigate a Workbook and Rename Worksheets

Use multiple worksheets in a workbook to organize data in a logical arrangement. When there is more than one worksheet in a workbook, you can navigate (move) among worksheets by clicking the sheet tabs.

Sheet tabs identify each worksheet in a workbook and display along the lower left edge of the workbook window. Sheet tab colors can also be changed. You can either double-click the sheet name or right-click the sheet name and use the shortcut menu to rename a sheet tab.

Enter Dates, Clear Contents, and Clear Formats (1 of 4)

Dates represent a type of value that you can enter in a cell. When you enter a date, Excel assigns a serial value—a number—to the date. This makes it possible to treat dates like other numbers and use them in calculations. The table shows date formats recognized by Excel.

Enter Dates, Clear Contents, and Clear Formats (2 of 4)

Date Typed As Completed by Excel As:
7/4/15 7/4/2015
7/4/98 7/4/1998
7/4 4-Jul (current year assumed)
7-4 4-Jul (current year assumed)
Jul 4 4-Jul (current year assumed)
Jul 4 4-Jul (current year assumed)
Jul/4 4-Jul (current year assumed)
Jul-4 4-Jul (current year assumed)
July 4,1998 4-Jul-98
July 2012 Jul-12 (first day of month assumed)
July 1998 Jul-98 (first day of month assumed)
Copyright © 2020 Pearson All Rights Reserved.

The table shows how Excel interprets dates.

Enter Dates, Clear Contents, and Clear Formats (3 of 4)

Enter Dates, Clear Contents, and Clear Formats (4 of 4)

A cell has contents—a value or a formula—and a cell may also have one or more formats applied, for example, bold and italic font styles, fill color, font color, and so on.

You can choose to clear—delete—the contents of a cell, the formatting of a cell, or both.

Clearing the contents of a cell deletes the value or formula typed there, but it does not clear formatting applied to a cell.

Copy and Paste by Using the Paste Options Gallery

Data in cells can be copied to other cells. When you paste data to another cell, the Paste Options gallery displays, which includes Live Preview to preview the Paste formatting that you want.

Edit and Format Multiple Worksheets at the Same Time (1 of 2)

You can enter or edit data on several worksheets at the same time. Use the Ctrl key to select more than one worksheet tab. If the sheet tab displays with a solid background color, you know the sheet is not selected. If you see [Group] in the title bar and tab names are underlined, those sheets have been grouped.

Edit and Format Multiple Worksheets at the Same Time (2 of 2)

Formulas, formatting, and styles in this workbook were applied to several worksheets at the same time by grouping the sheets. When you are finished, right-click a sheet tab, and on the shortcut menu, click Ungroup Sheets.

Create a Summary Sheet with Column Sparklines (1 of 5)

A summary sheet is a worksheet where totals from other worksheets are displayed and summarized. This summary sheet uses formulas constructed from other sheets in the workbook, known as the detail sheets. The figure shows the data from the detail sheet named Online Sales being used in a summary sheet. Notice that the worksheet name is surrounded by apostrophes followed by an exclamation point.

Create a Summary Sheet with Column Sparklines (2 of 5)

Sparklines are tiny charts within a single cell that show a data trend. Sparklines are created using the Create Sparklines dialog box.

Create a Summary Sheet with Column Sparklines (3 of 5)

The summary sheet now contains sparklines.

Create a Summary Sheet with Column Sparklines (4 of 5)

The IFS function checks whether one or more conditions—logical tests—are met, and then returns a value corresponding to the first TRUE condition.

Create a Summary Sheet with Column Sparklines (5 of 5)

This example of the IFS function contains four logical tests. It is important to include a default value that displays if none of the conditions are met. When no default value is included, an error message displays if no TRUE conditions are found. In this case, the default value is 0.

Format and Print Multiple Worksheets in a Workbook (1 of 2)

You can drag a worksheet to a new location. The summary sheet will be moved to be the first worksheet in the workbook.

Format and Print Multiple Worksheets in a Workbook (2 of 2)

By selecting more than one worksheet, you can apply the same formatting to all the worksheets at the same time, for example, to repeat headers or footers. You can also group worksheets in order to print several sheets at one time. In Print Preview, the number of pages that will print is indicated at the bottom of the window, as shown here.

Questions

Homework is Completed By:

Writer	Writer Name	Amount	Client Comments & Rating
ONLINE	Instant Homework Helper 4.8 4305 Orders Completed	$36	She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up! 5.00
Answer.docx Turnitin Report.pdf Contact Writer For Solution Contact Writer For Solution

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 3 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 6 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 12 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

6 writers have sent their proposals to do this homework:

Writer	Writer Name	Offer	Chat
ONLINE	Assignment Hub I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You 4.8 1218 Orders Completed	$42	Chat With Writer
ONLINE	George M. This project is my strength and I can fulfill your requirements properly within your given deadline. I always give plagiarism-free work to my clients at very competitive prices. 4.8 1344 Orders Completed	$20	Chat With Writer
ONLINE	Best Coursework Help I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects. 4.9 1554 Orders Completed	$23	Chat With Writer
ONLINE	Academic Master I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles. 4.8 2877 Orders Completed	$30	Chat With Writer
ONLINE	Professional Accountant This project is my strength and I can fulfill your requirements properly within your given deadline. I always give plagiarism-free work to my clients at very competitive prices. 4.9 1470 Orders Completed	$45	Chat With Writer
ONLINE	Peter O. I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects. 4.9 1260 Orders Completed	$31	Chat With Writer