8 Essential Tips Businesses Should Know About Excel

Business leaders have often felt left behind by the growing application of analytics and big data, but the truth is existing datasets can function just the same to power business decisions. Contrary to common belief, tools like Microsoft Excel and Google Sheets are not exclusively used by finance and accounting departments but also by power users who can extract meaningful information from datasets.

To help you become an Excel pro, we've compiled a power user's cheat sheet on all things Excel and Sheets – from rethinking how data is gathered to mastering shortcuts, our eight tips will help you view datasets in a far more meaningful way.

#1 Data must tell a story

The real art to data analysis lies in the ways and methods of interpretation. Turning data into a story really isn't easy, but when it's achieved, it will allow you to take data and turn it into meaningful information.

And the difference between data and meaningful information is this: data is raw and is a set of figures and details with no analysis that could take hours, days or even months to analyse or interpret correctly.

Meaningful information, on the other hand, is what you get out of a sound dataset. Now, of course, if the data isn't set up correctly in the first place, then the information may simply be inconclusive. And in some cases, the data may even revel little to no meaningful information.

To find and extract a story from data, you'll need to take your audience on a journey. As with anything, it must start with a beginning and include an end – and it's at the end here where stories are often found from a sound set of data. Consider these from the beginning:

What is the problem?

What did you do to capture the data?

How did the data make you feel? Were you surprised? Did it concern you?

What did the data say?

How did/does that shape the next decision or project?

What can you change for the next dataset?

#2 Know what you want before you begin

Yes, this ironically comes in second place but we assure you that it makes sense – if you don't know what you're looking for then how can you find the right way to capture the correct data? And if the data capture process is wrong, you'll probably struggle to get a meaningful set of information out of the dataset.

Take customer satisfaction surveys as an example that could easily be qualitative or quantitative. Quantitative data means you'll be dealing with numbers and no text, which makes performing calculations a breeze when needed. Whereas qualitative data will offer opinions and sentiments usually in a sentence or a few words that will be much harder and more time consuming to analyse. Although it does go without saying that they are also an insightful and valuable set of data.

#3 Don't think of data last

Cheeky admission but this is really an extension of rule one. When you begin a project or are given a task, try to understand the type of data you need before you create or dive into a project and process plan. To begin, open the data programme you use most and then proceed to build your dataset but first, ask yourself this: what do you need to outline here to help you tell a story?

When you have the end table that demonstrates all the info you need, work out the minimum amount of data required. After that, build a mechanism to capture that data using tools like SurveyMonkey or Google Forms – both of which are great for efficiently capturing data.

#4 Leave space and format your sheets properly

This is down to personal preference but, ideally, sheets should be formatted with the end-user experience in mind. Consider formatting it in a way that it is unique to you and keep in mind that a little extra effort to make it more appealing and easy to understand goes a long way.

Top tip: give tables titles that anyone can understand. And make sure to leave enough space to the left of the table and above the title bar so that readers can include comments or add a formula if needed.

#5 Don't use a mouse

Did you know that there are over 500 shortcuts in Excel? And while most would switch back and forth between using the keyboard and the mouse, the most efficient way to use Excel or Sheets is by ditching the mouse. Listed below are 13 of the most basic and essential shortcuts that is far faster than clicking each selection or filter manually. They are:

CTRL+X | Selects a range you want to cut out. Instead of right-clicking and selecting 'cut' simply press CTRL+X.

CTRL+C | Selects a range you want to copy. Instead of right-clicking and selecting 'copy' simply press CTRL+C.

CTRL+V | Selects a cell you want to paste to. Instead of right-clicking and selecting 'cut' simply press CTRL+V.

SHIFT+A | This selects all the sheet. SHIFT+A is great for formatting but not the best for selecting a set range.

CTRL+SHIFT+RIGHT ARROW | Selects the furthest cell on the left. Press CTRL+SHIFT+RIGHT ARROW and you will have highlighted the dataset unless, of course, you have blank columns.

CTRL+SHIFT+DOWN ARROW | Selects the first cell at the top of a column. Press CTRL+SHIFT+DOWN ARROW and you will have highlighted the dataset except when it is blank.

If you use CTRL+SHIFT+DOWN ARROW and CTRL+SHIFT+RIGHT ARROW you have the whole dataset selected without you having to drag the mouse down for hours!

CTRL+D | Selects the cell above and the cell below. Press CTRL+D and you’ll have duplicated the cell above.

CTRL+R | Like CTRL+D, CTRL+R takes a value from the left and duplicates it to the right.

CTRL+F | This is the keyboard shortcut to find.

CTRL+H | This shortcut will save you loads of time as CTRL+H is 'Find and Replace'. So if you are editing formulas and don't want to manually do the changes, try using CTRL+H.

ALT+D+F+F | ALT+D+F+F adds a filter to the first populated row. If there is no data, it will filter the top row.

ALT+D+F+S | This removes the criteria from the filter but leaves it in place.

F2 | F2 allows you to edit the formula of a cell without having to click in it.

For Mac users, use Option in place of Alt.

#6 Use your dollars wisely

All Excel power users lock their formulas properly and, now you too can do so by using the $ sign to lock a column or a row. Adding the dollar sign in front of the letter and the number allows you to lock the cell. Here are a few examples on how it works:

=VLOOKUP(A1,A:B,1,0) – In this formula, no columns, rows or cells are locked. This means that if you copy and paste the formula then the columns, rows and cells would also change.

=VLOOKUP($A1,A:B,1,0) – A dollar sign is added at the front of the column letter. With this, if you were to copy and paste the formula into a different column then it would still be looking up column a, however, if you change the row, the row would update accordingly.

=VLOOKUP(A$1,A:B,1,0) – Here a dollar sign is added at the front of the row number. This means that if you were to copy and paste the formula into a different column then, it would look up a different column. If you change the row, however, it would remain as row 1.

=VLOOKUP($A$1,A:B,1,0) – Two dollar signs are added in this example, one in front of the column letter and another in front of the row number. The dollar signs added on in this formula means that no matter where the formula is placed, it would still look up cell A1.

#7 Think about visual data

Data can sometimes be overwhelming as there could be too much to look at or too little time to process it fully. To avoid these scenarios, always present your data in a visual format and do so in the cleanest way possible. One thing to remember, however, is that different people would want their data presented differently as this is entirely down to preference so ensure your data is engaging to all readers by creating and presenting it in multiple formats within the same sheet.

Top tip: turn this into a habit by adding visual elements to all datasets as a way to present your findings. The quicker you adopt this, the faster you'll be adding real value to your reports!

#8 Statistics or facts

Mark Twain once said that “facts are stubborn little things” and the fact is, data does not lie. Make sure that all information presented is clear to your readers that it is extracted based on the findings of your statistical analysis. Why? Because numbers are often easily manipulated and can be misleading without the right context or explanation so, scepticism generally follows when it comes to stats and findings.

In summary, as data allows businesses to understand meaningful information that drives decisions, tools like Excel and Sheets are two simple but highly-effective tools to master. Whether it’s using data to streamline strategies or to identify goals and expectations, companies of all sizes can benefit from becoming a better qualitative storyteller.

Find out What Really Matters to Employees in the Workplace.