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.
Quick Excel Tip #8
If the [+] and [-] for Group Outlining is not in the correct place change the Outline direction for the sheet.
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.
Quick Excel Tip #10
Double click the format painter to apply the format over and over (Press Esc Key when finished).