Excel SEARCH and Wildcards
Excel has recently added three regex functions to handle text patterns, but did you know the old SEARCH function can use wildcards?
(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…)TRANSPOSE and TOROW Functions in Excel
When you have a vertical list that you need to make horizontal you can use the TRANSPOSE function to switch it. There is an alternative function that gives you extra options.
(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…
Better MROUND Function for Excel
The MROUND function gives inconsistent results. Here is a replacement custom function that provides consistent results.
(more…)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…)Macro to Set Sparkline Axis to Zero
When creating sparkline charts in Excel one of the problems is that the vertical axis doesn’t always start at zero. It is a manual process to reset the vertical access to zero and a macro can make it much quicker and easier.
(more…)Excel FizzBuzz Challenge
I was recently reminded of a basic programming challenge called FizzBuzz. Here is my Excel solution.
(more…)Handling Extra Columns in Excel Power Query
When you import a CSV file using Power Query any extra rows are automatically included. Unfortunately, extra columns are NOT included. Here’s how to fix that. In the image below I have the Formula Bar showing for the Source step for importing a CSV file. I have highlighted the argument that specifies how many columns it will import. This argument is optional. You can edit in the Formula Bar and delete that argument. Then when you refresh the Power Query…
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…)UNIQUEIF Excel Formula
I recently had an interesting question in a webinar. Could I create a formula like UNIQUEIF. I like a challenge so here is my answer.
(more…)Excel and ActiveX Controls
Microsoft has recently turned off ActiveX controls as default. If you have used them in the past and still want to use them you will need to turn them back on.
(more…)TRIMRANGE Syntax and INDEX and INDIRECT
The INDEX function can return a reference to a cell. However, the new TRIMRANGE syntax won’t accept it a reference. You need to use the TRIMRANGE function to use INDEX or INDIRECT. In my last post I showed how to always refer to cell A1 in a reference. The techniques demonstrated in that post won’t work with the new TRIMRANGE syntax. – see this previous blog post about the new syntax. If you try to use the new syntax, you…
Making Sure a Reference is Always A1
When you create a formula or a range that refers to cell A1 and you want it to ALWAYS refer to A1 no matter what – you have at least a couple of options.
(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…)Hiding Zeroes in Excel
Excel has a sheet setting that allows to stop zeroes from displaying in the whole sheet. There is a simple macro line of code that makes it easy to change.
(more…)Using Colours Effectively in Excel Charts
When I reviewed the charts in the CPA Australia 2024 Annual Report I found another series of charts that could be improved.
(more…)Column vs Bar Chart in Excel
As I was reading the CPA Australia 2024 Annual Report I was interested in the presentation of charts (graphs). A lot of money had been spent on the presentation of the report. There were lots of high-quality photos and other images. I was disappointed with much of the data visualisation.
(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…)Sharing a Power Query Between Files
If you have a created a power query to import data you can save it and have another file access it easily.
(more…)Updating Sentences in Excel with New Techniques
I wrote a blog post back in 2012 and showed how to use checkboxes to build a paragraph based on sentences you select. I thought I would update it with recent new techniques.
(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…
Unique Entries in a Range in Excel
Sometimes the UNIQUE function won’t return what you’re expecting when you refer to a range of cells. You can use another function to allow you to extract the unique individual values from a range.
(more…)Adding Up a Specific Column in an Excel Spill Range
If you have a 2-dimensional spill range in Excel and you need to add up a specific column here is an easy formula to use.
(more…)Excel Copy Across Macro
When you work with dynamic arrays and spill ranges you get used to the formula automatically spilling across to populate a range. When you aren’t using dynamic arrays, it can be frustrating to have to copy formulas across. Here’s a simple macro solution. In the image below I need to copy cell C3 across to the range D3:N3. This is a standard budget layout in Australia where the financial year starts in July. A one-line macro command can copy this…