Custom Number Formats allow you to add text to numbers and still use those numbers in calculations. There is a workaround if you need to display the number and the text on separate lines within the cell.
(more…)
Sometimes you like to point to a cell from the cell on the left using something like ===>. Here is an easy way to automate the length of the arrow based on the width of the cell.
(more…)
Like me, you have no doubt seen some colourful Excel files. One task that colour is used for is to mark rows so they can be reviewed. In general, please avoid applying colours manually when marking rows for review. Instead use a code in a separate column.
(more…)
Thanks to Rick Rothstein MVP for sharing a LinkedIn article he wrote a while back about colour constants. He gave me permission to share the contents in this post. Making your own colour constants.
(more…)
I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
(more…)
Sometimes with Excel formatting you just want to clear everything and start again from scratch. You can clear just the formats, and there is an icon you can add to the Quick Access Toolbar to make clearing all the formats earlier.
(more…)
Applying a date format before you drag with the Fill Handle may save you some time. See why.
(more…)
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
(more…)
It is common to have a lot of input cells in an Excel file. It is best practice to use the same format for all the input cells. This makes it easier to identify input cells for you and the user. Here is a technique using Conditional Formats that may be useful.
(more…)In my previous blog post I showed a technique to reduce clutter. The technique used a manual formatting method. Here is the automated version. You can see my previous post here. Below is the original table. We can use a Conditional Format to only display the first entry of each date in the Date column. Select the range A2:A11. Click the Conditional Formatting drop down and select New Rule (third from the bottom). Select the last option in the top…
When creating data input sheets, it is a good idea to use a table layout. Sometimes they can end up looking a little bit busy, especially if you are repeating entries down rows. To help users focus on what they need to do, you can use a little formatting hack to make the layout look a little less cluttered.
(more…)
There are a few lock downs in place around Australia at the moment, so let’s look at putting Excel into lock down.
(more…)
When creating an input range you may need to validate input cells. That may mean ensuring all input cells have an entry. Here’s how.
(more…)You can't hide a cell, but you can stop the cell value from displaying on the sheet. It involves a custom number format. https://vimeo.com/529238622
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week. But we still need to use the number normally. Here’s one way to do that.
(more…)
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
(more…)
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
(more…)
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
(more…)
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. (more…)
In my previous post I showed how to have a flexible data validation and how to validate it. This post will look at adding conditional formats to inputs and validations. (more…)
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. (more…)
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. (more…)
Did you know that you can format individual words and letters differently within an Excel cell or text box? (more…)
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. Download Materials 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. https://vimeo.com/366395923 This session covers: a format to avoid and the one to use in its place keyboard and mouse…
Who doesn’t get satisfaction from drawing a line through a completed task? That sense of achievement. Well you can do the same in Excel. (more…)
When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that. (more…)
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation. (more…)
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. Download Webinar Materials https://vimeo.com/330698381 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…
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. (more…)