Date Alignment Trick in Excel

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.

Conditional Format to Display Only the First Entry

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 section “Use a formula to …”.

In the formula box enter the following formula.

=COUNTIF($A$2:A2,A2)>1

Click the Format button and use the Font tab and change the font colour to White and click OK and then OK again.

The result is shown below.

The formula for a conditional format must return TRUE to trigger the format. The type of formula that you use is called a logical test, which returns either TRUE or FALSE.

The use of the $ signs is very important in this formula. The COUNTIF function counts the number of entries in a range. If the COUNTIF result is above 1 it is a duplicate. In cell A2 the formula will ALWAYS return 1 as it is counting itself.

When creating a formula-based condition across a range you need to build the formula to refer to the top left cell of the range. In this case we need the range to expand as the range extends down the sheet. Hence, we didn’t use any $ signs on the last two A2 references used.

In cell A3 the formula will be.

=COUNTIF($A$2:A3,A3)>1

This is because the A2 references in the original formula had no $ signs, so they will change with the cell to A3. In our case this COUNTIF will return 2 because the date in cell A3 is a duplicate of the date in A2. This will trigger the format.

This formula expands as the range extends. It uses the cell reference of the cell it is in to determine if the entry is the first entry or a duplicate. This formula will not change the format of the first entry, but it will change the formats of any duplicates.

Input Data Display Hack for Excel

Getting the format white

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.

One Minute to Excel #12 – Hide Cell Entries

It is a special format

You can’t hide a cell, but you can stop the cell value from displaying on the sheet.

It involves a custom number format.

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 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.

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.

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

Unlocking coloured cells in Excel

Styles and Find solutions

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.

Free Excel Webinar Recording – Custom Number Formats

Feedback score 92%

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.

Webinar Materials

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.

As always I will share a few other tips.

Unique Entries in Excel via a Conditional Format

Filtering to the rescue

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.