Excel tips: Ultimate crash course series
- Part 1 – Copy, cut & paste in Excel
- Part 2 – Conditional formatting in Excel
- Part 3 – Pivot Tables in Excel
- Part 4 – Mathematical Formulas in Excel
- Part 5 – Excel formatting guide for a great looking spreadsheet
- Part 6 – Essential Excel formulas like vlookup
- Part 7 – Filtering your data in Excel
In part two of the series, we break down conditional formatting. This is one of the most effective ways to bring your data to life in Microsoft Excel. Part one introduced you to some essential formulas and functions teaching you the basics of Excel. This series has been designed using data from the thousands of learners on Filtered courses and 30+ Excel experts.
Basic conditional formattingConditional formatting allows you to set rules that change the format of a cell automatically based on its content. E.g. In the below list of staff salaries, a conditional format of a red highlight has been applied to all salaries greater than £40,000. This helps you to quickly focus on certain aspects of a spreadsheet or to highlight errors and to identify important patterns in data. Conditional formats can be applied very easily and quickly to highlight certain cells or can be used in much more complicated and imaginative ways to show values graphically or automate the formatting of a spreadsheet. You can find the Conditional Formatting options in the Styles group of the HOME ribbon tab.
Applying rulesThe Highlight Cells Rules cover a range of different cell types as this video shows: Conditional formatting is very flexible and can help with a range of Excel issues. For example, if you were looking for a particular value as part of a reconciliation, then you could use Equal To or, to allow more latitude, Between, to highlight all candidate values in a list. The Top/Bottom Rules operate in a similar way, highlighting the highest or lowest items in a list, either by number or percentage. In addition to the basic highlighted cells for conditional formats, you can use graphical conditional formats. This is useful when you want to show a pattern within your data, such as an increase in profits over the past few years, or even to see if there is a pattern in your data. Here’s a video that shows you how: In the video you can see that we can select to Edit or Delete any of the rules we have created. We can also change the order of Rules and set certain rules to ‘Stop If True’. The order and Stop If True options can be used if you want one of your rules to override the rules that follow. Although in many cases the default rules will achieve what you want, Edit Rules gives you much more control over the conditions that trigger the conditional format and the formats themselves: As well as choosing between the different types of graphical Format Style, for each element you can choose the Icon or colour to be used. For a Data Bar you can choose different colours for positive and negative values and set where the axis between the two will be. You can also set the criteria for each element, choosing the value or cell reference to use as well as the type of value, for example, Number or Percent. In our example above, we have set the Icons for the lower 2 elements to No Cell Icon and chosen the Green circle for high-value items; we have set the threshold for this icon to be used as >= to the value in cell I1. Sometimes, the most effective use of conditional formatting is to apply a very simple highlight, such as an icon for notable values as we have done here: We hope you’ve enjoyed your second lesson. The past way to really get to grips with Excel is to practice, practice, practice. If you haven’t done so yet, open up Excel and experiment with Conditional Formatting. Next time, in part 3 of the series, we’ll get stuck straight into PivotTables. AAT students and professional members can access a wide range of Excel resources and training.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.