Excel Drop-downs and Wildcards
I just found out that Excel’s in-cell drop-downs via Data Validation allow wildcards characters that match an entry in the list. Wildcards combinations that don’t match are rejected.
(more…)Custom Number Format Tip: Add a Line Break in a Cell
Custom Number Formats allow you to add text to numbers and still use those numbers in calculations. There is a workaround if you need to display the number and the text on separate lines within the cell.
(more…)Ensure a Single Check Box Selection
Check boxes are supposed to be used to allow multiple selections. But since they are now so easy to insert, you might want to use them to allow a single selection. Here’s one way to do that.
(more…)Flexible Arrow Format in a Cell in Excel
Sometimes you like to point to a cell from the cell on the left using something like ===>. Here is an easy way to automate the length of the arrow based on the width of the cell.
(more…)Creating a Random Number in Excel
Excel has a couple of functions that will generate a random number. But the problem is they are volatile. The number changes when Excel calculates. Here’s a technique to generate a random number that doesn’t change.
(more…)Selecting a Locked Cell in Excel
When you protect a sheet in Excel there is a setting to stop users from selecting locked cells. This means when the sheet is protected, they can’t use the keyboard or mouse to select a locked cell. But you can still select an locked cell in a protected sheet – see how.
(more…)Alphanumeric Sort in Excel
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
(more…)Unhiding Sheets is Now Easier in Excel
Not sure when this change happened but it is now much easier to unhide multiple sheets in Excel. In the past you had to use tick boxes to select sheets unhide. On click per sheet. Now you can just use the Ctrl or Shift keys to select multiple sheets to unhide. In the image below you can see that the tick boxes have been removed and just the sheet names are listed. You can hold the Ctrl key down to…
Excel Icon Right Click
Did you know you can right click the Excel icon on the task pane? When you do you can see a list of recent files. In the image below I have right clicked the Excel icon. You can then click one of the file names to open Excel and the file. If Excel is already open it still works. You can open a recent file quickly by right clicking the icon and clicking the name. This technique also works for…
Power Query Merge
Merging in Power Query is a useful feature that automates and replaces XLOOKUP functions. But please make sure the look up table doesn’t have duplicates.
(more…)Clear Data from an Excel Table
Using formatted tables in Excel is best practice. If you regularly overwrite data in a formatted table here is a quick way to clear the data before pasting in updated data.
(more…)Excel and Financial Years and Months
Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
(more…)When to Avoid Using Colours in Excel
Like me, you have no doubt seen some colourful Excel files. One task that colour is used for is to mark rows so they can be reviewed. In general, please avoid applying colours manually when marking rows for review. Instead use a code in a separate column.
(more…)Another Blank Cell Issue in Excel
Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
(more…)Listing Range Names in Excel
Excel has Paste List option to list the range names in the file. Here’s a macro that does it in one click.
(more…)Adding a Dotted Line for the Budget in Excel
When you are charting the whole year in a line chart you might want to show the actuals up to a point and then the budget from then on. The budget could also be a dotted line. Here’s a way to do that.
(more…)Shortcut to Delete a Word
This technique works best in Word, but you can also use it in Excel. There is a keyboard shortcut to delete the current word. The shortcut is Ctrl + Backspace. When creating a formula in Excel that shortcut has another use that I covered in the blog post below. Note: in Excel you may now need to use Ctrl + Shift + Backspace. Whichever works - it can be useful. https://a4accounting.com.au/keyboard-shortcut-to-return-to-the-formula/ When you are in a text box (or in…
Bullet Points in Excel
Bullet points are more a Word or PowerPoint feature, but you can use them in Excel. There are a few tricks required to use them though.
(more…)Excel Strikethrough Format Appearing
You may have started to see the strikethrough format displaying in Excel and wondered why. It means the formula’s values haven’t been updated and a calculation is required.
(more…)Checkboxes as a Visualisation in Excel
I have covered the new checkboxes in Excel in a previous post. Typically, they are used as a simple user interface to turn off or turn on an option. But I learned recently that you can also use them as a visualisation.
(more…)Resetting the Used Range
The scroll bar on the right of screen allows you to quickly scroll down to the bottom of your data. Sometimes the scroll bar thinks the data goes down to the maximum row number. It keeps track of what’s called the Used Range. The Used Range goes down to the intersection of the last used row and across to the last used column. To see where the Used Range ends press Ctrl + End. The cell selected may be empty.…
Excel Textbox Link Extra Trick
I have shared before you can link a textbox to a cell and have a customised message delivered via the textbox. But there is something else I recently learned from Wyn Hopkins (MVP) about this technique.
(more…)Excel Variance Techniques
When comparing actuals to budget or this year to last year you often calculate the variance and variance %. The variance calculation can be standardised with one simple trick and another function can make sure the variance % has the right sign.
(more…)Excel Objects and Ctrl + A
Selecting all objects on a sheet in Excel is easy with Ctrl + A. Objects are things like textboxes, images, charts and slicers. If you select one object and press Ctrl + A all the objects on the sheet will be selected. When you select the object make sure the line around it is not dotted. Some objects like text boxes have a dotted line when you are editing the object. Ctrl key After you have selected all the objects…
Select a Spill Range
The Ctrl + A shortcut has been around for a long time and over the years it's functionality has expanded. The latest addition to it's abilities is selecting a spill range. Select any cell in a spill range and press Ctr + A and it will only select the current spill range. This can be useful for formatting as the format doesn't spill.
Pasting from Word to Excel
I was recently updating some Word-based templates, and I remembered how clunky it is to paste from Word to Excel. Here is a short tip to make it easier.
(more…)TRIMRANGE Referencing in Excel
A recent update has introduced a new way to refer to ranges in Excel. This update makes it much easier to refer to flexible ranges in Excel.
(more…)