Every Excel user has their own favorite tip. So we decided to bring some method, data, and science to bear in this hotly debated topic.
We consulted the research of 30 of the world’s leading Excel experts, our own internal Excel experts, grouped up and consolidated the emerging features, and applied a scoring methodology and index to produce a definitive ordered list of the 100 most useful Excel features, hacks, tips and tricks.
These are the top 10 Excel tips as determined by our ranking.
Skills are ranked by usefulness and given a score out of 100. Each skill also has a difficulty rating (out of 5) based on the complexity and sophistication of the feature. Finally, we provide the average time in minutes it takes the average learner to go from no knowledge to proficient.
The videos and gifs inserted in this list were created by Business Insider.
1. Conditional Formatting
Utility: 100
Difficulty: 3
Learn in 180 mins
Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That’s why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up an important business conversation.
2. PivotTables
Utility: 95
Difficulty: 3
Learn in 240 mins
At 4 hours to get to proficiency, you may be put off learning PivotTables but don’t be. Use them to sort, count, total or average data stored in one large spreadsheet and display them in a new table, cut however you want. That’s the key thing here. If you want to look only at sales figures for certain countries, product lines or marketing channels, it’s trivial. Warning: make sure your data is clean first!
3. Paste Special
Utility: 88
Difficulty: 3
Learn in 10 mins
Grabbing (ie Copying) some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there’s a lot you might copy (formatting, value, formula, comments, etc) and sometimes you won’t want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling.
4. Add Multiple Rows
Utility: 87
Difficulty: 0
Learn in 10 mins
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but actually it takes longer, so Right Click is what we recommend. And if you want to add more than one, select as many rows or columns as you’d like to add and then Right Click and add.
5. Absolute References
Utility: 85
Difficulty: 2
Learn in 15 mins
Indispensable! The dollar in front of the letter fixes the column, the dollar sign in front of number fixes the row F4 toggles through the four possible combinations.
6. Print Optimisation
Utility: 84
Difficulty: 3
Learn in 120 mins
Everyone has problems printing from Excel. But just imagine if what you printed were always just what you intended to print. It IS actually possible. There are a few components to this though: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it. You’ll be carrying out this task many, many times in your working life.
7. Extend formula across/down
Utility: 84
Difficulty: 1
Learn in 5 mins
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.
8. Flash Fill
Utility: 84
Difficulty: 2
Learn in 30 mins
Excel developed a mind of its own in 2013. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc). This is much faster and WILL impress people.
9. INDEX-MATCH
Utility: 82
Difficulty: 4
Learn in 45 mins
This is one of the most powerful combinations of Excel functions. You can use it to look up a value in a big table of data and return a corresponding value in that table. Let’s say your company has 10,000 employees and there’s a spreadsheet with all of them in it with lots of information about them like salary, start date, line manager etc. But you have a team of 20 and you’re only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique like email or employee number) in that table and return the desired information for your team. This is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.
10. Filters
Utility: 81
Difficulty: 2
Learn in 60 mins
Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there’s a value e.g. ‘Blue cars’ that you’re looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell color. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colors and vehicles to find your blue car.
Source: http://www.businessinsider.com/excel-tips-and-tricks-2017-11
