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.
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 run the macro on any file and it will only work when required.
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.
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.
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.
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.
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.
In a financial model you often have different types of allocations that start at different times. Creating a short formula to handle this flexibility can be a challenge. Here is one solution.
Learn about Excel’s powerful Table feature that treats tables like little databases.
I ran this free live webinar in March 2021.
You can access the pdf manual and example file by clicking the button below the video.
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
Well, the wait is finally over in the subscription version.
You can now unhide more than one sheet at a time – woohoo!
In my previous post I mentioned you should, as far as possible, keep data together in a single table rather than splitting it up between sheets. If you want to split it up for distribution purposes here is an easy way to do it.
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
I have received a few questions recently relating to working with data spread across multiple sheets. In general, if the data is in the same layout, keep it in one table.
In this short video I cover how to insert automated subtotals.
Its a built-in feature, that is easy to use.
When you paste into Excel from other applications, sometimes the formats can be problematic. Here is a tip to ignore formats.
In previous videos I worked with duplicated entries.
In this video I demonstrate how to extract unique entries from a list.
Three different ways in 60 seconds.
Off we go!
Yes, you can create a cell drop down without Data Validation. It uses a built-in technique and is flexible.
Copying is a common task in Excel. This technique applies to most things in Excel form cells and range to charts, images and sheets.
It also works in Word and PowerPoint.
Have you used the mouse and keyboard together? It is time to start.
Let’s go.
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
Imported data often has missing entries you need to populate.
You can use Power Query, but that duplicates the table.
This technique works on the existing table and is quick and easy to apply once mastered.
Start the clock!
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
In the previous video I removed duplicates, in this video we identify duplicates using Conditional Formatting.
I identify the duplicates twice in a minute in this video.
The clock is ticking.
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
Aikido instructor George Leonard on mastery:
”How long will it take me to master Aikido?” a prospective student asks.
“How long do you expect to live?” is the only respectable response.
From James Clear newsletter.
(I think you can replace (Ctrl + H) Aikido with Excel.)
I have started a new series of short videos showcasing Excel’s features that work in less than a minute.
There is a countdown to see if I can do it.
In this first one I remove duplicates in less than a minute.
The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.
Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.
When you protect a sheet in Excel many icons are turned off (greyed out), including the ever popular AutoSum icon. That’s when it pays to know keyboard shortcuts.
This link above is a great resource for Power Query data connectors.
It is listed alphabetic order.