Automating bullet points in Excel
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
(more…)Highest and lowest values in an Excel filtered list
If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them. (more…)
Distinct Count in Excel
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count. (more…)
Change in Ctrl Key Selections
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video. (more…)
Creating a Timeline Chart in Excel 2016
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart. (more…)
Auto Format Formulas in Excel
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.
(more…)Power Query
Its always great to receive e-mails like this. Power Query is a game changer.
Copying Without Affecting Relative References
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas? (more…)
TreeMap a new chart in Excel 2016
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy. (more…)
Excel and Outliers
Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data. (more…)
![Read more about the article Creating a table of all combinations in Excel [Video]](https://a4accounting.com.au/wp-content/uploads/2018/01/vimeo-250571093.jpg)
Creating a table of all combinations in Excel [Video]
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table. (more…)
Towards a Simpler IF Function
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS. (more…)
Creating a Unique Dynamic List in Excel
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list. (more…)
Do You Have Excel Inquire?
Inquire is a new add-in in some versions of Excel 2013 and later versions It is an auditing Add-in that can analyse and report on your Excel files. (more…)
Export a sheet as a PDF
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.) (more…)
Relationships Shortcut
Excel 2013 added the Data Model to Excel. The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables. You have to have used the formatted table option to use Relationships. See my December 2016 INTHEBLACK article for an example. The keyboard shortcut to create or edit, a Relationship is easy to remember - it is Alt a a Pressed in…
Finding the Last Used Cell in an Excel Column
If you need a formula to identify the last used cell in a column you don’t have to use an array formula. The AGGREGATE function can calculate it for you. (more…)
PowerPivot and Power Query
I have run two introductory webinars in 2016 on these two topics. You may need to download them and install them before the webinars so you can use them during the webinars. The Add-ins are free from Microsoft. (more…)
Day Of The Week
A quick and easy way to find out the day of the week for a specific date is to use the Long Date format from the drop down in the middle of the Home ribbon (in the Number section) - see below.
Flexible Hyperlinks in Excel
Hyperlinks are a great way to navigate around large spreadsheets. Unfortunately they each take a few clicks to create and can be easily broken. You can use a function to easily create multiple, flexible hyperlinks. (more…)
Clearing the tab colour
I use colours on my sheet tabs to signify different things. To clear a colour, you can use the following keyboard shortcut Alt h o t n Pressed in sequence, not held down.