Pivot tables - one of the best Excel functions for quick and easy data analysis. But one disappointment that I observed with users relates to the field of data formatting and, in particular, how to do cell formatting.
The question of formatting, which did not remain or was not inserted, appears if the user requests an update in order to update his data in the table or delete it, and then add the data back to the area of calculation. Formatting appears to disappear & # 39; from the table.
There is a way to prevent this. So let's look at an example.
Below is a small example of my data extraction, which documents the data fields "Calls Taken & # 39; as well as “Calls Dropped & # 39; over a period of time in February 2015.
Date - Calls - Calls - Calls - Total Calls
01/07/2015 - 10 - 5 - 15
02/07/2015 - 25 - 10 - 35
First create a standard pivot table.
- Select the dataset you want to use for the pivot table.
- The first thing to do is to place the cursor on your data list.
- Select the "Insert" tab
- Hit-pivot table icon
- Select the Pivot Table option
- Select table or range option
- Choose to place your pivot table in a new worksheet or in the current one; for this lesson choose the first option
- Click OK.
- The tab "Options and Design" will appear in the tool "Pivot Table"
- Check the boxes next to the fields you want to use to add them to the pivot table
- In this example, add Total Calls.
Now we need to add the calculated field. My calculated field is the number of calls dropped as a percentage of the total number of calls received on that day. Pretty standard calculation.
To create a calculation field
- Select any cell in the pivot table.
- In the Pivot Table section, select Tools, then click Formulas.
- Select a calculated field
- The insert calculated field dialog box will appear
- Enter a name for the calculated field in the Name field.
- Enter your calculation
- In this case, the calculation = (Calls Dropped / Total Calls)
- Name the calculated field as% of the total number of calls.
Then, go and select the cells directly in the data area where it is calculated, and change the formatting to%.
This changes my calculated field to%, but if I then delete the value fields, then decide to add in the Sum Calls field dropped out. See what happens. Formatting dashes to cells, not data values.
Now your number formatting only applies to this value. By setting the number format in the field itself, the number format will be applied sequentially regardless of how much the table changes.
It really is right in Excel.
- Click the “Value” field in the “Field List” field — in this example
- Setting the value field
- Number format
- Change it to%
- It now formats the calculated field as%
Work is done.
