I’ve been using Excel for more than 15 years, 10 of which for the largest financial data provider in the world. I have trained people in Excel for several years and now running workshops and teach Data Analytics with Excel at Xccelerate. What have I learned through all of this?
I’ve found that many people aren’t aware of some of Excel’s most useful tools, which could help them save a lot of time at work; it’s software that many of us have taken for granted throughout our schooling years but don’t push ourselves to master in the working world.
Without further ado, here are some of the most powerful features in Excel that you can most likely leverage, regardless of your current role (and impress your bosses and colleagues with):
1 - Power Query
Are you spending a lot of time extracting and formatting the same data in Excel? There is a big chance you could automate all of this using Power Query. Power Query is an Excel ETL (Extract, Transform and Load) tool. You can pull data from multiple sources (CSV, TXT, SQL, Hadoop, SalesForce, Web) and compile it into one table which can be refreshed in one click. For example, let’s say you have 1000s of files with the same format that you want to compile into one file.With one simple query you can get all the data in a report with just a few clicks!
2 - Power Pivot
Power Pivot is one of the most powerful features in Excel and hands down one of the best enhancements in the past few years. This works in pair with Power Query: you use Power Query to acquire / format and load the data, then you use Power Pivot to do your analysis. You can link entire sets of data together, then create a data model that can handle millions of data points! You can then connect other tools like Power View/Power Map/Pivot Chart to visualize your insights.
3 - Conditional Formatting
Even though, most people know how to use the premade conditional formatting options, you can achieve a lot more by writing your own conditions. You can see on the screenshot below how you can highlight cells in green that have a higher return than the average of the whole list of stocks.
4 - Forecast Tool
Are you manually doing your own forecasting?You can now do it straight in Excel using the forecasting tool. You can find the option on the Data tab. These are forecasted sales figures for Tencent for example:
5 - INDEX/MATCH
If you want to do a lookup in Excel you would usually use a VLOOKUP or HLOOKUP. However, many people are not familiar with INDEX/MATCH; once you understand the logic around it you will quickly understand that INDEX/MATCH is more powerful and let you do lookups both horizontally and vertically. Example below on getting the 1 Week return of a specific stock:
6 - Shortcuts
Ultimately you should aim to use your keyboard as much as possible to be more efficient using Excel. There are multiple cheat sheets online for Excel shortcuts. Below are some shortcuts that you might find quite useful:
Alt + Q to access the
Ctrl + 1 when you select a chart to bring the formatting panel.
Ctrl + E Flash fill
Create a quick bar chart -> select your values, then press F11, it will automatically insert a bar chart in a new sheet.
Ctrl + Arrows – you can navigate one your table using Ctrl and the arrows.
Highlight entire row/column then Ctrl Shift plus sign, this will insert a row or column. To delete you can press Ctrl and minus sign.
Ctrl + ; to add today’s date, Ctrl Shift + ; to add current time.
7 - Flash Fill
If you are doing a task that follows the same pattern, you can select the entire range of cells and press Ctrl + E, Excel will then apply it to the entire range. As per below, I’m removing the dot in the stock Ticker and replacing it with a space:
8 - Sparklines
This is another option in Excel that lets you visualize data within a cell. You can just select your data, click on insert -> Win/Loss. Which will give you a quick visualization of stock returns, for example.
9 – Azure Machine Learning add-in
You can now use different machine learning techniques using the Microsoft Azure Machine Learning add-in. You can either use an existing model or create your own on the Azure Studio website. It’s very easy to set up and let you access very powerful tools. For example, using the Sentiment Analysis tool, I pulled a list of stocks headlines and use the tool to see which headlines are negative/neutral/positive. All it takes is a few clicks to get this setup. This is a simple example, it could be used to analyze information on Twitter.
If you start using the tools I’ve shared here you could save a lot of time and run more in-depth analysis to help leverage your organization’s data. What about you? What tools are you using in Excel that people may not be aware of? Send me your thoughts and I might share it in an upcoming post!
If you’re in Hong Kong, be sure to take a look at our upcoming Data Analytics with Excel course to take your skills to the next level.
Need more advice?
If you are at a choice point in your career and need someone to help you navigate professional challenges. You can make an appointment to our complimentary 1-on-1 Career Consultation and receive personalised career advice.
