When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.
In October 2018 I shared lots of keyboard shortcuts. Download the materials using the button below and watch the video.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
wrap text and other useful formats
applying row and column grouping
selecting a table quickly
copying visible cells only
apply and remove Freeze Panes
returning after following a hyperlink
how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.
Advanced Filter Keyboard Shortcut
To open the Advanced Filter dialog use Alt A Q pressed in sequence, not held down.
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
Conditional Format Feedback
The Conditional Format webinars I ran in August were popular and well received- 94% feedback score.
An email I received recently is shown below.
To watch the recording and download the materials click here.
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Printing Tip
If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.
On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.
This removes all the colour and prints in black only,
You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Data entry and calculating
In some large models Excel may calculate for a few seconds after you make an entry.
In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.
Type your entries as fast as you like. Excel will finish calculating once you are done.
When you only want to copy the visible cells Excel has a special option that allows you to select visible cells only before copying. But there a way to make that the default option.
When you create a checkbox you need to link it to a cell on a sheet to be able to use its result. The user could overwrite that linked cell with a value or text and affect formulas that are using the checkbox linked cell. You can add a validation to make sure the linked cell only contains TRUE or FALSE.
Have you tried using different line colours in Excel? I’ve been experimenting with using grey lines instead of the default thin black lines when formatting reports.
A listing of all the DAX functions with their syntax.
DAX functions are used in Power BI and PowerPivot for Excel.
It is a reference site, not a training site.
It has been created by Marco Russo and Alberto Ferrari – legends in Power BI and all things DAX.
Link to DAX Guide