Cloisters Level 1 863 Hay Street, Perth WA 6000 | 08 9218 9059
Copyright © 2018 ATI-Mirage. All rights reserved.
RTO No; 1918
7 Under-utilised Excel Features
Posted by Billy Howarth | IT Consultant on 16 April 2019
Excel is a powerful program that allows you to better organise all sorts of data. Most people think of using it just for numbers, but it really is more versatile than that. Once you get the hang of the basics here are a few tips.
One Click to Select All.
Rather than going into options to select your entire spreadsheet or searching for the right keys on the keyboard, Excel has a simple button you can hit. In the top left corner of your spreadsheet is a button that has a tiny triangle on it.
Simply click that button and boom, your entire document is selected.
Selecting an entire row or column isn't that difficult either. Just click on the first cell in the row or column you want to select then click either shift - spacebar for an entire row, or ctrl - spacebar to select an entire column.
Adding multiple rows or columns
Often when organising your data, you realise that you need to add a row here or there. Sometimes you need to add more than one. This is simple enough to do. Just highlight the amount of rows you need and then hit insert. Add as many as you need or more since deleting them is just as easy.
The keyboard shortcut that you would use for that is Shift Ctrl +.
These charts are a great way to do a quick representation of trends without needing to take up a lot of space. Think mini graphs in a single cell!
To insert a sparkline into your Excel spreadsheet, click on Insert then select which sparkline you want: line, column, win/loss. It will ask you to select the data you want represented and then which cell to insert it into.
Wild Card Searching
Not sure about the target result for searching? You can use a wild card (or two) to search for your result. By using the search feature (Ctrl F) you can enter in either ? or * to do the wild card search.
The question mark allows for a one character search, whereas the asterisk allows for one or more characters. Even better, add in ~ to the front of the search if you think that the result you are looking for includes either term.
If you have a list of data in a row but it’s making it difficult to clearly display the information, there is an easy way to transpose the row into a column without having to type it all again!
Copy the data, go to Home then Paste and select the paste option Transpose. Simple!
For a quick, easy visualisation when comparing a large dataset in Excel, heat maps are amazing as they utilises conditional formatting to make colour coding so much easier.
Select the dataset you need to colour code. Then select from the home ribbon Conditional Formatting then Colour Scales. Select the colour scale you want to represent your data.
The most common option is for high values to be green with low values in red with the others appearing in a gradient. As you look at the various scales, there is a live preview to show you how it will appear if that common option isn’t what you want.
A quick and simple way to make information in Excel stand out!
SmartArt allows you to construct fancy graphical lists and diagrams within your Excel worksheet. Especially useful for Organisation Charts.
Select the Insert tab, then the SmartArt option. It will give you a number of different options to select from and once you select the option you want, it will give you the basic structure of the list/diagram.
Select the Text Pane button in the SmartArt Tools Design tab that will appear in the ribbon if you can’t see where to fill in information. The Design tab will also allow for you to easily change the colour and designs.
Let these features help to improve your mastery of Excel and let us know which is your favourite feature?
To learn more about Excel and its features, we offer a range of courses including:
Plus, we also offer the Microsoft Business Certification allowing you to become a Microsoft Office Specialist!