I use-to spend a lot of time formatting the background colour of headings then making the text white. Changing the font and size and then trying to remember what my style was to apply to other Excel workbooks because I like to keep my formatting consistent with my business branding.
This process was taking me for ever and going back and forth finding Excel workbooks that had the formatting I liked or finding the notes where I had written the style attributes, was just becoming a nightmare until I noticed Cell Styles.
Yes, it was right in front of me for a long time. Cell Styles are found under the home tab.
- Select relevant cells
- Home tab
- Select Cell styles
- Choose the colour from the list
- You can even create your own style
Keeping Zeros in front of product numbers can be tricky, as a facilitator with over 24 years’ experience with Microsoft Office products I have seen some work around for this one.
VLOOKUP FUNCTION ISSUES
Apostrophe in front of the number. This does keep the zeros in front but also treats it as text not a number. Which is fine it does let you perform calculations but if you are using it for other functions it may not work as those functions are looking for a matching format. For example, your VLOOKUP Function may not work out.
Type some text then the number, mmm sure but most of the time that’s not the look we are going for or it impacts on the outcome if you are then wanting to sort by numbers or need it for your Lookup Value when doing a VLOOKUP Function EG: INV 300, AMOUNT 300
If you are typing a mobile number, ABN, ACN or TAX File number and enter the spaces the zero in front will be retained.
CUSTOM NUMBER FORMATTING IN EXCEL
If the number, you need to type has NO Spaces then you will need the ultimate technique. Go to Custom Format or Press ” Ctrl 1″. Select Custom and enter a 0 for every digit you need displayed. If you need spaces or specific symbols include these.
Eg: 0000 enter 1 digit. 5 result will be 0005
000-00 enter 1 digit. 5 result will be 000-05
TEXT AND NUMBERS IN EXCEL
Let’s say you would like to display your number with SN in front of it but its such a chore to constantly type the text. Use Custom Format to display the “SN” text in front of your chosen numbers.
- Go to Custom Format or Press Ctrl 1
- Select Custom
- Replace General with the following
- Type “SN” 0000
Followed by a zero for every digit you need include any spaces or symbols such as – or /.
Type 5 result will be SN0005
This could be why when you try to use Text To Column to split the text from the number but it doesn’t display in the dialog box.
EMBEDDED COLOUR IN EXCEL CELLS
You may have come across a cell which has a colour applied to it and would like to remove or replace it with another colour. It also has the applied feature we discussed earlier of the text immediately followed by a specific number of digits. EG SN0005 and in blue.
The usual methods to remove or replace the formatting doesn’t work. Select the Cell, go to Home and change for Font Format to Black or another colour but it doesn’t work. What’s going one you may think.
CUSTOM FORMAT is going on.
Select the Cell/s and go to Custom Format (Ctrl 1) square brackets have been added to the front of the code to change it to the colour [Blue]
CUSTOM FORMAT COLOURS LIST IN EXCEL
Initially there are 8 Colours you can set in your Custom Format setting, they are:
[black] [white] [red][green] [blue] [yellow] [magenta] [cyan].
Wait there is more: 57 just make sure you spell colour the America way “color” no space between the number.
For Example, Color3 written in the Custom Format dialog box as: [color3]0000
Type the number 5 result 0005 in red.
CLEANING EXCEL FORMATS MADE EASY
We hear the words time poor thrown around and that includes when working with an Excel Spreadsheet. In the back of your mind as you’re working on your Excel Spreadsheet you think – there must be an easier and quicker way to get the job done. Formatting the spreadsheet and cleaning up the mess others have left behind is taking for ever. There is a quicker way. No, it’s not Delete, Delete that for emails – ha ha.
If you have a block of data that needs the formatting stripped back to Normal. Try Ctrl A this will select a block of data. From the Home tab select the Clear command then Select Clear Formats. This will leave behind the data but strip the Font, Size, Colour etc.
HOW CAN YOU DISABLE A HYPERLINK IN MICROSOFT EXCEL?
Importing data is quite a popular task to be used in conjunction with Microsoft Excel. If importing client or employee details, you might get and Email address, Website Address or links to social media. When you don’t need it to be active it can send you off accidentally to that email or web site causing delays and frustration.
Selecting the cells with the hyperlink can be tricky since every time you try and click on it to select it it’s going to shoot you off to the web site. Click one cell away from the hyperlinked cell and use keyboard shortcuts to select the data by holding the Shift key and your arrow keys. The Shift key selects but if you included the Ctrl key it will Select and jump blocks of data. In summary select one Cell away from the hyperlinked cell and Hold Ctrl Shift then tap the arrow keys.
Once you have the data selected go to the Home tab Select Clear and choose Clear Hyperlinks.
Still need help watch this video for instructions on how to retain zeros in front of numbers when using Microsoft Excel.
For more formatting concepts visit: 6 Useful Tips on Excel Formatting
To retain Zeros in front use spaces or symbols as your separator. For a mobile use spaces 0414 417 059 and the zero in front will remain. When it’s not possible to use symbols use the ‘ but keep in mind this technique will treat the number as text not a number. For best results go to Custom Formatting and enter a Zero for every digit you want to display.
To remove hyperlinks go to the Home tab select Clear then Clear Hyperlinks.
Still stuck, Book a face to face training session.
Sydney – Greater West Australia – Currans Hill