Quick Excel Tips [1]

Sometimes it is the small things which make the biggest differences. The following tips are small, some of them very small, but they might save you a lot of time and a lot of annoyance.

Quick Excel Tip #1

If the formula Tooltip box is in the way, you can move it. Click on the edge of the box and drag it.

I didn’t know this for almost ten years, nobody ever told me.

Quick Excel Tip #2

Press F4 to toggle the $ signs in a formula. There are 4 different scenarios, keep pressing F4 until you reach the one you want.

Bonus tip: it works range cell references and as well as single cell references.

Quick Excel Tip #3

Create multiple Named Ranges at once by using the Create from Selection. When combined with Tip 4, this is a handy feature.

Quick Excel Tip #4

Create a two-dimensional lookup using named ranges and the SUM function.

The space character between ranges is known as the intersection operator; it creates a range based on where the other ranges overlap.

Simple but powerful!

Quick Excel Tip #5

Make a sheet very hidden (invisible). It does not appear in hidden sheets list.

Quick Excel Tip #6

It’s easy to switch the rows and columns in your data.

Select Cells -> Copy.

Select a single cell -> Paste Special -> Transpose – Ta dah!

The data has now changed orientation.

Quick Excel Tip #7

Of all the ways to create a Bullet Chart in Excel, surely this is the easiest: A stacked bar chart with no fill placed on top of another stacked bar chart. Useful for creating a variety of advanced charts.

Bonus Tip: You can even hold ALT while dragging the chart to snap it to the grid.