These two Apps don’t always play nicely together. MYOB has a bad habit of turning off calculation in its Excel files. Here’s how to fix it quickly.
Category Archives: Tips & Tricks
Data Entry Formats in Excel [Video]
Format as you go
When you enter data into Excel you can format as you type. See how in this short video.
Related Posts
The Magical N Function in Excel
Who you calling short?
One reason I like the N function is because it is Excel’s shortest function name. But it has quite a few useful features as well.
Capture an AutoCalculate Value in Excel
A new feature
A recent update to Excel has made a slight change to the AutoCalculate feature. You can copy a value by simply clicking on it.
One Minute to Excel #24 – 1,000 random dates
A RANDARRAY solution
Let’s say we need to do some testing and we need 1,000 random dates in 2022.
We can use a new function to make this easy to create and easy to change.
RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.
I set myself a challenge to do this in less than minute – see how I went in the video below.
Hyperlink to a Specific Cell in Excel
Range name to the rescue
When you create a hyperlink manually in Excel it captures the cell reference. If you insert rows the cell reference in the hyperlink doesn’t update. If you need it to update, this is what you need to do.
Clear the Filter in One Column Only in Excel
Keyboard shortcut
When you have multiple filters across columns you may want to clear just the filter in one column. There is a keyboard technique to do that.
Selecting the Cell in the First Row in a Column in Excel
Two shortcuts
There is a keyboard shortcut to select the first column in a row, but as far as I know there is no shortcut for the first row in a column.
Excel Chart Label Line Break
When adding labels to a chart sometimes you need a line break. Well it is possible.
Display a New Window
If you have two or more screens (I have three) you can have separate Excel windows on separate screens. This can make copying and linking much easier.
The keyboard shortcut to open a new window in the current file is Alt W N pressed in sequence not held down.
This allows you to have the same file visible in two separate windows. Each window can have a separate sheet.
The New Window icon is on the View ribbon tab.
Excel and Emojis
Have some fun with emojis
Yes, you can use emojis (those little images you see in text messages, tweets and posts) in Excel. You can even use them in formulas. 😀
Input Data Display Hack for Excel
Getting the format white
When creating data input sheets, it is a good idea to use a table layout. Sometimes they can end up looking a little bit busy, especially if you are repeating entries down rows. To help users focus on what they need to do, you can use a little formatting hack to make the layout look a little less cluttered.
One Minute to Excel #23 – Text numbers to real number again
Another solution
One thing you learn quickly about Excel is that there are many ways to achieve the same outcome.
This is another example. In an earlier video I showed two separate ways to convert text numbers into real numbers.
Well, I have just learned another way. An Excel MVP Rick Rothstein shared a third way. I tweaked it and share a keyboard shortcut to do it as well.
Hope you enjoy it.
Added Nov 27, 2021
If you use Text to Columns for other conversion in the same session, you may need to use Alt A E W F as the Delimiter defaults may interfere with the conversion.
Display an Excel Pop-up Message Without a Macro
Data Validation to the rescue
Would you like to display a pop-up message when a user enters a value into a cell? You don’t need a macro to achieve this.
Another Excel Custom List Tweak
Adding Total to the end
I posted recently about how you can amend a custom list to change the sequence of a slicer – read it here. Here is another tweak I learned from Mr Excel (Bill Jelen).
Comma Format
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.
Related Posts
Excel Alt key Shortcuts
Short video at bottom of post
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.
One Minute to Excel #22 – Normalise a budget
Power Query can be quick
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.
Monitor Cells with Excel’s Watch Window
Don't wait, watch
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.
Related Posts
One Minute to Excel #21 – Hiding Zeroes
Two ways
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.
One Minute to Excel #20 – Custom Lists
Built-in feature
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.
Back when text was just text
Text functions revisited
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.
One Minute to Excel #19 – Skip Blanks
Paste Special Technique
If you have data that has blanks in it you may be able to combine columns using Paste Special – Skip Blanks.
Power Query Zoom
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)
Excel Shortcut keys revisited 20 years on
Some things don't change much
20 years ago I had an article published on Excel shortcuts keys – let’s revisit it.
One Minute to Excel #18 – Convert real numbers to text numbers
Two techniques
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.
Excel Custom List Override
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.
One Minute to Excel #17 – Convert text numbers to real numbers
Two techniques
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.
Keyboard Shortcut to Record a Macro
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.
Excelling at Excel – Twenty Years Ago Today
My first article
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.
- The right mouse button is still a time saver and now has even more options. You can now Filter and Sort using the right click. Try right clicking the navigation icons on the left of the sheet tabs for a complete list of sheets. I drag with the right mouse button to copy formats only, or values only.
- Yes, I still use the Ctrl key to copy quickly. Try dragging a sheet tab with the mouse whilst holding the Ctrl key down – it copies the sheet – woohoo!
- Selecting multiple ranges with the Ctrl key and mouse is even better now. In the more recent Excel versions you can de-select ranges. In the old days you had to start from scratch if you wanted to change a selection.
- Toolbars are gone but the Quick Access Toolbar is customisable – see this post on that.
- Toolbars are out, but you can create your own ribbon tabs using Customize The Ribbon – right click the ribbon.
- Conditional Formatting (Home ribbon) now has lots more options and many built-in ones.
- Shift key – still lots of options with Shift.
- Double clicking – yes lots of time savers. Try double clicking the Format Painter (left side of Home ribbon) to apply formats multiple times. Press Esc to stop.
- Comments – now Comments have been re-badged as Notes and Comments are a new threaded commentary system.
- Paste Special – Ctrl + Alt + V opens Paste Special after copying.
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.