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.
Category Archives: Tips & Tricks
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.
Free Webinar Recording – Excel Budget Challenge Solution
Feedback score 92% based on 83 responses
In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.
The detailed pdf manual and example file can be downloaded using the button below. Content 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.
This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.
The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.
Techniques/topics covered include
- using INDEX-MATCH (better alternative to VLOOKUP)
- 3-D formulas to summarise all report sheets and techniques to make using them easy
- using a reporting template to speed up creation
- validations
- extracting sheet names
- automating reports
Free Webinar Recording – Date and Time Calculations
Feedback score 95% based on 61 responses
In January 2020 I looked at solving some of the frustrations date and time calculations can cause in Excel.
The detailed pdf manual and example file can be downloaded using the button below. Content 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.
Discover the functions that handle different types of calculations.
See how to avoid some of the frustrations that dates and times can cause. Topics covered include:
- adding months to a date
- formulas for the first and last day of the month
- using today’s date in a formula
- handling days of the week calculations
- automating dates in headings
- how to avoid the traps in time calculations
- remove times from dates
- generating random dates (for testing)
As always there will be a few other tips and tricks shared during the session.
Validating Data Validation in Excel
Part One
The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.
Hyperlinking to a Cell in Another Excel File
It is possible
You can create a hyperlink to another Excel file in Excel but you can’t control what sheet or cell will be active. Well you can actually, but you need to know how.
Grouping Icon Above Group Rows in Excel
Finding a well hidden setting
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.
Free Webinar Recording – Excel Yourself 2019
Feedback score 93% based on 41 responses
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.
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.
This year’s webinar includes
- a new logic function IFS
- how to do budget allocations across months using the MOD function
- how to do complex monthly budget allocations
- using helper cells to achieve advanced conditional formats
As always there will be other tips and tricks shared along the way.
Excel Sort within a Sort
Colour by numbers
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.
Applying a standard custom format in Excel
Styles to the rescue
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.
Free Webinar Recording – Excel Chart Tips and Tricks
Feedback score 94.5% based on 59 responses
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.
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.
This webinar is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.
The Chart interface changed in Excel 2013 and I take you through some of the changes.
You will see best practice design and formatting techniques demonstrated and explained.
See how to create dynamic charts that automatically change based on selections made or data added.
Learn about the feature added in Excel 2010 called Sparkline charts.
I also include some useful date-based functions.
Putting Emphasis on Words in Excel
Don't overdo the colours
Did you know that you can format individual words and letters differently within an Excel cell or text box?
Changing the Close & Load settings in a Power Query
Its a right click option
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer.
Free Webinar Recording – Excel Formula and Function Tips
Feedback score 95.8% based on 66 responses
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.
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.
This session covers lots of tips, tricks and techniques to speed up the formula creation process. You will learn:
- how to quickly start a formula
- the benefits of the numeric keypad
- to use AutoComplete to save typing
- how to easily insert $ signs to fix references
- about the calculation sequence
- how to use the colours Excel displays when editing formulas
- the tricks on selecting large ranges
- using formulas and Format as Table together
- a helpful technique when working with ranges in other sheets
Free Webinar Recording – Excel Formatting Tips
Feedback score 94% based on 70 responses
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.
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.
This session covers:
- a format to avoid and the one to use in its place
- keyboard and mouse shortcuts
- how to use and create customer number formats
- about Styles and how they can make your formatting more consistent
- that colours can be used to filter, sort and even find things in your sheets
- how to stop zeroes displaying plus other general formatting tips
- a quick demo of Flash Fill
Sequential Numbers Formula
A ROWS by any other name
Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function.
Allocating Across Financial Years
Make the most of MOD
A recent question from a CPA in Canberra allowed me to combine the MOD function and helper cells to offer a solution.
Linking sheet controls to cells
Easy and quick way
Most spreadsheet controls need to link to a cell to enable interaction with the spreadsheet. There is an easy way to do that.
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.