Data Analyst Interview Questions: Excel Series- Conditional Formatting

There’s an impromptu exec meeting coming up in 10 minutes, and ‘quick!’, you’ve been asked to present a list of the Top 10 and Bottom 10 performers in your stores nationwide. You’re at your desk, staring at your Excel spreadsheet, wondering how to navigate your way through tens of thousands of rows to get this list. Oh, look, there go 2 minutes of your already limited time. What do you do?

Hello everyone. It’s Maria, your favourite Data Explorer 😄.
Welcome to the start of our 7-day Excel series 🥳. Hope you followed up the just concluded 7-day SQL series. Today, we’ll be talking on Conditional Formatting.

Conditional formatting is a tool that applies formatting to your data depending on the conditional rules you lay out. It’s a great way to see patterns and trends in your data, to visualize your data and check for specific information. If you hover over the Conditional Formatting button on your Excel workbook ribbon, it gives the information — “Easily spot trends and patterns in your data using bars, colours and icons to visually highlight important values.” In this way, you could give specific colours, fonts or other features to specific data you’re working with.

Under the Conditional Formatting drop-down, we have the following options:
• Highlight Cells Rules (Greater/Less Than, Duplicate Values, etc.)
• Top/Bottom Rules (Top/Bottom 10, Above/Below Average, etc.)
• Data Bars
• Colour Scales (Fill specific cells with colour)
• Icon Sets (Directional icons, Shapes, Indicators, etc.)
• New Rule (Here you can create your own rule, and set it to format values where your formula/rule is true)
• Clear Rules
• Manage Rules (Tweak the rules to your taste)

Let’s use this to answer our question. Looking at our data, we’ll select our column (Sales/Profit/Revenue/etc.). Then we go to the Conditional Formatting button, click on our preferred option (in this case -Top 10/Bottom 10), and voilà!, the magic happens. The right cells will be highlighted in selected colours, thereby making it easy for us to filter them by colour. You could even use this to get the Top 5 or 15, whatever range you’d prefer.

Another example: Making use of our Marketing Campaign dataset, let’s highlight the highest amounts spent on Meat Products.

You can also use it to visualize data greater or less than. If you have a set amount, e.g. ₦1,000,000, you can use Conditional Formatting to visualize the stores that got sales greater or less than this amount, or the average amount.
After you input your desired figure, you can then format calls that meet up with any colour of choice, e.g. you want the stores that passed the ₦1m mark to be highlighted in green colour.

From our Marketing Campaign dataset, let’s get the customers that haven’t made a purchase for over eighty days.

We can also make use of Data Bars to represent the values in each cell. The longer the bar, the higher the value, i.e. the number of purchases.

One can also format cells based on what they contain, e.g. values, texts, dates, errors, blanks, etc. Here, the cells in the ‘Marital Status’ column containing the word ‘Single’ have been formatted.

You can specify multiple conditions concerning the appearance of your selected cells, e.g. change the font or background colour, fill with colour, select border styles, etc.

Conditional Formatting can be applied to a range of cells, an entire Excel table, and even a PivotTable report. It can also be used to fish out duplicate values, find cells that contain specific keywords, amongst other uses.

That’s it today on Conditional Formatting. As always, give this lots of claps and comments. Hope to see you tomorrow for our discussion on Array Formulas in Excel. 🤗