5 Styled Table Copy
Explication de la tâche .
Here we are again, we have the same file from before. But now we need to create a tool so that people can browse the table better.
Ultimetaly, we are trying to build the original table we had, but this time as a view. Locked and untouched from the data. This view should have the same type of settings as the original file, with separators for provinces and totals. As well as titles and a clean view.
Could you with a pivot table create a view for this table that has the information clear with a way to filter for provinces so that the board of executives can easily see everything.
Add some details about profits (Sales minus COGS)
🌶 Défi épicé . 🌶
Comment faire.
Well, at this point you should know how to transform this range of data into a table and then into a Pivot Table.
Hit Ctrl-T when selecting cell A2 and go to the Table Design tab and hit sumarize with pivot table.
To create a copy of this table in a pivot table you will require to drag all the fields of interest into the "Rows" Field of the report, in this case we start with ID and bring most of them down to the Rows field.
If the date field is getting grouped by months and days. Just right click on one of the dates and select "Ungroup"
This is far from what we need but it's the step in the direction that we require to build the perfect view.
Most of the time your pivot shows up only as COMPACT VIEW which is not what we need. We need a view that doesn'T have subtotals.
The key to success is to go on the "design" or "Layout" tab on top (should be the last one when your pivot table is selected) And remove the subtotals, and select tabular view and repeat all items. You can also go back in the analyze tab to clean up the +'s and everything.
Nice! Look at that, we have the table but we don't have the numbers yet.
We are interested in the profits for this exercise so this will require going back to the original data sheet and adding a collumn that does the Sales minus the Cogs - In this specific exercise you will need to go in Cell J2 and type the following function "=G2-H2" And name the top of the collumn "profits"
Alright, Profits are ONLINE. Your pivot need a refresh and then you can drag your 3 numerical fields into the "Values" Field.
By doing this you will be able to add the number amounts to the table. You might need to clean up the formats to make it look nice.
To do that, just double click the titles and change the number formats!
Wow! We have the table, it looks swell. It's all the data from the other sheet!
What's really great is that no one can edit this information and change your data. It's only for a view. What will really tip this in the direction of AWESOMENESS is by adding slicers to your pivots. If you add a slicer for province you will be able to quickly change the table to only display the data for each province! You could even add a slicer for sales REP so that the table can further filter itself!
To do this, Click your pivot table and go in the INSERT TAB > and hit INSERT SLICER and choose one for Province and one for Sales Rep.
Position them on your page and try it out!
This concludes this exercise, but you can make it look better by changing pivot table styles, add titles on top and make it to your tase!
