28 Mar For this assignment, you will learn to create, sort and filter an Excel table. Melanie Castillo, Product Manager for Rosedale Landscape and Garden, has requested a workshee
For this assignment, you will learn to create, sort and filter an Excel table.
Melanie Castillo, Product Manager for Rosedale Landscape and Garden, has requested a worksheet that summarizes the current palm tree inventory data. Melanie would like the worksheet to include the total Quantity in Stock and Total amount of Sales by Type, and she would like the items to be sorted from lowest to highest retail price using custom sort. She would also like the Total amount of all Sales, with a Funnel Chart created.
You will do the following:
· Edit the file e02L_Palms file (see Project 3B files) to provide Melanie with the information requested, and use the Table feature to find the data requested.
· Format the worksheet titles and data and include an appropriately formatted table so that the worksheet is professional and easy to read and understand.
Insert a footer with the file name, and add appropriate document properties.
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
Copyright © 2020 Pearson All Rights Reserved.
Copyright © 2020 Pearson All Rights Reserved.
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.
1
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
Copyright © 2020 Pearson All Rights Reserved.
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
2
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
Copyright © 2020 Pearson All Rights Reserved.
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
3
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (1 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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.
4
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (2 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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 + =.
5
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (3 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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.
6
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (4 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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.
7
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (5 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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.
8
Use Flash Fill and the Sum, Average, Median, Min, and Max Functions (6 of 6)
Copyright © 2020 Pearson All Rights Reserved.
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.
9
Move Data, Resolve Error Messages, and Rotate Text (1 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
10
Move Data, Resolve Error Messages, and Rotate Text (2 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
11
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
Copyright © 2020 Pearson All Rights Reserved.
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.
12
Use COUNTIF and IF Functions and Apply Conditional Formatting (2 of 7)
Copyright © 2020 Pearson All Rights Reserved.
The slide shows the Function Arguments dialog box for the COUNTIF function.
13
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
Copyright © 2020 Pearson All Rights Reserved.
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.
14
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.
15
Use COUNTIF and IF Functions and Apply Conditional Formatting (5 of 7)
Copyright © 2020 Pearson All Rights Reserved.
The Function Arguments dialog box is used to create the IF function.
16
Use COUNTIF and IF Functions and Apply Conditional Formatting (6 of 7)
Copyright © 2020 Pearson All Rights Reserved.
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.
17
Use COUNTIF and IF Functions and Apply Conditional Formatting (7 of 7)
Copyright © 2020 Pearson All Rights Reserved.
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.
18
Use Date & Time Functions and Freeze Panes (1 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
19
Use Date & Time Functions and Freeze Panes (2 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
20
Create, Sort, and Filter an Excel Table (1 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
21
Create, Sort, and Filter an Excel Table (2 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
22
View, Format, and Print a Large Worksheet (1 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
23
View, Format, and Print a Large Worksheet (2 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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
24
Navigate a Workbook and Rename Worksheets
Copyright © 2020 Pearson All Rights Reserved.
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.
25
Enter Dates, Clear Contents, and Clear Formats (1 of 4)
Value Typed | Example |
m/d/yy | 7/4/2016 |
d-mmm | 4-Jul |
d-mmm-yy | 4-Jul-16 |
mmm-yy | Jul-16 |
Copyright © 2020 Pearson All Rights Reserved.
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.
26
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.
27
Enter Dates, Clear Contents, and Clear Formats (3 of 4)
Copyright © 2020 Pearson All Rights Reserved.
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.
28
Enter Dates, Clear Contents, and Clear Formats (4 of 4)
Copyright © 2020 Pearson All Rights Reserved.
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.
29
Copy and Paste by Using the Paste Options Gallery
Copyright © 2020 Pearson All Rights Reserved.
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.
30
Edit and Format Multiple Worksheets at the Same Time (1 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
31
Edit and Format Multiple Worksheets at the Same Time (2 of 2)
Copyright © 2020 Pearson All Rights Reserved.
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.
32
Create a Summary Sheet with Column Sparklines (1 of 5)
Copyright © 2020 Pearson All Rights Reserved.
A
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Wridemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.