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.
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.
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.
Well, the wait is finally over in the subscription version.
You can now unhide more than one sheet at a time – woohoo!
When you paste into Excel from other applications, sometimes the formats can be problematic. Here is a tip to ignore formats.
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.
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.
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
Seems new icons have been added in the right of screen to allow you to switch easily between Task Panes in a recent upgrade.
This is in the subscription version of Excel.
The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!
To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.
So only adults allowed in Power Query.
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
The Goal Seek feature can save you a lot of trial and error when you want a calculated cell to equal a specific value.
The keyboard shortcut to open the Goal Seek dialog is Alt T G.
The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.
Did you know you can accumulate copied entries in the Windows clipboard?
The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste.
You can also pin items to the clipboard to keep them there.
To change the setting.
Click the Windows button and choose the Settings icon
The select System.
Then select Clipboard (bottom left) and change the Clipboard History to On – done.
If you copy multiple items you can press the Windows key and V to access what’s on the clipboard.
Then you can click the top right elipse icon to Pin the item to the clipboard.
If you frequently export files as pdfs then this keyboard shortcut may save you a little time.
Pressed in sequence, not held down use
Alt F E A
Slicers are a great filter interface. Sometimes, due to layout restrictions, you prefer the slicer to go across the sheet rather than down the sheet. Here’s how you do that.
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
If you want to clear all the fields from a Pivot Table you can use the clear all option.
On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.
This removes all the fields and allows you to start again from scratch.
Linking to a text box in a sheet is straightforward, unless you want to copy that linked text box to another sheet and retain the link. Here is how you do it.
To link to a text box you click the text box and then click in the Formula Bar and press = and then click the cell to link to and press Enter.
This works OK on the sheet but if you copy the text box to another sheet it links to the same cell in the other sheet. If that’s what you want, great. If it isn’t then you need to use this technique.
Click the text box click in the Formula Bar and press = then instead of clicking on the current sheet click on another sheet tab and click a cell in another sheet then return to the current sheet and then click the cell you actually want to link to and press Enter.
By doing it this way the sheet name is included in the link and that ensures the link is kept when you copy the text box to another sheet.
You could also manually type the sheet name into the Formula Bar, but using the mouse is much easier.
It is best practice to use grouping to hide and unhide rows in Excel. I recently saw a technique that also displays a message.
You can easily link a textbox to a cell. If you change the link you may have an issue with the text format used.
A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.
Below is an example of the date issue.
The formula in cell B2 is
=SUBSTITUTE(A2,".","/")*1
As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.
The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.
This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.
Note: Power Query can also automatically fix dates like these when it imports data.
The Indian Financial Year start on 1 April. Like Australia its Financial Year month numbers can be painful. Here is a formula to sort them out.
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.
Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.
This link above is a great resource for Power Query data connectors.
It is listed alphabetic order.