Make the most of Microsoft Excel with these top 8 tips

Microsoft Excel, part of the Microsoft 365 suite of productivity and business tools, is the world’s most popular spreadsheet application. It’s used by businesses for finance, accounting, resource planning and more.

Learning the ins and outs of Excel is one of the most effective ways to take your business to the next level, as Microsoft’s flagship spreadsheet program has been a cornerstone of the modern business world for its whole existence.

With spreadsheeting being so important in the modern business world, here at Dunedin IT, we believe that knowing how to use Excel’s many features and shortcuts will save you time and truly unlock your company’s spreadsheeting potential.

That’s why in this article, we’re going to go over some of the top tips you should be utilising when using Microsoft Excel.

Hide Rows and Columns

When using Excel and going through your data, sometimes you’ll find that there are certain rows and columns that are just getting in the way.

Or even sometimes, there will be data that’s irrelevant currently that you want to hide for just the time being.

In any case, Microsoft Excel allows you to easily hide rows and columns with a few clicks.

The selected rows or columns will now be hidden, and won’t show up when you’re using the spreadsheet.

You can see which parts of your spreadsheet have hidden rows or columns easily too, as there will be two lines in the row or column bar to show hidden cells.

To easily unhide rows or columns, you can double-click the two aforementioned lines. Or, you can select the two adjacent rows or columns and right-click, and select ‘Unhide’.

Hiding rows and columns is particularly useful for long spreadsheets such as day sheets or call logs – as you avoid having to scroll down to the end of the sheet.

Select Using Ctrl + Shift

Excel has many elegant tools you can use to make tasks quick and easy, and selecting cells is no different. Microsoft allows you to quickly select multiple cells using both Shift and Ctrl, which can easily speed up your workflow when working in the program.

Clicking on two cells while pressing Ctrl will select those cells simultaneously, as will any other cell you click while pressing Ctrl.

Shift, on the other hand, allows you to create a box selection of cells. Simply click whatever cell you want to be on the top left corner, and then Shift-click whichever cell is desired for the bottom right corner. Excel will automatically select a rectangular box based on the selected cells.

However, you can use both Ctrl + Shift to select whole rows and columns. By selecting a cell and holding down Ctrl + Shift, you can use your arrow keys to select all the data above (by pressing Up), below (by pressing Down) or left and right (with the respective arrow keys).

Use Quick Analysis

Quick Analysis is another tool you can use to save time and quickly do what would usually be a time-consuming task. This tool lets you easily format your data, or even make a chart out of a set of data, with just a few button clicks.

This tool saves a lot of time and has many functions that can generally improve your Excel experience and workflow.

Automate with IF Functions

Using IF functions to easily automate your spreadsheet is one of the most useful tools in Microsoft Excel.

In any cell, firstly type ‘=IF’. The = symbol indicates to Excel that you’re entering a function, of which there are many you can learn and use. Then, you want to create your new function.

For example, this function is looking to see if C2 is Greater than B2, and will display ‘Over Budget’ if it is. Otherwise, it will display ‘Within Budget’. IF functions work with the templating =IF(Condition, Is, Otherwise), with Is and Otherwise being your two outcomes. You can use this template to simply make your own simple IF functions with ease.

Tag Colleagues in Comments

Collaboration is key to any modern workplace, and Microsoft knows this. This is why Excel — and the other Microsoft 365 programs — have great collaboration features.

One simple feature you should be utilising in collaboration is being able to tag colleagues in comments. This can be super useful and helps improve communication.

To do so, simply right-click wherever you want to comment and select ‘New Comment’. Then, @Mention whoever you want to tag in the comment. This will automatically send them an email notifying them that you’ve tagged them.

Add More Than One Column

Creating multiple columns in Excel is simple, and a great way to add more columns to a finished table or easily expand your spreadsheet.

Make Use of Data Validation

Data validation restricts user entry, ensuring that only correct data is being entered into the spreadsheet. For example, you can restrict entry on certain cells to only allow numerical values between 50 and 100. Or, you can only allow text that contains a certain word.

To do so, select the cells you want to validate, then head over to the ‘Data’ Tab. Then, click ‘Data Validation’. This will bring up a data validation menu which you can use to create new rules for those specific cells.

(Source: https://www.ablebits.com/office-addins-blog/data-validation-excel/)

Bonus Shortcut Keys

Excel has lots of shortcut keys that you can use to easily enter data or complete actions that would usually require you to type or click a few buttons. Some of the best ones are:

• Start editing the current cell: F2 (This is a crucial one, as it speeds up your workflow significantly).
• Insert the current time: Ctrl+Shift+:
• Change a Date’s format: Ctrl+Shift+#
• View all formulas in a sheet: Ctrl + `
• Hide the current column: Ctrl + 0
• Hide the current row: Ctrl + 9
• Strike through the text in a cell: Ctrl + 5
• Switch between currently open workbooks in different windows: Ctrl + F6
• Flick through the sheets in the whole workbook: Ctrl + PageUp/PageDown

Get started with Excel and 365 Today

These tips will help you become an Excel master and truly enhance your workflow, and show how powerful and useful Excel actually is in the modern world. With so many features, it’s no wonder that so many companies still utilise Excel daily.

Getting started with Microsoft 365 is a great way to take your business to the next level. Including Excel, 365 has all of the programs any modern office needs to keep up with the current business world, and has many tools that will make your workplace truly shine.

Get in touch today and see how we can help you get started!