Top 10 Quick Excel Tips
Posted:
June 06, 2020
10 Most Useful & Simple Microsoft Excel Tips
A survey of several hundred office staff we ran suggests we spend more than 10% of our working lives spreadsheeting, and for those working in research and development or finance, it’s more like 30%, or 2.5 hours a day.
Imagine, then, if this substantial proportion of the global workforce were a little better at using the application. Time would be saved, and productivity would improve.
Imagine, then, if this substantial proportion of the global workforce were a little better at using the application. Time would be saved, and productivity would improve.
Here selected ten which are especially easy to learn and which can make a material difference to productivity. For all those in need of a quick dose of tips to improve their Excel proficiency check out the top 10 tips below.
1. CTRL Z / CTRL Y
This is your Excel get-out-of-jail-free card. If you aren’t using Ctrl Z to undo mistakes in Excel, then you should be. What many people don’t know is that Ctrl Y does the opposite — redo. The two work in tandem very nicely, and you cycle through iterations of your work until you find the right one.
2. F4
There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. For example, if you’ve just applied a border to one cell, use F4 to apply it to others.
3. CTRL + Arrows
If you’ve found yourself scrolling through a dataset to reach the bottom of it, stop right now and start using Ctrl + the arrow keys! This simple shortcut takes you straight to the end of the data in a column or row you are using. Combine it with Ctrl, Shift to highlight/select large areas of data in seconds.
4. Extend Formula Across/Down
The beauty of Excel is its easy scalability. Get the formula right once and Excel will churn out the right calculation a million times. The + cross hair is handy. Double clicking it will take it all the way down if you have continuous data. Sometimes a copy and paste (either regular paste or paste formulas) will be faster for you.
5. Flash Fill
Excel developed a mind of its own in 2013 with this feature. Flash Fill automatically fills your data when it senses a pattern. Suppose you have a list of product numbers in the first ten cells of column A, e.g. ‘ABC-00001’ to ‘ABC-00010’ and you only need the numbers after the ‘-’. You can easily discard the ‘ABC’ using Flash Fill. Pre-2013 this was possible, but relied on a combination of functions (FIND, LEFT, &, etc). Now, this is much faster and will impress people. Establish the pattern by typing ‘00001’ into the first blank cell. If Flash Fill is turned on (File Options, Advanced) just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Just hit the Enter key to accept. Or, get it going manually by clicking Data > Flash Fill, or Ctrl+E. Flash Fill is like magic, and can be used in many different scenarios. It’s a fantastic time saver when you need to input or change a lot of data quickly and accurately. Flash Fill is a jaw-dropping feature.
6. Paste Special
Copy and paste is one of the simplest and most used functions in Excel. But we often carry over a format we don’t want, or we copy a formula over, when instead we just want a value. These little frustrations can take time to fix, which is why Paste Special is so… special. Paste Special enables you to pick which elements of the copied cell you bring over. After you’ve copied your cell (Ctr+C) hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values — probably the most common use of Paste Special..
7. Add Multiple Rows
We often need to add new rows between existing rows. The shortcut (Ctrl, shift, +) is pretty handy, especially as you can toggle the + to add multiple rows. Often, just highlighting the number of rows you want to add (say 5) and using right click, insert is quicker when adding in bulk as it will add the number of rows you’ve highlighted.
8. SUM
This is one of the first functions you’re likely to learn in Excel — how to sum a row or column. But did you know you can select the cell at the end of a row or column and press Alt + to do this functions in seconds?
9. Freeze Panes
Ever scroll down a large table of data only to forget which columns are which? Freeze Panes is the answer here. You can freeze just the top row, first column or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt W F is the shortcut.
10. Remove Duplicates
This is so simple and quick to use. Remove Duplicates does exactly what you’d expect — it removes the duplicates in any given range of data. Our advice is to remove the values you want to dedupe and place them in another sheet. It’s found on the Data tab in the Data Tools section of the Ribbon. If you just want to highlight duplicates, you can do this using Conditional Formatting. The shortcut to get you there is Alt H L. (Or find it on the Home ribbon under Styles).
**Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data which has no gaps.
**Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data which has no gaps.
Harness just a few of these ten items, and you can transform your typical work day. Whether you want to help justify data-driven business decisions at a high level, or simply get home to your family earlier, mastering the right Excel functions is a quick and easy way to maximize your productivity.
Related Products
0 Comment(s)
Recent Posts
