Pie Chart Other Segment in Excel
Pie charts often get a bad rap in data visualisation circles (sorry) but here is a technique to avoid those multiple small segments.
(more…)Capturing Formulas in Excel
Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..
(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…)Excel Depreciation for Part of the Year
When working with financial years and depreciation you often have the situation where the asset being purchased has been purchased during the year and not at the start of the year. Here's a function that calculates fixed declining balance depreciation and handles part year depreciation. The DB function has an optional fifth argument that allows you to specify how many months did appreciate in the first year. In the image below you can see the DB function being used in…
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…)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…)