Excel Pivot Tables - Getting Started
Last week I discussed the basics of pivot tables, this week we are going to build our first pivot table so you can understand how they work.
The biggest hurdle when creating the PivotTable is to determine exactly how you want your report to look and what you want to report on. So, using the data I showed last week I want to know the total number of tickets that were sold for each event (in rows) and how many were Adult, Senior or Children’s tickets (in columns). Here’s how to start:
- On the Menu Ribbon choose Insert and then PivotTable.
- Select the data to use
- Select the worksheet and the cell to place the table (I prefer to start a new worksheet)
- Now you will see the following screen:
From here you can check or drag and drop the fields into the grid. If you check off each field you want to use Excel will put these checked items into the Row Label area – this can be very confusing. To avoid this confusion, instead drag and drop the fields you want into the areas you want them displayed in. This sounds easier than it is. So let’s go over what each part of this screen that is showing.
Report Filter – use to apply an Excel filter to an entire table. For example, if you drag the "Event" field to be the filter the table then a drop-down options area (Easter, Christmas, etc) will appear. When you choose an option from this drop-down list the table will show only the data contained in those rows.
Row Labels – displays the contents of the chosen field in rows (in my example I have chosen Events as my row labels). You can also filter your report from here (using the drop down list).
Column Labels - used to apply an Excel filter to one or more columns that have to be shown in the pivot table. In my example the sum of values is showing as the column data. The system put this in automatically after I chose the fields to be used as values.
Values – lists what kind of data will be shown in the report. For my example I want to show the totals (sums) of kinds of tickets sold (Adult, Child, Senior). NOTE: when you first move your fields into this area they will be shown as a “count” and not a “sum” or other type of calculation. To correct this, click on the field in the Values area and choose Value Field Settings and opt for Sum, Average, Mean, etc.
Here are the results of my table – exactly how I wanted it to look!
- If you don’t like what you’ve created, click out of the PivotTable window and click Undo (or Ctrl Z) to go back to start over again.
- If you click outside of the grid you will loss the window of fields, to get it back just click inside the table grid and the list shows back up.
- If you have placed a field in an area and want to remove it – drag it to the top (where the fields are listed) or click on the field and choose remove field. Note this will only remove the field from the area and not from the list of fields to choose from.
Try these tips out for yourself and next week I will go even deeper into what other options you can apply to PivotTables.
If you need help with an Excel project or have questions – contact us at firstname.lastname@example.org for assistance.
Why Simple Web Design is Best
When it comes to web design – simpler is better and it can be proven! In August, 2012, Google conducted a study and researchers found that users judge websites as beautiful or not within 1/50th—1/20th of a second and they also found that that “visually complex” websites are consistently rated as less beautiful than their simpler counterparts.
To read why this is so click here to read the full article here https://medium.com/design-ux/168074e9575
If you want to explore creating a simpler site or need help with your existing site contact me at email@example.com to help you move forward.
You Have To Laugh
Funny Video: Cat meets bunny - https://www.youtube.com/watch?v=8RREVNQYe1I