Skip main navigation

New offer! Get 30% off one whole year of Unlimited learning. Subscribe for just £249.99 £174.99. New subscribers only. T&Cs apply

Find out more

Profitability Analysis and Finding Anomalies

Learn more about 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.

Conditional Formatting on the Table and Pivot Table

When analysing large volumes of data, Conditional Formatting is, as you can see, a handy function for the detection of patterns and trends and funding anomalies. Having said that; problems do sometimes occur, and when this happens, it is useful to systematically work your way through the error, to solve it.
The following are examples of errors that might occur when dealing with Conditional Formatting on the table and pivot table. Take a moment to work your way through the points listed below to get a sense of how to approach a Conditional Formatting error.

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.

This article is from the free online

How to Use Microsoft Excel for Data Analysis

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now