Blogs

The best way to get data into Excel

By Neale Blackwood, CPA posted 17-09-2020
19 people like this.
Power Query is a relatively new feature in Excel, but not that new. Unfortunately so many people seem to be totally unaware of its existence. In terms of importing data into Excel it is easily the most important feature in the last 20 years. Power Query allows you import data from virtually any source and then transform the data and fix issues and add and remove columns to get the data just right. Then once you have created the query to get the latest data in the transformed structure it is just a refresh. It remembers all the steps you took to fix the data. Its a bit like a recorded macro but even better. Getting your data right is the biggest hurdle in ...
6 comments

CPA VIC Young Professional Network Discussion Group Is ON!

By Judy Gao, CPA posted 26-09-2020
4 people like this.
Dear Fellow CPAs, After you read below, if interested, please get in touch with Judy Gao via cpayoungprofessionalnetwork@gmail.com join our upcoming ZOOM event (Mon 19 Oct 1pm to 2pm): Managing Your Career with Matt Gaffney Registration Link 2020 is a truly once-in-a-generation challenging year but with great opportunities. CPA Australia has shown great leadership and resilience during COVID19. I noticed lots of events and podcasts with trending topics have been organised and promoted promptly to support us. Not mention this CPA Member Connect platform for true engagement. I cannot express how proud and appreciated I am to be a CPA ...
1 comment

Extract data based on the sheet name in Excel

By Neale Blackwood, CPA posted 12-12-2022
3 people like this.
Here's a technique I use a lot to speed up report development. Sheet names have to be unique, so they can't be duplicated. This makes them great for dept names or states. This short video combines a few techniques to extract from a data set based on the sheet name. All in less than a minute.
5 comments

Managing employee annual leave balances

By Joseph Pannuzzo, FCPA posted 13-11-2020
5 people like this.
Annual leave is an entitlement for employees which is to be taken regularly to ensure that they have appropriate downtime and can relax. Not taking accrued annual leave can be hazardous to the health and safety of employees. Excessive accrued annual leave balances can represent a significant financial liability for employers. Here are some general options for managing employees with excessive annual leave balances: Shutdown Depending on business requirements, employers may shut down during quiet periods, for example over Christmas/New Year, and require their staff to take annual leave during this time. The Fair Work Act says that if a requirement ...
4 comments

Casual Employees: mitigating the risk of a retrospective claim

By Joseph Pannuzzo, FCPA posted 14-10-2020
6 people like this.
Many employers have a number of employees who fulfil different roles within their organisation, from full time permanent to part time and casual employees. Often, employees may commence with an organisation as casual, with unpredictable, seasonal or fluctuating hours depending on the needs of the business and the employee. However, as time goes on, some casual employees do in fact have regular hours of work, despite their casual employment. In a recent decision (WorkPac v Rossato) the Federal Court of Australia confirmed that labelling an employment contract as “casual” doesn’t automatically make them a casual employee. The key take away from the decision ...
2 comments

Column vs Bar Chart in Excel

By Neale Blackwood, CPA posted 30-06-2025
5 people like this.
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. As a simple example, below is a recreation (as close I could get) of the Total Revenue chart from the bottom of page 118. You can check out the annual report at the link below. https://www.cpaaustralia.com.au/integrated-report/downloads I did the CPA Australia Data Interpretation and Visualisation micro course a couple of years back and one thing that was stressed ...
1 comment

CPA Australia Excel Tips podcast

By Neale Blackwood, CPA posted 22-01-2024
2 people like this.
If you use Excel you might like the CPA Australia Excel Tips podcast . https://www.cpaaustralia.com.au/tools-and-resources/podcasts/excel-tips This is the companion podcast to my Excel Tips articles in the INTHEBLACK magazine. I share lots of tips, tricks and techniques that can make using Excel a much better experience. I cover a lot of the new features and updates. Please share this free resource with colleagues. #Excel
0 comments

Judy Gao will interview former Foreign Minister, Commissioner to Greater China and the Group CEO of Asialink at 29 Oct

By Judy Gao, CPA posted 17-10-2020
3 people like this.
Dear Fellow CPAs, This is Judy Gao, a proud CPA member, I have an exciting upcoming event to share. If you are interested in Australia-China relations, your businesses or yourselves are suffering from the diplomatic tensions between Australia and China, please register and join below event: At 6pm, 29 Oct, I will moderate a panel discussion on the Australia-China relations and impact on young professionals from geopolitical, economic and cultural perspectives with three distinguished panellists including former #ForeignMinister Gareth Evans, #Commissioner to Greater China Brett Stevens and #GroupCEO of Asialink Penny Burtt. RSVP: Eventbrite ...
0 comments

PivotTable Answers Part 1 – Refreshing and Financial Years

By Neale Blackwood, CPA posted 25-08-2025
1 person likes this.
During the session there were a few similar questions. I have answered them below. Session recording The webinar recording link is: https://cpaaustralia.webex.com/cpaaustralia/ldr.php?RCID=156b302e0585fc4d8129c91d7c6e2d4f and password is: ExcelWebinarSeries2025 . Questions: When "auto-fresh" will come out? Looking for a timeframe for "auto-fresh". For the auto refresh feature, do i need to have the latest Excel version? Plus. Do changes to the underlying data automatically update the pivot table? In the session I mentioned that there will soon be an AutoRefresh option for ...
0 comments

[Virtual Event-Free CPD Hr]CPA YPN- Inspiring Stories Of Most Influential Asian- Australians Award Winners

By Judy Gao, CPA posted 13-11-2020
1 person likes this.
Dear All, Registration link (1 FREE CPD HR): 6pm - 7pm AEDT, Thurs 19 Nov. CPA Young Professional Network VIC DG is excited to inviting you to join our upcoming event - SO PLEASE REGISTER AND JOIN US to meet and hear unique inspiring stories of 2019 & 2020 AWARD WINNERS of UNDER 40: MOST INFLUENTIAL ASIAN-AUSTRALIANS to meet and hear insights from JUDGES AND ORGANISER OF UNDER 40: MOST INFLUENTIAL ASIAN-AUSTRALIANS JOIN US : ) YOU CAN BE THE NEXT AWARD WINNER! 2020 Award Winners of the 40 UNDER 40 were just announced on 29 Oct 2020. It's our great privilege to orgnaise this event in such short notice for you. ...
0 comments

PivotTable Answers Part 2 – Report Layouts

By Neale Blackwood, CPA posted 26-08-2025
2 people like this.
During the session there where a few layout-related questions. Question: Is there a way to always show the classic pivot presentation by default instead of having to change it via pivot table options/display/ each time a pivot table is created? Yes, in the File>Options>Data section - click the Edit Default Layout button. Click the PivotTable Options button and open the Display tab and click the Classic PivotTable option – click OK and OK and OK again. Next Pivot you open will use the Classic Pivot layout. Question: Hi Neale, i do normally put the commentary (let say in the colomn J) for example. however, is there any way ...
0 comments

GDP Vs the Most Innovative Companies Vs the Richest People

By Herry Santoso, CPA posted 18-03-2024
1 person likes this.
It's interesting to study as to whether GDP is an indicator for the Country's innovation and the richest people or not. GDP 2023 and 2022 is as follows ( https://lnkd.in/gFcQt9_k )): 1. USA (US$26.9 T in 2023 and US$25.4 T in 2022) 2. China (US$17.7 T in 2023 and US$17.9 T in 2022) 3. Germany (US$4.4 T in 2023 and US$4.0 T in 2022) 4. Japan (US$4.2 T in 2023 and US$4.2 T in 2022) 5. India (US$3.7 T in 2023 and US$3.3 T in 2022) 6. UK (US$3.3 T in 2023 and US$3.0 T in 2022) The most innovative companies in 2023 according to BCG (https://www.bcg.com/publications/most-innovative-c) 1. Apple (USA) 2. Tesla (USA) 3. ...
0 comments

Don’t let your Christmas party become a workplace claim

By Joseph Pannuzzo, FCPA posted 15-12-2020
Be the first person to like this.
Work Christmas parties provide a well-earned opportunity to have fun with your staff and celebrate your achievements. But employers beware: Christmas parties are a WHS minefield. Employers are responsible for minimising risks at work functions by: Checking the venue for safety hazards and COVIDSafe compliance Limiting the amount of alcohol being served and ensuring your staff are not intoxicated Providing suitable transport to ensure everyone gets home safely Ensuring ahead of time that your staff know the workplace guidelines and expectations around behaviour and alcohol consumption Reviewing and reminding staff of any current ...
1 comment

Power BI - Dashboards on your mobile

By Sharad Maheshwari, CPA posted 10-01-2021
Be the first person to like this.
Using Power BI , you can get your dashboards on Power Bi app at a very reasonable price . This is a great value for money Attached is the link for Power BI Dashboards on your mobile Looking to work with members on Power BI
0 comments

What a company!

By Herry Santoso, CPA posted 10-12-2023
Be the first person to like this.
What a company! When a nation possesses a globally recognized product brand, it results in economic prosperity for its citizens. One of them is Samsung. Samsung phones are utilized by individuals of all ages worldwide, from teenagers to the elderly. Amazing! No wonder, if Samsung can sustain the economy of South Korea. ( https://lnkd.in/gf_2CnpX ) Samsung's contribution to GDP is around 20%. What a company! 2017 GDP: US$1.62T (Samsung’s Contribution: 20.50%) 2018 GDP: US$1.72T (Samsung’s Contribution: 20.35%) 2019 GDP: US$1.65T (Samsung’s Contribution: 19.75%) 2020 GDP: US$1.64T (Samsung’s Contribution: 20.33%) 2021 GDP: US$1.81T (Samsung’s ...
1 comment

Q&A 10 Essential Excel Functions Webinar

By Neale Blackwood, CPA posted 19-09-2025
Be the first person to like this.
Here is the recording link. Recording link: https://cpaaustralia.webex.com/cpaaustralia/ldr.php?RCID=c966b8bfe97ecc0528138e2636457c65 Recording Password: ExcelWebinarSeries2025 I couldn't cover all the questions in the session - they are listed below. Q: How can you find the number behind a date in Excel? A: In the session I showed that a date has an underlying number. To see that number you must remove the format from the cell. To do that select the cell and press in sequence Alt H E F which removes the format. Q: Does XLOOKUP still use a lot of processing power making it inefficient over larger number of rows? A: Not ...
0 comments

Can BRICS Currency topple US$?

By Herry Santoso, CPA posted 14-02-2024
Be the first person to like this.
It’s hard to beat US$ because it has maintained dominance for many years across all four markets. There are four major markets where the U.S. dollar has consistently dominated in trading, and other currencies are not widely utilized in these significant markets 1. Equity Market 2. Debt Market 3. Commodity Market 4. Money Market 1). Equity Market, US$ has been dominating for years and market cap of 2023: NYSE at US$27.69T & Nasdaq at US$24.56T. Monthly trade volume US$1.4 T and US$1.2 T for NYSE and Nasdaq, respectively. Shanghai stood at US$8.5T and monthly trading volume stood at US$0.5T ( https://lnkd.in/eBYqP-UM ) 2). Debt Market, ...
1 comment

𝗖𝗼𝘀𝘁 𝗮𝗻𝗱 𝗕𝗲𝗻𝗲𝗳𝗶𝘁 𝗳𝗼𝗿 𝗜𝗻𝘁𝗲𝗿𝗻𝗮𝘁𝗶𝗼𝗻𝗮𝗹 𝗢𝗿𝗴𝗮𝗻𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝗠𝗲𝗺𝗯𝗲𝗿𝘀𝗵𝗶𝗽

By Herry Santoso, CPA posted 27-01-2025
Be the first person to like this.
𝗖𝗼𝘀𝘁 𝗮𝗻𝗱 𝗕𝗲𝗻𝗲𝗳𝗶𝘁 𝗳𝗼𝗿 𝗜𝗻𝘁𝗲𝗿𝗻𝗮𝘁𝗶𝗼𝗻𝗮𝗹 𝗢𝗿𝗴𝗮𝗻𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝗠𝗲𝗺𝗯𝗲𝗿𝘀𝗵𝗶𝗽 President Trump decided to withdraw from the WHO, and my first thought was to question why he made that decision. According to this article, wealthy countries contribute relatively little to the WHO. The ranking of contributions appears inconsistent with their GDP rankings: 1) The U.S. paid US$1 billion, ranking 1st (GDP: US$27.3T, also ranked 1st). 2) Germany paid US$722.6 million, ranking 3rd (GDP: US$4.4T, ranked 3rd). 3) The UK paid US$133.8 million, ranking 9th (GDP: US$3.3T, ranked 6th). 4) France paid US$115.8 million, ranking 12th (GDP: US$3.0T, ranked 7th). ...
0 comments

PivotTable Answers Part 3 – General Part 1

By Neale Blackwood, CPA posted 27-08-2025
Be the first person to like this.
During the session there where quite a few general questions. This is Part 1. Question: can we add data range into the pivot? Yes. If you use a formatted table as your data source when you add data to the formatted table the data will appear in the Pivot when you refresh. I demonstrated this in the session. If you don’t use a formatted table you need to manually extend the data source range. Question: You would probably run out of resources with excel and pivots and need to use power BI. What do you think the limits are? How many rows and columns to your data? The session data set only had nearly 10,000 rows. You are not limited ...
0 comments

How to create a Time Line Chart in Excel

By Neale Blackwood, CPA posted 10-12-2024
Be the first person to like this.
My latest article covers how to create a Timeline chart in Excel - link below. Time Line Chart in Excel If you aren't sure what a timeline charts looks like there is an example in the image below. The link above has a companion video, podcast plus the example file.
0 comments