Data is a commodity we didn’t understand the value of until the tech boom took everything by storm. Now, we’re generating more data than all the past cultures combined. Something to really think about, isn’t it? However the daunting aspect to all this data is managing it efficiently. Microsoft Excel has been and still is the ultimate tool to manage massive amounts of data. So on this 35th anniversary of the first Excel we’re glad to bring you 10 Powerful excel functions that make work easier.
Why Excel is still the BEST
If you’re wondering how Excel has stayed relevant through the career span of a whole generation, the answer is constant expansion. Microsoft has consistently expanded the functionality of Excel while maintaining the integrity of the application. The plethora of functions and features currently available on a standalone installation of Excel dwarfs any other Microsoft product.
While all Excel functions have a use scenario, no single user needs every Excel feature themselves. Sift through the 500+ functions, and you’ll be left with 100 or so useful functions and features for the majority of modern knowledge workers.
We’ve selected 10 functions which are especially easy to learn and can make a significant difference in productivity. Check out Excel Essentials: The Complete Excel Course for further in-depth knowledge.
10 Powerful Excel Functions
Take your excel skills to the next level with the following powerful excel functions. So, let’st get started.
1. INDEX-MATCH (45 minutes to learn)
While VLOOKUP is handy for looking up the value in one column and returning a corresponding value from another column, INDEX and MATCH are the popular and most powerful among Excel functions for looking up data.
These functions are invaluable when used on their own, combining them taps into the full potential. Apart from making you look like an Excel whiz to your colleagues and manager, these functions actually save a lot of time. Here’s how these functions work:
VLOOKUP has its limitations. It can only look up values from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH solves this so you can look up a value anywhere in the lookup table regardless of its position.
Assume you have a spreadsheet with a list of products as in the image. You have columns titled “Product Number”, “Profit”, “Product Name”, and “Revenue”. You have a list of the product names on another spreadsheet. If you want to look up how much profit each product has generated, This is the perfect scenario for INDEX and MATCH.
You’ll be using the product name (our lookup value) to look up the profit. VLOOKUP would not work because the product name sits to the right of the profit.
The syntax would be:
=INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
Here’s a good way to remember how it works:
=INDEX (Column containing return value, MATCH (Lookup Value, Column to lookup against, “0” ))
“0” gives you an exact match. You can match against less than “-1” or greater than “1” as well.
At first and even second glance, INDEX and MATCH seem complex. You’ll certainly need some practice, but it’s worth getting your head around considering the amount of time and effort it saves.
2. SUM (2 minutes to learn)
This is one of the first and most basic functions you’re likely to learn in Excel — summing all data in a row or column. The traditional method is to select a cell and write down the SUM equation with the range of data.
However there’s a simpler way to do this. Just select the cell at the end of your data range and press Alt + ”+”. Follow the image to make it clear.
3. CTRL Z / CTRL Y (1 minutes to learn)
Similar to all other windows applications, this is your get-out-of-jail-free card. Yes, that’s Ctrl+Z. However most people don’t know what the opposite shortcut is. Ctrl+Y does the opposite — or redo as more commonly known. The two work in tandem allowing you to cycle through iterations of your work until you find the correct one.
4. Remove Duplicates (10 minutes to learn)
This is one of the distinctly important features Excel has to offer. Remove Duplicates does exactly what it says. it removes duplicates in a given range of data.You can find it on the Data tab in the Data Tools section of the Ribbon.
If you just want to highlight duplicates, you can also use Conditional Formatting. The shortcut to get you there is Alt+H+ L.
5. Freeze Panes (15 minutes to learn)
Ever scroll down a large table only to forget which columns are which? Freeze Panes is the solution to this particular problem. You can freeze the top row, first column or any number of either so it stays in display regardless of where you’re in the data sheet. This is by far the neatest among Excel functions in our view.
Identify the columns and rows you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab>Window>Freeze Panes or use Alt+W+F.
6. F4 (10 minutes to learn)
There are two especially satisfying ways to use F4 in Excel. Firstly, when creating an Absolute Reference, F4 toggles you through the various options. The second is perhaps the most important, that could seriously improve your Excel productivity. Pressing F4 repeats your last action, when available.
7. CTRL + Arrows (5 minutes to learn)
The amount of data stored in each Excel sheet is unlimited. Quite often you’ll find yourself scrolling on and on to reach the end of a row or column. Instead you could use the Ctrl key to jump across to the last cell in a column or row.
Warning: If there are gaps in your column or row this will just take you down to the first gap. In this case Ctrl+End the end of the range.
8. Paste Special (10 minutes to learn)
Copy and paste needs no explanation. However, often it carries over a format we don’t want, or we copy a formula over, when instead we just the values.
These little quirks can take time to fix, which is why Paste Special is there. Paste Special lets you pick exactly which elements of the copied cell you bring over. After you’ve copied your cell with Ctrl+C hit Ctrl+Alt+V or, go to the Clipboard section of the Home ribbon, to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values.
9. Add Multiple Rows (2 minutes to learn)
Often you’ll 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. Moreover, highlighting the number of rows you want to add (say 5) and using right click to insert the entire column or row is quicker. Especially when adding in bulk.
10. Flash Fill (30 minutes to learn)
Flash Fill automatically fills your data when it senses a pattern. Excel developed a mind of its own with this feature.
Suppose you have a list of product numbers in column A, e.g. EP-01. You can AutoFill according to this name by simple following the steps shown in the image.
Ending Notes
Trust us when we say this is just the tip of the iceberg. There’s a saying that “ 4 years of statistical science is available on Excel”. These Excel functions are fundamental knowledge at this point, especially since even clerical work is carried out using Excel. You can hold a small team meeting focusing on these functions to boost productivity by an order of magnitude.
Excel contains anything you might need to manipulate and analyse data efficiently. To a point where the efficiency actually depends on the user more than anything else. So why waste time on mundane numbers when you can use it better to actually make sense of those numbers. After all data means nothing if you can’t organise it neatly and intuitively.
0 responses on "Top 10 Powerful Excel Functions"