Seven Microsoft Excel tricks that will impress your boss (and help your business)

You may use Microsoft Excel at work, but do you use it properly? Discover seven Excel tricks that will impress your boss (and help your business). 

If you want to stand out at work, or work smarter in your own business, then it goes without saying that you need to be super-organised and efficient. A messy desk and chaotic inbox is never going to impress anyone!

Microsoft Excel is a vital tool in many professions – from accountancy to event management – and at every professional level – from entry-level clerk to upper management. But did you know that it’s more than just a handy spreadsheet programme?

Seven Microsoft Excel tricks you may not know (yet)!

There are so many tools and functions on this platform that can be used to reduce your workload – many of which you probably aren’t aware of yet.

If you want to turbo-charge your productivity, and get your boss’s jaw dropping at your efficiency, here are seven to master.

1) Pivot Tables

Pivot Tables are useful for reporting and analysis. Here’s how they work:

  • The PivotTable tool is available under the Insert option.
  • Click on it before highlighting the data set you want to analyse and then click on “OK”.
  • Move the data table by dragging the individual fields in the top half of the slots to the four lower boxes.

This tool will sort all of the data into an easily understandable table so you don’t need to sort through a large collection of data and can focus fully on the most relevant pieces of information.

As the table filters and minimises data that’s not relevant to your concern, it’s easy to make sense of the information.

2) Flash Fill

Flash Fill is useful for tidying up database records and managing mailing lists. It helps users avoid repetitive data entry tasks, like splitting first names from last names, or separating upper case formatting from lower case formatting.

Excel will look for all patterns in user corrections and complete the work without any outside input in similar data entries.

This action is easier to implement. All you need to do is:

  • Click on the column that is present alongside the data.
  • Type in how it should look.
  • Select the correction provided by Excel and the cells below it.
  • Then type CTRL+E in order to flash fill the section.

3) Goal Seek

Goal Seek is useful for setting targets and creating forecasts. It helps you determine what you need to reach a particular goal.

For example, if you want to earn £500,000 in a year from product sales, you can use the Goal Seek tool to determine how many units you would need to sell in order to reach that goal.

This tool is found under Data – Data Tools – What-if Analysis – Goal Seek and is quite easy to use:

  • Select a cell with the required formula in the “Set Cell” box and add the goal “To Value”.
  • Then select “By changing cells” to choose an input value for Excel to adjust and select “OK”.
  • Excel will show the values you need to reach target goals and help create more accurate predictions and forecasts.

4) Conditional Formatting

Conditional Formatting is useful for reporting and analysis for large data sets.

It can be tiresome to wade through large volumes of data and make note of relevant information. If all the information is presented in a similar style and format, it’s easy to miss important pieces of data during analysis.

Conditional analysis lets you highlight certain sets of data so they’re easier to spot. The option is available under the Home – Conditional Formatting button and there are numerous format choices. You can choose conditions like “Less than” or “Greater than”, etc., for highlighting them.

5) Index and Match

Index and Match is useful for finding and pulling information from a complex data set.

Most people opt for VLOOKUP when they want to look for information in a particular data set. But this function has limited capabilities – it only looks at the first column of a table and provides a limited amount of data.

Index and Match does a more thorough search and will provide results from the entire selected dataset. Use Match to return the required search target data from a specific column and another Match to find the row that holds the answer. You then need to feed these two values into Index and leave the rest to Excel.

6) Waterfall Charts

Waterfall Charts are useful for creating reports on performance. They provide a visual representation of the data and can easily showcase upward and downward trends in the numbers.

The chart breaks down flow and numbers to reach a total value and showcase the trend in the data set. You can find the waterfall chart under the Insert Option. Just select the set of data to chart before choosing the Waterfall chart from the Waterfall or Stock chart option.

7) Forecasting

Forecasting is useful for projecting future results and outcomes and modelling expectations in performance.

It works by analysing two sets of corresponding data, and projecting future values based on the information in them.

You also have the option to include room for errors and reasonable changes in data sets. Forecast calculates upper and lower confidence margins as well. Here’s how it works:

  • To get the value, you need to select corresponding data sets and go to the Forecast sheet option available under Data – Forecast.
  • In the “Create Forecast Worksheet” dialogue box, choose between the line or column chart and click create.
  • The data gets organised and the chart will be available immediately.

Are you using Excel to its full potential?

These are just some of the numerous options and functions available in Excel. You can also explore tools like 3D SUM, Arrays, $ and &, etc., to improve overall productivity and use Excel to its maximum potential.

It doesn’t take too much time to learn and understand these Excel tools and functions, but they can make a big difference in how you work.

It’s always a good idea to have a comprehensive understanding of how Excel works – either to work smarter in your career, or for your own business. (Professional Excel training and advanced knowledge will also make your CV more appealing.)

With many excellent and affordable online Excel training programmes available, it’s easy to steal a march on your competition and get to the top of your game – so what are you waiting for?

Ashley Andrews is an Inbound Marketing Strategist at Activia Training, a UK-based training provider specialising in improving delegates’ workplace performance in business skills, management development and IT applications.

Ashley is passionate about sales and management issues, and regularly blogs about these – and many other topics – on the Activia blog.