Excel power users: 5 essential tips for spreadsheet mastery

This is the default value for this text field

Author: Jake, Marketing  
Last updated: August 3, 2017

Excel power users – listen up. Here’s a selection of tips and tricks to help you save time, reduce errors and get more from your Excel experience.

1. Master keyboard shortcuts.

If you want to master Excel, stop using your mouse. To get started, use Microsoft's table of keyboard shortcuts and function keys. For extra help, check out the free app called KeyRocket. It tracks your mouse as you use Excel and lets you know if there are any shortcuts you could be using.

excel-1 (1).jpg

2. Use INDEX MATCH instead of VLOOKUP.

As an Excel power user, you’ve probably used VLOOKUP to find and transfer data between tables more times than you care to remember. But VLOOKUP uses more processing power and is more prone to errors than the INDEX and MATCH functions used together.

As a refresher, the INDEX value returns a value based on a defined column and row number, while the MATCH function returns a number based on the relative position of a lookup value within a defined column. Their syntax is as follows:

=INDEX (array, row number)

=MATCH (lookup value, lookup array, match type)

When used together, the number that the MATCH function returns becomes the row number in the INDEX function. This looks like:

=INDEX (array, MATCH formula)

This combined function will return the same value as the VLOOKUP function, while using considerably less processing power. For a more detailed look at the differences between VLOOKUP and INDEX MATCH, click here.

excel-2 (1).jpg

3. Multiply matrices using the MMULT function.

It’s easier to multiply matrices of numerals than you might think. Simply select the box where you’d like your matrix product to appear, and then enter MMULT(Array1, Array2) in the function dialogue box. Just make sure the number of columns and rows match, and then press Ctrl+Shift+Enter to produce your result.

Click here for more information on the MMULT function.

4.  Hide cell errors from your spreadsheet printouts.

Here’s a handy feature that removes any unsightly errors from your documents, replacing them with blank cells. This is great if you’re in a hurry and don’t have time to fix the errors before a presentation or staff meeting.

First, click on the Page Layout option tab. In the lower right-hand corner of the Page Setup option group is the dialogue launcher, which opens up a new Page Setup window. Click the Sheet tab in this new window, where you’ll see a “Cell errors as:” field. Change this from “displayed” to “<blank>”, and you’re all set.

excel-3 (1).jpg

5. Use a VBA function to format dates into quarters or weeks.

While it’s possible to convert a column of dates in Excel to months, the software’s TEXT function can’t convert it into quarters or weeks. That’s why this Excel power user tip uses a function from the Visual Basic for Applications (VBA). Simply open the VBA using Alt+F11, and click on Module from the Insert dropdown menu. Then type the following into the terminal:

Function MyFormat(Cell, FormatCode)

MyFormat = Format(Cell, FormatCode)

End Function

This will allow you to use the extra functions that are in VBA but not available in Excel.

Assuming your date is in A1, type =MyFormat(A1,“Q”) for the quarter, or =MyFormat(A1, “WW”) for the week. If you have more dates in column A, you can then press Ctrl+Enter to apply the function to the whole column.

For more info, here’s a video of Excel power user extraordinaire Bill Jelen walking you through the entire process.

Every power user should strive for Excel mastery.

We know these tips have barely scratched the surface of Excel’s true potential – mastering a tool this versatile and complex is an ongoing process. Even as a power user, you’ll always find ways to improve your skills and apply new efficiencies to your professional life. That’s why it’s so important to draw on the experience and expertise of industry leaders and innovators.

If you’re ready to shift into gear and take your Excel skills to the next level, download our guide here.

Topics: ACE, Analytics, Data, Excel, Big Data

KNOWLEDGE IS POWERGet weekly email insights from RedPixie

Free GuideLearn how to choose the right IT partner with our free guide
surface banner.jpg