Microsoft Excel Tips & Tricks

Get more from Office with these time-saving Microsoft Excel tips – straight from the experts.

Instant PivotTables: No experience required

Instant PivotTables: No experience required

New to PivotTables? No problem! Excel can create one for you.

Go to Insert > Recommended PivotTables, select your data range, and choose the one that looks best.


Chart your data: it's as easy as ALT+F1

Chart your data: it’s as easy as ALT+F1

Select any cell in your data range and press Alt+F1. Voila! You now have a chart!


Tables make your data pop

Tables make your data pop

Want to turn ordinary data cells into a full-fledged Excel table? Just click anywhere inside your data and press Ctrl+T.

A table provides all sorts of handy conveniences, like alternating coloured rows and adding AutoFilter buttons. Plus, it just looks better, too.


Fill cells in a flash

Fill cells in a flash

Spend less time typing with Flash Fill.

Just start typing in a column to the right of your data. In this example, we typed Smith, then North. Excel then did its magic and gave suggestions for the rest of the cells. To accept the suggestions, press Enter, and you’re good to go.


Accessible templates are the practical choice

Accessible templates are the practical choice

You’ve worked hard on your data. Make sure all your stakeholders can appreciate it by using accessible templates.

Click File > New, and search for “accessible.” Choose Accessible Template Sampler to see all of the accessible templates available in Excel.


Choosy analysts use IFS

Choosy analysts use IFS

When you need a formula that does a bunch of “if this, then that” calculations, use IFS. IFS evaluates multiple conditions so nested formulas aren’t required. In this example, the IFS function looks at a cell in the Score column, and depending on the value of the score, shows a grade letter.

IFS is available in the latest version of Excel for Office 365.


To pivot or not to pivot

To pivot or not to pivot

Let’s say you get a report that has data, but you can’t create a PivotTable because it’s already pivoted. Unpivot it!

  1. Select your data, click Data > From Table/Range > OK to create a table.
  2. When Query Editor opens, hold down the CTRL key to select the columns that you want to unpivot.
  3. Go to Transform > Unpivot Columns > Only selected columns.
  4. Go to Home > Close & Load to place the unpivoted data in a new sheet in Excel.

Now you can make your own PivotTable.


Get your total by going formula free

Get your total by going formula free

Here’s a handy tip for tables: Add a Total Row without writing a formula. Just click anywhere inside an Excel table. Then press Ctrl+Shift+T and Excel will add the Total Row to the bottom.


Freeze it and forget about scrolling back and forth

Freeze it and forget about scrolling back and forth

Want the top row and first column to stick around while you scroll through your worksheet? Select a cell, then click View > Freeze Panes > Freeze Panes. You’ll notice two “frozen lines” will appear, which intersect at the top-left corner of the cell. Everything to the left of the column’s frozen line stays put while you scroll side-to-side; everything above the row’s frozen line stays when you scroll up and down.


Where's that button? Ask Tell Me

Where’s that button? Ask Tell Me

You’ve used a certain Excel feature before... but you can’t remember where it is. No problem, Tell Me can help.

Just click in the box labeled Tell me what you want to do (it’s the one with the light bulb), and search for a button, menu, function – whatever you want. In this example we searched for “freeze” to find the button for freezing columns and rows. What will you search for?


Slice data your way

Slice data your way

Slicers are buttons that let you filter data easily.

Select any range in a table or PivotTable, and go to Insert > Slicer. Select the column you want to filter by. When you’re done, you’ve got handy buttons that let you filter.


What's your status? See it instantly

What’s your status? See it instantly

The Status Bar shows averages, counts, and sums – without typing out complicated formulas! Just select some cells in your table, and then gaze your eyes on the lower right-hand corner of the Excel window. There you’ll see instant stats about your selection.


Make it visual, make it stick with Data Bars

Make it visual, make it stick with Data Bars

Want to communicate your numbers visually? Data Bars can help.

Select your data range, then go to Home > Conditional Formatting > Data Bars, and select a colour scheme.


Find the needle in the haystack with VLOOKUP

Find the needle in the haystack with VLOOKUP

VLOOKUP finds information in another table, and brings back related information to the cells you’re working with. And, with the TRUE argument, it will find the information with an approximate match. In this example it looks up 0.92, and finds the closest match: 0.9. Then it brings back the value in the next column to the right of 0.9 which is the letter A.

What our participants say about our short courses...

The University of Newcastle, Sydney Campus is a leading provider of short courses in Australia, with industry qualified and experienced educators that bring up-to-date real-world skills directly to the classroom.