If you want to increase your productivity in Excel, then you need to know how my top 5 essential Excel tips and tricks work.
I find myself using these tricks on a daily basis which is why I have selected these particular tips out of literally hundreds that I could have chosen from.
In this post I will teach you the 5 essential tricks that I personally use in Excel to boost productivity. After reading this post, you’ll also be saving valuable time from your daily Excel tasks.
- Quickly Add Filters To Range
- Using The Format Painter
- Complete A Range Using Auto Fill
- Freeze The Top Row or Header
- Split Large Data Into Individual Columns
Quickly Add Filters To Range
Filtering data is a fundamental task in Excel. Whenever I’m given a data set to work with, the first thing I do is add filters and then look through them one by one to get a feel for the data.
I also like to sort the data A-Z and Z-A to get an understanding of the extreme values in each particular column.
To quickly add filters to your data set, select any cell within the data set and click CTRL + SHIFT + L on your keyboard. The top row of your data set, where your headers should be, will have filters applied.
Using The Format Painter
An ugly looking spreadsheet with badly formatted cells looks unprofessional and untidy so you’ll want to make sure your data is formatted correctly. This includes being consistent with borders and removing unsightly cell highlighting.
Your weapon of choice for this task will be the Format Painter!
You can activate the format painter by selecting a cell, or range of cells, whose format you want to copy, and then click on the ‘Format Painter’ icon:
Home –> Clipboard –> Format Painter (keyboard shortcut is ALT –> H –> FP)
Once you’ve activated the Format Painter, simply click on another cell where you would like to copy the formatting to.
For example, column D in the below spreadsheet shows the distance of a planet from the sun in our solar system. All of the values are numbers but you can see that the formats are inconsistent and ugly. I have some cells in bold, italics, decimal point numbers and even percentages!
The top cell in this column is formatted correctly, including the number of decimal places I want, and I’d like to copy that to the rest of the column. So I click into cell D3 and then hit the Format Painter button.
I then simply click and drag my mouse down across the cells that I want to copy the formatting to. Notice the little paint brush icon on my cursor? This indicates that the Format Painter is active and will apply the copied formatting to the cells you are selecting.
Format Painter Tip – The Double Click Method
If you want to copy the cell formatting to a number of non-adjacent cells, then activate the cell whose format you want to copy and then double-click the Format Paint button. You can now click on as many cells as you like and the format will be copied.
To exit copy formatting mode, either click the Format Painter button again of hit the ESC key.
Complete A Range Using Auto Fill
The auto-fill deserves a post all of it’s own, such is it’s usefulness and functionality. In fact, I have a great post you can read on 6 Ways To Use The Fill Handle.
For now though, I want to show you how you can use the fill handle to complete a range for you automatically.
A fantastic use of the auto fill is when you want to copy a formula down to the remaining cells in a column. Consider the sales data I have in the spreadsheet below.
I have calculated the year-on-year sales increase in cell D2. I want to replicate this formula for the remaining regions. With D2 selected, I hover the mouse over the bottom-right corner of the cell and my cursor changes to a small black plus-sign.
This tells me that the auto fill feature is available. I can now double-click the bottom-right corner of my selected cell and it will auto fill the formula in that cell down to the remaining rows.
Auto fill is a huge time-saver and prevents you from inputting the formula incorrectly on any of the remaining rows. This is just one feature of auto fill that is essential to learn. You can learn more in my post on 6 Ways To Use The Fill Handle.
Freeze Top Row Or Header
Have you ever worked with a data set in Excel that’s so big that it exceeds the height of your computer screen? Chances are that you have so you’ll know that when you scroll down to see the rows that are not in your current view, you lose the headers that are in the first row.
To keep your top row/header in view when you scroll below the bottom row in view, select any cell within your data set and click on the View tab on the ribbon, then in the Styles group, click on Freeze Panes and then select ‘Freeze Top’ Row.
You will notice a faint grey line is extended across the border between row 1 and row 2 on the spreadsheet. This indicates that the Freeze Panes is active.
Now when you scroll down your data, the top row will remain visible as you scroll. This is extremely helpful when navigating large data sets, allowing you to keep reference of what each column is – particularly when they are populated with similar data types!
Split Large Data Set Into Individual Columns
You may encounter a situation where you need to load a data set into Excel for further analysis or investigation. Often this data will come to you in a standardized form such as CSV (comma separated values) or tab-delimited.
When pasted into Excel, you’ll find that each row of data just sits within 1 cell, rather than splitting out across the columns as you might expect it to.
Take a look at the dummy data set below. It has 5 columns and 10 rows, but when inserting it to Excel, all 5 columns of each row are inserted to a single cell in column A, 1 row per cell.
We can split this data so that each value occupies the adjacent column using the Text to Columns feature.
Select all of your data and then go to:
Data tab –> Data Tools section –> Text to Columns.
The ‘Convert Text to Columns’ wizard dialog box will be presented. The first step is to choose what Excel should look for to split the text into chunks by.
In our example, each column is separated by a comma. We leave the default option of ‘Delimited‘ selected and then click Next.
What Do We Mean By Delimited?
According to Wikipedia, files that use delimiter-separated values store 2-dimensional arrays of data by separating the values in each row with specific delimiter characters, e.g. a comma.
The delimiter value indicates where the next value in that row of data begins. We can tell Excel to look for the delimiter and split text into chunks wherever it sees it. Each value is inserted into the next available column in the row.
Next I will need to specify which delimiter value to split the text on. I want to use a comma, so I need to deselect the default ‘Tab’ and select ‘Comma’.
The Data Preview box gives you an idea of what the split out data will look like.
If the preview looks good, I can click Next.
The final step is to assign each column a data format. For this example, I am going to leave the data format as general and finish the wizard by clicking ‘Finish‘.
The data has now split into 10 rows of 5 columns. Each column represents the specific values for each row. The data set included headers, which have also been split out.
Text to Columns is an awesome feature and I anticipate you will use it frequently.
Conclusion
In this post I showed you my top 5 essential Excel tips and tricks. I hope you can use these to help boost your own productivity.
Let me know in the comment if you learnt anything new today. And feel free to comment your own essential tips and tricks.
