One Minute to Excel #12 – Hide Cell Entries

It is a special format

You can’t hide a cell, but you can stop the cell value from displaying on the sheet.

It involves a custom number format.

Open the Power Query Editor

If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.

 

One Minute to Excel #11 – Add % to a Pivot Table

It is not intuitive

Often people perform calculations off to the right of Pivot Tables to calculate percentages.

In this short video I show you those calculations can be done inside the Pivot Table itself.

The solution is not intuitive, but it is easy.

This example builds upon the previous One Minute to Excel post.

Data Validation Shortcut

I typically use the shortcut Alt A V V pressed in sequence (not held down) to open the Data Validation dialog.

I like it because you can do it one-handed. The A and V are close together.

There is another shortcut that works the same. Again, pressed in sequence and not held down. Alt D L

Use the one that is easiest for you.

Pivot Table Shortcuts

Here’s a couple of useful keyboard shortcuts for Pivot Tables.

Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.

Alt J T L  – pressed in sequence, not held down.

 

To add Subtotals above the entries in an existing Pivot Table.

Alt J Y T T – again pressed in sequence, not held down.

One Minute to Excel #10 – Create a Pivot Table

It is easy

Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.

In the short video we see how easy they are.

Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.

Save and Close in Excel

Let's create a macro

I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.

I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.

Expand the Formula Bar

The Formula Bar can be expanded using the icon on the end. But there is a keyboard shortcut as well.

You can expand it or return it to one line using the keyboard shortcut Ctrl + Shift + U.

Thanks to Excel MVP Tom Urtis for sharing this shortcut recently on LinkedIn.

One Minute to Excel #9 – Drop down list in a cell

Three techniques

This short video covers different ways to insert a drop down list into a cell.

I go over my one minute time limit by a couple of seconds, but I do cover three techniques.