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

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

Posted at 08:35h in Computer Science by

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

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

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)

Value Typed	Example
m/d/yy	7/4/2016
d-mmm	4-Jul
d-mmm-yy	4-Jul-16
mmm-yy	Jul-16

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)

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)

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.

Do you need an answer to this or any other questions?

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.

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

e02L_Palms.xlsx

Sheet1

Excel_2_PPT.pptx

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

Are there Discounts?

Hire a tutor today CLICK HERE to make your first order

Related Tags

About us

Quick help

Subjects covered