Profitability Analysis and Finding Anomalies

Up to now, we have mostly explored ways to use Excel to present data. Excel is also a useful tool for analysing data. When using calculated fields in pivot tables and conditional formatting, data can easily be analysed and compared.

In this video tutorial, you will learn how to analyse the profitability of businesses and detect anomalies in data using calculated fields in pivots and conditional formatting.

Calculated Columns

Similar to the standard calculations in Excel, you can create calculated columns.

One way of creating a calculated column is by simply adding a column to your Excel table with a standard calculation. For example, if you want to calculate profit, you will calculate revenue minus cost. Remember to refresh the data in your pivot table to display and visualise the newly added data. It is important to note that this method will not work for all calculations.

Another way of creating a calculated column is by using the Calculated Fields function from the Analyse section of the ribbon. This function allows you to select the fields you want to use in your calculated column and set a format or data type, for example, percentages.

Conditional Formatting

Conditional formatting adds a lot of power to pivot tables. It allows you to apply specific formatting to cells that meet certain criteria in your pivot table.

The following resources provided an in-depth overview of how to calculate values in pivot tables as well as how to use conditional formatting in pivot tables.

Click on these links to be taken to the resources.

• Calculating values in a PivotTable
• Quick start: Apply conditional formatting
• Change, find, or clear conditional formats dialog box options
• Use a formula to apply conditional formatting

Conditional Formatting on the Table and Pivot Table

Conditional Formatting on the Pivot

1. Apply conditional formatting rule on the revenue column at the level of countries as blue bars.
2. Apply a condition formatting rule on the revenue at the level of countries as green bars.
3. Apply a rule on all cells with Margin% value as colour scale.
4. Edit the table and enter a value of 999999 on the first row in the cost column. Refresh the pivot.

What happened?

• The margin calculation returned the wrong value.
• The error in the cost value in one row caused a very low value of Margin% and in comparison, all real values seem average.

This is a common problem with outlier values. Fix it by using manage rules /edit rule and changing the default settings for Minimum and Maximum settings to 10th percentile and 90th percentile respectively.

Conditional Formatting on a Table

1. Create a rule that will highlight the Cost column where The value in the cost column is less than 10.
2. Change the style to the first style on the left in the first row, in this way the style will not interfere with the rules.
3. Create a rule that will apply to the Unit Price column. Choose the top/bottom rules and create a rule to highlight the top ten values.
4. Filter the column by colour and selected only the highlighted rows with light red cell fill.
5. Create a rule that will be highlight the unit cost column if the unit cost column is larger than the unit price column.
6. Filter by colour, how many rows are filtered (7712).
7. Apply the rule so that it will colour the entire row:

What is the expression used for the rule so that it will affect the entire row? Drop your answer in the comment section below.

Join the discussion

So far we’ve mostly asked you to answer questions, but we’d like to learn from you as well. Do you have any personal tips or tricks for managing sets of data in Excel that you would like to share with us, and your fellow learners?

Use the discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to check the step off, then you can move to the next step.