Hi friend! Every now and then we have to submit a report. This process can be painful, but using some colour and formatting might make the process more tolerable; nay—enjoyable. I am going to show you how to use custom formatting to add some colour to your report and spread some cheer.
Scenario
The Sobar business from last week is a hit and you want to create a quick report for the manager to juxtapose the sales from 2020 with the sales from 2021. We have 2 options for our final product:
Image A or B, which do you prefer? For the purpose of this post (and perhaps always🤔), the choice is an illusion and the correct answer is image B. How can we create image B with the fancy colours and symbols, you ask? Answer: Custom formatting (not to be confused with conditional formatting)!
Clarification
Though conditional formatting and custom formatting can accomplish the same tasks, they are accessed quite differently and custom formatting often works faster than conditional formatting. Conditional formatting is accessed via Home > Conditional formatting in Excel or Format > Conditional formatting in Google Sheets, while custom formatting is accessed by right-clicking in a cell or range of selected cells and selecting Format cells in Excel, or Format > Number in Google Sheets.
Custom formatting
We start with our data looking like this. If you are too lazy to replicate this manually, here is a copy of the sheet:
We want to add a column to the right of 2021 that shows the percentage change from 2020. We could do this by:
Adding a column for percentage change (%△)
Finding the change by using the equation below in the first cell of our new column
3. Copying the formula down the column
4. Convert the values into percentages by selecting all the values in the column and clicking the "%" icon in both Excel and Google Sheets.
With our percentage change (%△) column expressed as a percentage, our data looks like this now:
We are almost there. Let us format the percentage change column using custom formatting such that a positive change is written in green with a green upward-facing arrow, a negative change is written in red with a red downward-facing arrow, and no change remains black. Just like in our desired image B earlier.
Understanding Custom formatting
We can find custom formatting in both Excel and Google Sheets.
In Excel, select the cells you want to format, then press Ctrl + 1 (⌘ + 1 for Mac) to open the Format cells window and click Custom under the Number tab. We enter our formatting rule(s) in the box containing "General".
In Google Sheets, select the cells you want to format, then click Format > Number > Custom number format.
A Custom number formats window opens up and we will enter our formatting rule(s) in the box next to Apply.
As you can see, both Google Sheets and Excel offer extensive (and intimidating) custom formatting options which you can explore later. However, our desired format must be made from scratch.
Basic rules of Custom formatting in Excel and Google Sheets
Custom formatting is too broad to cover in one post. However, I will teach you the basic rules needed to create a format that does not exist among the preset options. The formatting rules are identical for both Excel and Google Sheets.
Fundamentally, custom formatting takes four arguments separated by semicolons: positive numbers; negative numbers; zero; text. (+;-;0;@)
Also:
"#" and "0" are placeholders for numbers (The example will clarify)
To change the colour of the entries, place the colour in square brackets at the front like this: [Red].
Be careful when adding Colour; the entries are sensitive to case and spelling. For example, use "Colour" for Excel in British English and "Color" for Excel in American English.
I prescribe an example for your headache
For our selection, we want every positive number to be green with the plus sign included, every negative number to be red with the negative sign and every zero value to return a grey dash(-), you would enter the rule below. Remember to separate positive; negative; zero using semicolons. We are not dealing with any text so we exclude that argument:
[Colour10]+0%;[Red]-0%;[Colour16]-;Let us see this in action below
In the example above, I use the colour index values instead of writing the name of the colour to specify the exact shade of the colour I want. You can find the colour index values in the image below:
💡: If ‘Colour’ does not work in Google Sheets, try ‘Color’
Adding the arrows
To add the arrows in Excel, go to Insert > Symbol then find them under Font: Arial, Subset: Geometric Shapes. Google Sheets does not have the symbols, but you can get them from Google Docs (Insert > Special characters) and paste them into Google Sheets. Once you get the arrow symbols, insert them into empty cells in the sheet for use later.
Now that we have our arrows, we copy and paste them into the formatting rule we created earlier and click OK. The upward-facing arrow will go in the positive argument and the downward-facing arrow will go in the negative argument:
After clicking OK, we have our desired format full of colours and arrows and ready to be shared!
Conclusion
Custom formatting is vast and I encourage you to explore it using some of the basics you've learned here today. Just think, you can use any symbols you want ♥♦♠♣☁☂★☆☀. Have fun with it. It is the small things that can turn that frown upside down.
Have a 5★ week!
Comments