5 Unknown Excel Features To Make Your Work Easier
Microsoft Excel is an extremely powerful and vast program, and because of this many of its features, new and old, can go unnoticed.
Yet some of these features could have saved you hours of time and aggravation, and improved your spreadsheet greatly.
Here is a list of the top 5 features of Excel (in my opinion), that people are not aware of.
Go To Special
I begin with a feature that has been around for many years and inspired this article. I demonstrate some techniques that require this feature on my courses, and very rarely are people aware of it’s existence.
Go To Special highlights cells on a spreadsheet, or in a selected range, that meet certain conditions. For example, it can be used to highlight all the blank cells, all those with formulas or those that contain Data Validation rules.
This feature can be used to easily delete blank rows in a range, change the colour of blank cells, or to protect all the cells containing formulas.
Go To Special can be found by clicking the Find & Select button on the Home tab of the Ribbon.
The video below shows how Go To Special can be used to delete all the blank rows in a list.
You can save your own views in Excel as a Custom View. A custom view includes any print settings, filters and also hidden columns that you have applied.
It provides a fast way for you to switch to a specific filter that you regularly do, or a view that you often print.
It can save much time messing around with unhiding columns, applying different column filters and modifying print settings.
To save a custom view;
Create the view by applying the required filters and print settings.
Click the View tab on the Ribbon and then Custom Views.
Click the Add button, enter a name for your view and click Ok.
You can then apply the view when needed by opening the Custom Views dialog and clicking Show.
It is recommended to create a view without these settings applied, which you might call normal. This provides and easy way of resetting the filter, hidden columns and print settings.
Formatting a Range as a Table
This feature was introduced with Excel 2007 and has been improved in every version since.
There are many benefits to formatting your range as a table. These include;
It creates a dynamic range. If more rows, or columns, are added to the table it will automatically expand. This means any PivotTables or formulas using that table will be using the correct range.
It change the column headers (A, B,C, D etc) to the name of the field/column as you scroll down the worksheet. No need for Freeze Panes on your header when it is formatted as a table.
Provides consistent formatting for the range including formatting alternate rows for readability. If more rows are added to the table they are automatically formatted.
The table can be named for easy referencing from any sheet.
- It provides structured references for your formulas. This makes your formulas easier to write, and to also read and understand them at a later date.
Take the following examples of a SUMIFS function. One using standard cell references and one using table references.
Using standard cell references
Using a table’s structured references
My creating sports league tables and tournaments in Excel online course contains some complex formulas that reference across sheets. Tables are used for all the ranges and work as a great example of how they can simplify the process.
To format your range as a table;
Select the range of cells you want to format.
Click the Format as Table button on the Home tab of the Ribbon and choose the style you wish to use.
Confirm the range of cells is correct and click Ok.
Formatting cells in Excel is a big deal. It is very important that Excel understands the data you are using such as time, dates, currency and percentage. However it is also important that readers understand it.
Although Excel provides many popular formatting options at just a few clicks of a button, sometimes you need more than what they offer as standard.
Custom formatting offers a far more extensive list of formatting options including the option to format negatives values on a sheet in a red font.
It also allows you to create your own formatting. So if you have a specific format that a part code, employee ID or a date needs to be entered, then custom formatting is the place to go.
Create Your Own KG Format
Flash Fill is a new feature to Excel 2013, but is so good that it immediately makes it onto my list. It has to be one of the best additions to Excel in recent years.
Flash Fill is an extension of the standard, yet also awesome, AutoFill feature of Excel (you know, the small square in the bottom right corner of a selected cell).
It will look for a pattern in the data that you entered and repeat that pattern in all of the cells that you fill, or copy to. Flash Fill can be used to;
Convert the case of text e.g. lowercase to uppercase, uppercase to proper case etc.
Concatenate text from multiple cells into one.
Copy part of a cell into another such as part of a product code, or somebody's last name.
Substitute a character, or characters, in a cell with something else e.g. replace 12.03.2014 with 12/03/2014.
Remove unnecessary spaces from the beginning and end of text in a cell.
Watch this video showing 5 awesome Flash Fill examples.
What were your favourites?
Which of these awesome Excel features do you love the most?
What Excel Features Do You Recommend?
What Excel features can you not live without? Are there some Excel tools or shortcuts that you know that you find other users are not aware of.
Please share your hidden skills using the comments below.