There are only two characters Excel recognises when separating numeric days, months and years in dates. They are the / and – characters.
Please don’t use the full stop as Excel won’t recognise it as a date.
Below you can see examples of using / and – in dates.
When you use the full stop Excel won’t recognise it as a date – see below.
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.
Learn how to build better spreadsheet interfaces using form controls like check boxes, scroll bars, option buttons and combo boxes.
Form controls can reduce the number of input errors; simplify and speed up input plus add structure to a file.
Discover the function that you need to use to get the most out of these controls.
Find out a few tips and tricks to make creating and using form controls easier.
As always there will are a few more tips and tricks shared in the session.
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.
Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.
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
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.
NOTE: This is not a beginner’s session.
This webinar is based on a budget scenario which you need to read before the session starts. It is only 3 pages long and included in the materials.
Topics covered include
As always there will are a few more tips and tricks shared in the session.
The rain falls on all the fields, but crops grow only in those that have been tilled and sown.
Chinese saying
The Data Validation dialog allows you to limit or control what a user enters into a cell. There are two keyboard shortcuts to open it.
I have spent my days stringing and unstringing my instrument, while the songs I came to sing remain unsung.
Rabindranath Tagore (1864-1941)
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Do you use Indenting in reports? Example below.
If you do, you make like this keyboard shortcut.
To increase Indenting press Alt H 6 pressed in sequence, not held down.
To decrease Indenting press Alt H 5 (is that an Outdent?)
Plan for the future because that’s where you are going to spend the rest of your life.
Mark Twain (1835-1910)
Sometimes when writing code you use the same text string in multiple places in your code. To make things easier you should capture that text string in a variable.