Do more with conditional formatting in Excel (part 1 of 2)
If you’ve been using Microsoft Office for a long time, you may remember that big changes came about with the introduction of Office 2007.
All the Office applications got new file types, for a start, so that Access changed from .mdb files to .accdb, and Excel changed from .xls to .xlsx.
These file type changes were caused by quite a number of internal changes to the structure of the files, not least that they became effectively zipped XML archive files. But at the same time, lots of new features were introduced – perhaps most notably the growth from 65k rows in a worksheet to just over 1 million.
But there were also lots of new features added to the Conditional Formatting toolset. Among the most interesting additions were:
- The ability to have more than 3 conditions per cell (many more!)
- The ability to colour code every cell with shades on a scale (for example from red to green) based on its value
- The ability to add in-cell shading – like a mini bar chart – to add visual clues as to the relative value in that cell
- The ability to add icons such as arrows and traffic lights to cells depending on their values
There were lots of other additions aimed at making existing capabilities easier to use – for example highlighting duplicate values in a range was always possible with conditional formatting, but now it only requires a couple of clicks.
But there are other things that are still only posible with a bit of know-how. And by the end of this video tutorial, you to can know-how!
Key conditional formatting tricks
Once you’ve watched our video you’ll know how to do the following:
- Make the conditional formatting more dynamic, by referencing a “threshold cell” in your condition. Change the condition simply by changing the value in that cell
- Make the entire row change colour when a single value in that cell meets the condition
- Use a scroll-bar control to change the value in a cell by dragging the scroll-bar.
Take a look at the video, and give us your feedback in the form below. And if you’d like to know more about what can be done with Excel, why not get in touch and organise some Excel training for you and your company?
Part 2 coming soon!
Come back later this week and in part two, we’ll take a look at how to use conditional formatting to make an on-screen form tailor itself to the answers you give!
Video length: 14m 13s
Excel version used: 2010
Tutorial specific to this version: No