Microsoft Excel is one of the most diverse tools you can get for spreadsheeting. Whilst I am slightly biased because I'm a Microsoft Office Specialist Master Instructor, never the less, Microsoft Excel is one of the most powerful tools you can use and in reality most people only use about 5% of the functionality that is actually in the application. One of the nifty tools I stumbled on many years ago when I first sat my Microsoft Office 2000 Master Instructor Exams was the ability in Microsoft Excel to utilise the Find command to actually search for text or cells with a specific format such as a cell that has a Yellow background or text with bold formatting.
That's right …
you can use the Find command to search for specific formatting in a cell.
To begin with we need to setup a few cells in a spreadsheet to demonstrate this tool. First of all open Microsoft Excel and a new workbook should open. If it does not simply choose the shortcut keystroke [Ctrl] + [N] and a new work book should be visible.
Okay so what we need to do is to select a couple of cells and change their formatting. Lets choose the cell address C8 and apply a yellow background. To do this simply choose the Format menu and choose Cells from the drop down menu and then choose the Patterns tab. Now its time to choose a colour and lets choose a Yellow colour. Then go through and make three other cell addresses yellow such as F9, E10 and G42.
Now follow the same steps outlined in the previous paragraph and choose a second colour for the background of the cells like a blue. Make sure that you don't put any text in the cells as we want to simply move through the cells by the formatting.
Alright, to start off, let us look at how to access the Find command. There are two ways to access this command and the first techniques is pretty straight forward, you simply go to the Edit menu and choose Find from the drop down menu and the Find and Replace dialog box will appear.
Alternatively, you can bring up the Find and Replace dialog box by using the shortcut keystroke [Ctrl]+ [F]. If the Find tab is not visible, mind you it should be, simply choose the Find tab. Press the Options but you will notice that a Format button will appear next to the Find What text box. Simply click on the Format button.
You will notice a dialog box pops up similar to the Cells dialog box. What you need to do at this point is to choose the same Yellow colour that you used for cell address, C8, F9, E10 and G42 and then press the OK button. Make sure that you do not have any text in the Find What text box. It is now time to find all those cells that have a background formatting of yellow. To do this, simply choose the Find Next button. What will happen is that it will go to the first cell that has the background colour of yellow. If you keep pressing the Find Next button it will go to each of the cells where you have used the background colour of yellow.
By using the Find function, you can literally search your whole document for any formatting that you require. Note also that whilst we did not have any text in the cells, you could do a similar Find command for looking for text with a certain Font Type, a certain Font Size or border type. You could even search for a certain number format as well.
Why not try a different background colour.…Try searching for the blue colour background and see what results you get.
The Find command is a very useful tool as it allows you to search not just for text but text with certain formatting or simply for certain formatting on cells. One of the key advantages of using this technique in your spreadsheets is to help speed up your ability to find the data you are looking for.
Chris Le Roy is a Microsoft Office Specialist Master Instructor and has available Microsoft Excel Shortcuts to help you with Microsoft Excel. He also offers a correspondence course where you can earn yourself Microsoft Excel Certificates issued by his company without even leaving your home - Microsoft Excel Spreadsheet Training. Tips on Microsoft Excel are also Available
No comments:
Post a Comment