Useful MS Excel tricks
30 years since entering the market Microsoft Excel remains the most widely used software by businesses. Despite its wide use, the full capabilities of Excel are still poorly understood. Of 100,000 workers we tested over the past three years, less than half know what Conditional Formatting - an essential feature - even does.
So what are Excel's essentials? We reviewed articles written by Excel experts and combined this with aggregated data from thousands of our users to compile a list of the 100 most useful Excel functions and features including PivotTables, Absolute References, Conditional Formatting and more.
For all those in need of a quick dose of tips to improve their Excel proficiency check out the top 10 tips below. But do download the full 100 and make your way through it to get the most out of this wonderful guide.
So let's start!
1. Conditional Formatting
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
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 n1ew 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
Grabbing
(i.e. 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 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. It's annoying and ugly to plonk in
formatting from elsewhere. So just copy the values and all you'll get is
the text, number, whatever the value is. The shortcut after copying the
cell (Ctrl C) is Alt E S V - easier to do than it sounds. The other big
one is Transpose. This flips rows and columns around in seconds.
Shortcut Alt E S E.
4. Add Multiple Rows
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but actually it takes longer than just right-clicking on the row numbers on the left of the Excel display. So Right Click is our recommendation. 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
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. Try it out with the following exercise. Type out three foods horizontally in cells B1, C1, D1 (Olives, Granola, Tomatoes) and three colours in cells A2, B2, C2 (Green, Blue, Yellow). Now type in cell B2 '=A2&" "&B1'. Congratulations: Green Olives! Now - and here's the exercise - add dollar signs so that when you copy the formula across you get green everything. Or just Granola, but of different colours. Experiment!
6. Print Optimisation
Everyone has problems printing from Excel. But imagine if what you printed were always just what you intended. It IS possible. But there are a few components to this: 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
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
9. INDEX-MATCH
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. It is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.
10. Filters
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 colour. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colours and vehicles to find your blue car. Alt D F F is the shortcut (easier than it sounds - give it a go). Conditional Formatting and Sorting serve related purposes. Sorting involves rearranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualisation. Filtering is fast and effective. Choose well.
If you have any query or want any help related to excel feel free to join our Telegram channel and message us..
Channel link :
0 Comments