Pract 1
Pract 1
Pract 1
1 Date: 09/01/24
Our data set consists of 213 records and 6 fields. Order ID, Product, Category,
The following dialog box appears. Excel automatically selects the data for you. The
default location for a new pivot table is New Worksheet.
3. Click OK.
Drag fields
The PivotTable Fields pane appears. To get the total amount exported of each
product, drag the following fields to the different areas.
1. Product field to the Rows area.
Result.
Filter
Because we added the Country field to the Filters area, we can filter this pivot
table by Country. For example, which products do we export the most to France?
Note: you can use the standard filter (triangle next to Row Labels) to only show
the amounts of specific products.
Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items.
To change the type of calculation that you want to use, execute the following
steps.
1. Click any cell inside the Sum of Amount column.
3. Choose the type of calculation you want to use. For example, click Count.
4. Click OK.
To easily compare these numbers, create a pivot chart and apply a filter. Maybe
this is one step too far for you at this stage, but it shows you one of the many
other powerful pivot table features Excel has to offer.
A pivot chart is the visual representation of a pivot table in Excel. Pivot charts a
Create a Pivot table to analyze and summarize data
To insert a pivot chart, execute the following steps.
3. Click OK.
Below you can find the pivot chart. This pivot chart will amaze and impress your
boss.
Note: any changes you make to the pivot chart are immediately reflected in the pivot
table and vice versa.
Filter Pivot Chart
To filter this pivot chart, execute the following steps.
1. Use the standard filters (triangles next to Product and Country). For example,
use the Country filter to only show the total amount of each product exported to
the United States.
2. Remove the Country filter.
3. Because we added the Category field to the Filters area, we can filter this
pivot chart (and pivot table) by Category. For example, use the Category filter
3. Choose Pie.
4. Click
OK.
Result:
Use VLOOKUP function to retrieve information from a different worksheet or
table
What If Analysis using Goal Seek to determine input values for desired
output
Step 1: On the Data tab, in the Forecast group, click What-If Analysis.
Step 4: Type a name (70% highest), select cell C4 (% sold for the highest price) for
the Changing cells and click on OK.
Enter the corresponding value 0.7 and click on OK again.
Step 5: Next, add 4 other scenarios (60%, 80%, 90% and 100%).
Step 6: Finally, your Scenario Manager should be consistent with the picture below:
Note: to see the result of a scenario, select the scenario and click on the Show
button. Excel will change the value of cell C4 accordingly for you to see the
corresponding result on the sheet.
1. Scenario Summary
2. To easily compare the results of these scenarios, execute the following
steps.
3. Click the Summary button in the Scenario Manager.
4. Next, select cell D10 (total profit) for the result cell and click on OK.