Using Quick Analysis feature to create Percent Totals formulas.

Percent total is a great perspective when performing simple data analysis. The usual approach is to create the formula manually, format it to a % number, and finally, copy it to the other cells. A pie chart is another approach for a visual depiction of the data, but when there are a lot of data points, the chart produces too many thin pie slices and it is hard to read and distinguish the percent values.  The formula method is also good, but tedious to perform.

The Quick Analysis method described below is a more efficient method of producing percent total formulas, without having to write them manually. The only extra step is to convert the data range to a table object. Converting a range to a table object has many benefits. It facilitates sorting, filtering, pivot tables and many other functions, but it also provides a great way to produce percent totals for each line item using the quick analysis feature (versus manually creating a column, formulas, and copying & pasting).

 

 

New Method:  No % Total 3rd column necessary to create, let the tool create the column and calculations in one single action:

See the Setup below where only two columns are there in the data, and there is no % Total column.

 

Steps 1 – 3:  Click in the data, press CTRL + T, and click on OK.

 

Steps 4 – 6:  Click in the Table, press CTRL + A, Click on the Quick Analysis icon, and Scroll to the right and select % Totals.

 

Enlarged View of Quick Analysis Menu (from previous page)

 

When you click the % Total button in the Quick Analysis menu, the % Totals Column and the Formulas are created in a single action.