Summarizing Complex Data

Introduction

You have already learned how to organize spreadsheet data, utilize functions, and apply basic analysis techniques. However, Excel can do much more! For example, what if you want to perform basic calculations without typing in formulas? What if you need to search your data using a specific value, such as an item number?

Let’s say you manage a grocery store and one of your customers wants to know if you have a certain item in stock. Excel to the rescue, to search your records! Of course, your records must be accurate, but that’s another story…

In this lesson you will learn how to create data outlines and pivot tables as well as utilize the VLOOKUP function.

Mamma Mia Pizzeria

In this scenario, you are analyzing sales data for a local pizzeria. Open the Mama Mia Pizzeria Sales document found when you click on the book icon in the upper right corner and complete the following:

Copy the Daily Sales worksheet and name the new worksheet Pivot Table.
Using the Daily Sales worksheet, create an outline that shows the average number of pizzas sold on each date and the total sales.
Collapse the outline so the daily sales and the grand total are visible.
Using the PivotTable worksheet, create a PivotTable and select the following fields: Date, Pizza, and Quantity.
Filter by Mama Mia pizza.
Create a slicer for Date.

When you complete the exercise, check your answers by watching the demo video below. This video does not have audio.

When working with Excel, there are times when we want information from a specific row in the spreadsheet. Needless to say, scrolling down and looking for that particular record would be cumbersome and inefficient. A much easier way to find the information is to use the VLOOKUP function, which looks up specific data from a vertical list.

In this practice exercise, you will use the VLOOKUP formula to search for information.

The project instructions are located in Resources under the book icon in the upper right corner.

After your finish the exercise, check your answers by watching the demo video below.

IMAGES CREATED BY GAVS