Grouping Icon Above Group Rows in Excel
The default setting is to have the grouping icon below the grouped rows. But you can switch things and have the icon at the top of the grouped rows. (more…)
Grouping Measures in Power BI
It is considered best practice to use Measures for all your values in Power BI reports and dashboards. One way to make it easier to find Measures is to group them together. (more…)
Free Webinar Recording – Excel Yourself 2019
In December 2019 I reviewed four of my articles from 2019 plus some new content. The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/379919236 This year’s webinar includes a new logic function IFS how to do…
Creating Groups for Power Queries in Excel
Once you start to use Power Query you may find yourself with quite a few queries in the one file. To make it easier to control them you can use groups to keep similar queries together. (more…)
Excel Sort within a Sort
Did you know you can sort by colour in Excel? Did you know you can sort ascending or descending within that colour? I was asked a question in a recent webinar and in answering I found out that you can sort within a sort. (more…)
Applying a standard custom format in Excel
One of the most common custom number formats used in Excel is the mmm-yy format. As an example this format displays all the dates in January 2019 as Jan-19. This format is used in most reports, budgets and financial models. There is quicker way to apply it than using the Format Cells dialog. (more…)
Counting Duplicates in an Excel Range
The Duplicates option under conditional formatting is useful to identify when there are duplicate entries within a range. This requires you to review the range to see if there are any duplicates. You can use a formula to identify ranges that contain duplicates. (more…)
Free Webinar Recording – Excel Chart Tips and Tricks
In November 2019 I re-ran my Excel Chart Tip sand Tricks session. The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/374068298 This webinar is focused on showing you how to create and modify charts in Excel…
Putting Emphasis on Words in Excel
Did you know that you can format individual words and letters differently within an Excel cell or text box? (more…)
Extracting initials in Excel
If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it. (more…)
Top 5 reports with PivotTable and PivotCharts
When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution. (more…)
Activating a Sheet When an Excel File is Opened
There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
(more…)Changing the Close & Load settings in a Power Query
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer. (more…)
Conditionally Selecting a Column in Excel
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula. (more…)
Power Query feedback
Received this email after a recent Power Query session - some things may take a few goes to understand, that's OK, that's how we learn.
Free Webinar Recording – Excel Formula and Function Tips
In October 2019 I re-ran my Excel Formula and Function Tips session. The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/366912862 This session covers lots of tips, tricks and techniques to speed up the formula…
Free Webinar Recording – Introduction to Power Query
In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network. The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video. Download Materials Power Query allows you to automatically perform data cleansing routines on your data sources – no manual…
Free Webinar Recording – Excel Formatting Tips
In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/366395923 This session covers: a format to avoid and the one to use in its place keyboard and mouse…
Excel Macro to Insert a Formula
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
(more…)Sequential Numbers Formula
Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function. (more…)
Extract Email from Copied Outlook Address
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
(more…)Allocating Across Financial Years
A recent question from a CPA in Canberra allowed me to combine the MOD function and helper cells to offer a solution. (more…)
Hiding Zero Rows in Excel with No Macros
Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution. (more…)
Free Webinar Recording – Interactive Excel Chart
In September 2019 I re-ran my very first webinar that was based on an August 2012 INTHEBLACK feature article. I have provided a detailed pdf manual and example files which you can download at the link below. This session covers using controls with charts to make the user experience easier tips for using controls on spreadsheets flexible formula techniques to make reports more dynamic a technique to stop figures plotting on charts using range names with controls and reports general…
Linking sheet controls to cells
Most spreadsheet controls need to link to a cell to enable interaction with the spreadsheet. There is an easy way to do that. (more…)
Some actions clear the clipboard
Some actions in Excel will clear the clipboard after you have copied or cut. Two common ones are inserting a new sheet clearing a filter - using Clear in the Data menu (keyboard shortcut Alt a c ) So if you are going to do either of these, do them before copying or cutting.
![Read more about the article Copying between sheets in Excel [VIDEO]](https://a4accounting.com.au/wp-content/uploads/2017/06/vimeo-221396947-1.jpg)
Copying between sheets in Excel [VIDEO]
Copying between sheets or files is a common task. I’d like to share a quick and easy way to do it. (more…)
Increase Font Size in VBA Window
Yes, you can make the text in the VBA window easier to read. It is also great for training. (This tip may not work on 4K monitors.) To open the VBA Window press Alt + F11. Click the Tools menu and then Options - see image below. Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below. Click OK and the font size will now be increased in the…
Windows File Explorer – New Folder
To insert a new folder in Windows File Explorer use Ctrl + Shift + N.
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…)