In April 2019 I demonstrated many of Excel’s Custom Number Formats.
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 brand new webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.
They can
display negatives in red and with brackets
format mobile phone numbers correctly
display numbers and text together and still perform calculations
hide zeroes
display rounded numbers to thousands and millions
display the day of the week
create customised dates
be saved and be available in new sheets and files using a Template
be saved as a Style
See examples and demonstrations of many different custom number formats and learn how to create your own.
Let’s say you want the active cell to be A1 in all the sheets in a file before you save the file. There is an easy and quick way to do it. It doesn’t matter how many sheets are involved, but it does come with a warning.
In March 2019 I demonstrated how to use some of Excel’s the Form Controls.
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.
It is best practice in Excel to have a consistent colour for input cells so that users know where they can and need to make changes. You can automate this process by using a Conditional Format.
It’s not mentioned a lot, but some of Excel’s subtraction calculations are not exact. When you look at them at 15 decimal points they are slightly out. Most times this make no measurable difference to the result. But sometimes when you round, it does.
When you need to paste new rows to the bottom of a formatted table, or insert blank rows at the bottom, one way is much quicker than others when working with large, complex models.
The column Autofit on the whole sheet is a great Excel feature. But if you have a few columns that have lots of text it can make using it problematic as you need to manually adjust those wide columns. Here’s a macro to make it easier.
I recently saw a good post on Linkedin from Leila Gharani MVP on increasing the Excel Formula Bar font size. It seems useful for training, so I wrote a simple macro to simplify the process.
I recently had to re-install Office and I found an annoying Microsoft Word default setting. The setting causes a sentence to go to the next page for no valid reason. Here is the setting to change.
Excel’s Conditional Formatting feature has a built-in unique option. Its unique option only identifies entries that are not repeated. This is different to the Advanced Filter Unique option which lists each unique item from a range once. To filter by entries only appearing once you can use Conditional Formatting with filtering. No formulas required.
In February 2019 I demonstrated how to use the Format as Table feature in Excel, including some advanced techniques.
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.
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 there will are a few more tips and tricks shared in the session.
Grouping of dates in Excel’s PivotTables is fairly common and in the most recent versions of Excel, automatic. Many people don’t realise that you can perform other types of grouping in Excel.
Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
I ran a webinar in January 2019 where I presented and explained a budget challenge file I had submitted in November 2018. I mentioned during the session that I didn’t like the layout of the Data tab. Well someone asked how should it look? So here is how I would have arranged it.
In January 2019 I presented a webinar that examined a solution to a 4 dimension budget challenge. Download the materials using the button below and watch 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.
