Technology newsroom
Convert Numbers to Percentages in Excel PivotTables Easily Without Formulas
Learn how to transform numerical data in Excel PivotTables into various percentage formats, such as % of Grand Total or % of row/column totals. This allows for easier data analysis and creation of clearer reports, all without writing any formulas.
Often, data in Excel PivotTables is filled with numerous figures, making comparisons or identifying key trends difficult. This article from SyncTech Solution will show you how to convert those numbers into percentages, allowing you to perform in-depth data analysis instantly, whether it's sales proportion compared to the total or market share within each category, without spending time writing a single formula.
### Understanding First
Normally, when you drag a numerical data field to the Values area of a PivotTable, Excel automatically summarizes the results as a Sum or a Count. This is raw data that is useful to some extent, but it cannot convey the "proportion" or "significance" of each data item relative to the overall picture.
The "Show Values As" function, hidden within the PivotTable, is the tool that transforms how data in the table is calculated, from simple sums into various comparative percentage calculations, such as:
* **% of Grand Total:** Displays the proportion of each data item compared to the "grand total" of the table. Ideal for seeing what percentage of company sales each product or branch contributes.
* **% of Column Total:** Displays the proportion of each data item compared to the "total of that column." Ideal for analyzing market share within the same category, for example, what percentage of total sales in the North region comes from Product A.
* **% of Row Total:** Displays the proportion of each data item compared to the "total of that row." Ideal for observing data distribution, for example, what percentage of sales in the North region comes from each type of product.
Using this function will help your reports communicate more clearly and enable executives or teams to make data-driven decisions more quickly.
### Before You Start
To ensure your PivotTable functions correctly and without errors, you should prepare your data beforehand.
1. **Organize Data:** Ensure your data is in a correct table format, meaning column headers are in a single first row, and there are no merged cells in the data section.
2. **Check Data Type:** Columns intended for calculations (e.g., Sales, Quantity) should contain only numerical data, free from text or symbols, as this can lead to calculation errors.
3. **Create PivotTable:** Prepare to create your PivotTable from your dataset. Drag the fields you want to group into the Rows and/or Columns areas, and drag the numerical field you want to calculate into the Values area.
### How To
**Step 1: Right-click on the data you want to change**
* **WHAT:** Select any cell within the column of data you wish to convert to a percentage (in the Values area of the PivotTable).
* **WHERE/HOW:** Right-click on that cell with your mouse; a menu will appear.
* **WHY:** To access the quick command menu related to managing data in that field.
* **EXPECTED RESULT:** You will see a variety of menu options appear.
**Step 2: Select the "Show Values As" command**
* **WHAT:** Hover your mouse over the "Show Values As" menu.
* **WHERE/HOW:** When you point to this menu, a sub-menu displaying various calculation formats will open to the side.
* **WHY:** This tells Excel that you want to change the display format of the values in this field from a normal calculation (No Calculation) to another format.
* **EXPECTED RESULT:** Various calculation formats, such as % of Grand Total, % of Column Total, will appear for selection.
**Step 3: Choose the desired percentage format (Example: % of Grand Total)**
* **WHAT:** Click on "% of Grand Total".
* **WHERE/HOW:** From the sub-menu of "Show Values As," click this option.
* **WHY:** To instruct Excel to calculate the value of every cell in this field as a proportion of the net grand total of the entire PivotTable.
* **EXPECTED RESULT:** All numbers in that data column will change to percentage values, and the Grand Total cell will display 100%.
**Step 4: Try changing to another format (Example: % of Column Total)**
* **WHAT:** Try changing the calculation to "% of Column Total".
* **WHERE/HOW:** Repeat Steps 1 and 2, but in the final step, select "% of Column Total".
* **WHY:** To view the proportion of each data item within its own column category, which is useful for more specific analysis.
* **EXPECTED RESULT:** The numbers will change to percentages again, but this time the Total of "each column" will equal 100%.
**Step 5 (Optional): Display both numbers and percentages simultaneously**
* **WHAT:** Add the original data field to the PivotTable again.
* **WHERE/HOW:** From the PivotTable Fields pane, drag the original numerical data field (e.g., "Sales") into the Values area again. You will see two columns of the same data appear.
* **WHY:** This allows you to set different display formats: one column showing actual values, and the other showing percentages.
* **EXPECTED RESULT:** In the PivotTable, there will be two duplicate data columns. Then, follow Steps 1-3 for the second column to convert it to percentages.
**Step 6: Reverting to normal values**
* **WHAT:** Undo the percentage display.
* **WHERE/HOW:** Right-click on the percentage column again, select "Show Values As," and choose "No Calculation."
* **WHY:** To revert the display back to the original Sum or Count.
* **EXPECTED RESULT:** The data column will return to displaying normal numbers, as it did initially.
### Verify Results
After following the steps, verify the accuracy as follows:
1. **Numbers converted to percentages:** The values in the data cells have changed from normal numbers to percentage values with the % sign.
2. **Check Grand Total:** If you selected "% of Grand Total," the final grand total of the table should be 100%.
3. **Check Column/Row Totals:** If you selected "% of Column Total" or "% of Row Total," the total for each column or each row should be 100%.
### If Issues Persist
Generally, this function rarely causes issues, but if the results are not as expected, try checking these points:
* **Option is greyed out:** If the "Show Values As" menu is greyed out and cannot be selected, it might be because the data in your Values column is not purely numerical (it may contain text or blank spaces). Go back and correct the source data.
* **Unusual results:** If the percentages seem illogical, check that your source data does not contain subtotal rows, as this will cause the PivotTable to perform redundant calculations.
* **Need more complex calculations:** If the available percentage formats don't meet your needs, for example, if you want to compare percentages with the previous year, you might need to use other functions in "Show Values As," such as "% Difference From," or create a Calculated Field, which is an advanced technique. If unsure, consult your IT department or an Excel expert.
In summary, converting numbers to percentages in PivotTables is a powerful fundamental skill. It allows you to elevate your data analysis and create more engaging and easier-to-understand reports with just a few clicks.