Paste Values and Formats
If you need to paste values and formats then you can use a single keyboard shortcut after you have copied. Alt H V E will paste both.
One Minute to Excel #12 – Hide Cell Entries
You can't hide a cell, but you can stop the cell value from displaying on the sheet. It involves a custom number format. https://vimeo.com/529238622
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.
Australian Financial Year Quarter Formula
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
(more…)Dashboard Webinar feedback
It is always nice to receive feedback - this was for the recording of the Dashboard #1 session.
One Minute to Excel #11 – Add % to a Pivot Table
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. https://vimeo.com/529238423
Financial Year in Power Query
If you need to extract the Australian Financial Year from a date in Power Query here is how to do it.
(more…)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.
Save and Close extra
In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you can run the macro on any file and it will only work when required.
(more…)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
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. https://vimeo.com/525865809
Save and Close in Excel
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.
(more…)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
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. https://vimeo.com/525865680