Save yourself a lot of grief by learning these 5 foolproof Excel formatting tricks! Once you have mastered these 5 essential formatting tricks, you will be able to get so much done in so little time. Here’s what you need to know:

1. Troubleshooting

With conditional formatting, you can highlight any potential mistakes, errors, missing information and repetitions. Lower the amount of mistakes you make and create error free reports/documents with conditional formatting. What you can do is select all the cells you intend to search for errors and locate the menu tab, then select format and choose conditional formatting. Use the formula =iserror() when the dialog box comes up. That would highlight any potential errors you have made. If you’re tracking repetitions however, type in Countif().

2. Alternate rows/Columns

For easy reading, you can use conditional formatting to achieve that. Select the areas in the table that you want to format, then look for conditional formatting through the format tab under the Menu section. Once the dialog box comes out, you can change the format to something like this for highlight: =MOD(ROW(),4)=0. This is just the bare bones of the structure you need to modify your highlight colours. Experiment around with it and see what you can potentially achieve!

3. Graphs

Create In-cell graphs with the REPT() function and also conditional formatting to form a more dynamic chart. Your formula should start with =REPT( “__”, __/100). This is the bare structure of what your formula will look like, experiment with the actual output to see what you can come up with. Once you are able to get this down, you will have created yourself a nice little graph. To add conditional formatting, follow the same steps as above to reach the dialog box. Once you have done so, look for new rules and use a formula to determine cell format. For your format values, you input =($___>AVERAGE($__$_:$__$__)) something akin to this, depending on the nature of your graph. Once you have the format, change your font colour and style however you please.

4. Missing information

Make sure your report is seamless and perfect by not only looking for mistakes but also any possible missing information. This would be especially helpful if you have too much information to sift through. Select the relevant cells, look for your conditional formatting option and select new rule, then select the option: Format only cells that contain, select blanks from the drop down menu. Once done, click okay and it should be formatted accordingly!

5. Range

If you’re looking to single out data that fits in a certain specific range, you can do so as well. Look for your conditional formatting format and select new rule, once done select “Format only cells that contain.” You will then be given two drop down menus. Don’t do anything to the first one, but for the second, select between and key in your values. Once you have done so, set the format and hit okay. You should be able to do exactly what you intended.

These excel formatting tricks are not only flawless in terms of effect, they also save you a lot of time manually customising things. Keep trying and experimenting with different combinations with this as a starting point!