Excel Hints and Tips – Formatting in Excel…do you use it?

Excel Hints and Tips – Formatting in Excel

“We can all format in Excel can’t we?”

When we deliver courses on Excel which we do every week they quite naturally focus on formulas and logical data analysis tasks. We do train this on the Basic Excel training course cell formatting but it’s a smaller section as a lot of folk seem to know it but do they really….

Here’s a list of features for you to question how much you know about cell formatting, just some Excel hints on the topic from us!


Format Painter (FP) button

Excel hints - Format Painter Icon

This wonderful button allows you to paste the cell format from the source cell to destination cells. If you first click on a cell which is already formatted and then single click on FP then on to a cell unformatted it pastes the format across to the new cell. Double click on FP and you get multiple chances to paste across cells. Escape key switches off FP.


Conditional Formatting

Excel hints - Conditional Formatting on AgeThis feature allows you to create a number of rules for selected cells. So if you have a range of numbers, you can apply colour to them based upon some criteria. In the example to the left, if the age is less than 30 its pink else its blue. These can be edited by using Manage Rules. There are even options to use data bars and icon sets to illustrate your data.

An extension to this feature Is to use ‘conditional formatting with formulas’ . To do this you take the option to ‘use a formula to determine which cells to format’.  This would fill colour all the rows where there is a letter L in column H (Formula: =$h2=”L”). More examples of this are on our Master Class Excel Bronze course. See the example below.

Excel hints - Creating a Rule for Conditional Formatting

Date formatting as a day

This simple technique again covered in our Basic Excel course converts the format of a date cell to a day. See example below.

Excel hints - Date Formatting Screen shot


Quick Way to make a cell pounds and pence

Excel hints - Accounting Formatting ButtonThis button is on the Home Tab in the middle of the number gallery.


Cell formatting with Locked Cells and Protection.

Excel hints - Formatting with Lock CellsIt is possible and useful to lock and protect cells which contain formulas. To do this you must unlock everything, choose what to lock (e.g. formulas cells) and then put a password on the sheet. (Review Tab). In the simple example below cell A4 has been locked whilst A1:A3 are unlocked. Cell A4 has a password, so in effect no one can change the formula unless they have the password. This is covered in more detail on our Intermediate Excel course.



We hope you find these tips useful and can apply them to your work in Excel!


Like these Excel hints? Why not take a look at our previous post on some features in Excel?