This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

Quick Excel Tips [1]

Quick Excel Tips

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.

Excel Tip - Move Tooltip

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.

F4 to Toggle Absolute

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.

Create Named Ranges - Fast

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!

Intersection Operator with Named Ranges

Quick Excel Tip #5

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

Make Worksheet Invisible

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.

Quickly Transpose Data

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.

Layer Charts

Quick Excel Tip #8

If the [+] and [-] for Group Outlining is not in the correct place change the Outline direction for the sheet.

Change Group Outline Direction

Quick Excel Tip #9

Do not unprotect multiple sheets one at a time, that will take too long.

Use File -> Info -> Unprotect, it’s is much faster.

Unprotect sheets quickly

Quick Excel Tip #10

Double click the format painter to apply the format over and over (Press Esc Key when finished).

Double Click Format Painter

Save

Leave a Reply

Your email address will not be published. Required fields are marked *