I typically turn gridlines off on my sheets and then use borders for the lines. I have added icons to my Quick Access Toolbar to speed up the process.
Monthly Archives: March 2020
Reading Books Online – legally
CPA Australia option
Seeing we may be stuck at home for a while here is a suggestion regarding reading books online. If you are a member of CPA Australia you can read certain books online for free.
New Dynamic Array Function SEQUENCE
Part One - its all about spilling
Dynamic arrays have the potential to change the way Excel spreadsheets are created. They were released in the January 2020 wave of updates to the Office 365 subscription version.
Issue with extracting a sheet name in Excel
Single sheet file issue
I wrote about a formula to extract the sheet name many years ago, but I just found an issue with it. Click here to see older blog post.
Free Webinar Recording – Excel Format as Table Feature
Feedback score 95% based on 58 responses
In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.
The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.
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.
In this session you will learn all about Excel’s formatted tables.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
- advantages and limitations of formatted tables
- keyboard shortcuts
- using formatted tables with formulas
- solutions to some of the limitations of formatted tables
- using range names with formatted tables
- using formatted tables with data validations
- creating a running total
- using PivotTables
- Relationships (Data tab)
As always I will share a few other tips.
Negative Time in Excel
Party like its 1904
It is possible, but it involves a setting change that comes with a warning.
Weeks and days calculation in Excel
In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.