The comma format for numbers is pretty popular.
There are a couple of keyboard shortcuts to apply it.
Hold the Ctrl and Shift keys down and press 1 on the keyboard.
The next one uses the Alt key. Use the left Alt key. Keys pressed in sequence (don’t hold them down).
Alt H K
Remember Hong Kong.
The Alt key offers a way to use icons without using the mouse. In some cases, these Alt key shortcuts can be quicker than using the mouse.
The standard budget layout isn’t great for pivot tables. You can easily and quickly convert it in to data using Power Query
See now in this short video.
When you are developing a file for a report, budget or forecast you may need to keep track of certain cells. They could be validations or profits or some other important value. The Watch Window can help you monitor multiple cells in one place.
In the Formulas tab in the Formula Auditing section is the Watch Window icon.
Clicking the icon opens the Watch Window. It will be blank.
You can click the Add Watch button to add a cell to monitor. You can chose cells from any sheet.
Click Add to watch the cell. You can widen the Watch Window and change column widths as well.
You can click the column headings to sort by the column.
You can also select a range to watch. but it will list the range as a series of cells – see images below.
When you save and close the file the watch entries are saved. When you open the file the Watch Window won’t be open, you will have to re-open it.
The shortcut Alt M W (pressed in sequence, not held down) will open the Watch Window. The same shortcut also closes the Watch Window.
If you want to display a blank cell instead of a zero there are two ways to do it.
See both in this short video.
Do you have a list or lists that you use all the time? Would you like to write the first entry and then drag it like January to get the rest of the list? Here’s how.
20 years ago my last article for the Accountants Weekly magazine was published. They spelled my name wrong after getting it right for all the other articles, maybe that’s why I stopped.
If you have data that has blanks in it you may be able to combine columns using Paste Special – Skip Blanks.
I used this today in a live webinar.
I zoomed into the Power Query window in Excel to make it easier to see.
Ctrl + Shift + + (plus)
20 years ago I had an article published on Excel shortcuts keys – let’s revisit it.
When you are working with text numbers in tables sometimes you need to convert real numbers into text numbers to do look ups.
There are at least two ways to do this.
Let’s see how to convert real numbers into test numbers, real fast.
In Australia our financial year starts in July. Excel is set up to work with calendar years and we need to do some date gymnastics to have our reports start in July. Here is a hack for Custom Lists that can make some things better in Excel.
When you import numbers from other systems they sometimes come in as text and are left aligned.
There are a couple of ways to fix them and they can both be done in less than a minute.
You need the Developer ribbon tab visible to record a macro, or do you?
This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down.
Alt T M R
You can also use the little icon next to the Ready at the bottom left corner of the screen.
Once you start recording the small square icon to stop recording appears in the same spot.
My first article was published on 10 August 2001 in the Accountants Weekly magazine.
I have scanned the original article and it is shown below.
20 years later I thought I would update the 10 points from the article.
When published all those years ago these tips applied to both Excel 97 and Excel 2000. We have come a long way since those days.
The Merge cells format has lots of issues. It can crash macros and stop you copying and pasting.
In less than a minute you can use a macro to solve the problem.
Did you know the right-hand side drop-down, above the code window, lists all the Subs and Functions in a module? Now you do.
Replacing colours manually can be a tedious task.
Did you know you can use Excel’s built-in Find & Replace to do the job for you?
Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.
I have no idea how long that feature has been there, but I have just found it. Woohoo!
When you have a filter in place in Excel you typically only affect the visible cells when you edit multiple cells. There is a case when you are affecting all cells not just the visible ones.
A check box is an easy interface to create and use.
See how to add one to a sheet and use it in a calculation.
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.
You can’t hide a cell, but you can stop the cell value from displaying on the sheet.
It involves a custom number format.
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.
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.
If you need to extract the Australian Financial Year from a date in Power Query here is how to do it.
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.
This link above is a great resource for Power Query data connectors.
It is listed alphabetic order.