Quick Links

• Merging Cells

• Center and Left-Aligning Numbers

• Not Using a Recognized Date Format

• Color-Filling Whole Columns or Rows

• Using Different Fonts

• Formatting Tables Manually

There's a fine line between a well-formatted Microsoft Excel worksheet and one that's full of issues that take time to fix. Whether you're an Excel newbie or a seasoned pro, avoiding these formatting habits will ultimately speed up your workflow and ensure your spreadsheet works as expected.

6 Merging Cells

Picture this: you have a row in Excel where the same data applies to each cell. Rather than repeating the same value multiple times, you decide to click "Merge And Center" in the Home tab on the ribbon.

That looks great, right? However, formatting your data in this way is a sfouurefire way to give yourself and your coworkers a headache down the line.

Microsoft Excel works best when you have a consistent grid of individual cells placed in rows and columns, so merging cells disrupts this spreadsheet structure.

For example, if you add filter buttons to the top row of your data containing merged cells, and try to sort that data using these buttons, you'll see an error message. The same happens if you click "Sort" in the Data tab on the ribbon.

You'll also face a similar issue if you try to paste data from an unmerged row to a merged row.

Don’t Merge and Center in Excel: Center Across Selection Instead

I was a loyal merger and centerer until I found this tool.

Posts 11

First, select the cells you would have previously merged, and click the dialog box launcher icon in the bottom-right corner of the Alignment Group in the Home tab. Alternatively, press Ctrl 1, and hit the Right Arrow key.

Next, expand the "Horizontal" drop-down menu, and click "Center Across Selection." Then, click "OK."

Now, although the cells look like they're merged, their structure and integrity have been preserved. As a result, you can use the filters and copy and paste data between rows without triggering any error messages.

5 Center and Left-Aligning Numbers

When you enter text into a cell in Microsoft Excel, it aligns to the left, and when you enter a numerical value, it aligns to the right.

Related

Excel's 12 Number Format Options and How They Affect Your Data

Adjust your cells' number formats to match their data type.

Posts

As a result, you might choose to left-align the cells containing numbers to make your spreadsheet look tidier. On the other hand, you might select all the cells and apply center alignment to everything in your spreadsheet.

But stop there! There are good reasons why Excel automatically places numerical values to the right of a cell.

On the one hand, having all numbers aligned to the right makes them easier to read and compare than when they're center-aligned.

4 Not Using a Recognized Date Format

When you enter a value into a cell that Excel recognizes as a date, the number format in the Number group of the Home tab will change to either Date or Custom.

However, if it tells you that the cell still has General number formatting, it means that what you've entered is recognized as text. As a result, you won't be able to use the value in formulas, like working out the difference between two dates.

Related

What Are Date and Time Serial Numbers in Microsoft Excel, and Why Do They Exist?

Date and time calculations would be impossible without them.

Posts

To fix this, first, clear the cell by selecting it and pressing Delete. Then, press Ctrl 1 to launch the Format Cells dialog box, and click "Date" in the Category list of the Number tab. There, you'll see the various date formatting options you can select to force Excel to see your value as a date.

Code

Description

Example

YYYY

A four-digit year

2025

YY

A two-digit year

25

MMMM

The full month name

December

MMM

A three-letter month

Dec

MM

A two-digit month

01

M

A one-digit month for January to September, and a two-digit month for October to December

1 or 12

DDDD

The full weekday name

Wednesday

DDD

A three-letter weekday

Wed

DD

A two-digit day of the month

01

D

A one-digit day of the month for the first nine days, and a two-digit day of the month for days 10 to 31

1 or 31

After clicking "OK," type the date using the structure you selected or created, and Excel will recognize the number format correctly.

3 Color-Filling Whole Columns or Rows

In this screenshot, the whole of column D is manually formatted with a blue cell fill to indicate that it contains the totals.

However, this means that you have more cells formatted than necessary, making your spreadsheet look untidy and slowing its processing speed.

Related

7 Ways to Speed Up Your Excel Spreadsheets

Don't twiddle your thumbs waiting for Excel to respond.

Posts

Also, if you select some cells in the colored column and shift them to the right to insert more data, the formatting will become skewed.

The best alternative is to format your data as an Excel table, and adjust the table's design to bold the right-most column.

To do this, select the data, and in the Home tab on the ribbon, click "Format As Table."

Then, select a cell in the table, open the "Table Design" tab, and check "Last Column" in the Table Style Options group to bold the total column. You could also select the data and increase the font size for added emphasis.

However, if your data contains a dynamic array, this method won't work because Excel tables can't accommodate spilled arrays. Also, you might want to emphasize a different column.

Related

Everything You Need to Know About Spill in Excel

It's not worth crying over spilled references.

Posts

In these scenarios, use conditional formatting. In this example, you want the cell in column E to turn blue when there's a number in column A of the corresponding row.

So, select the whole of column E by clicking the column header, and in the Home tab on the ribbon, click Conditional Formatting > New Rule.

Then, click "Use A Formula To Determine Which Cells To Format," and in the empty formula field, type:

=ISNUMBER($A1)

Next, click "Format" to select the fill color, and click "OK" to close the dialog boxes.

Because you told Excel to color the selected cells when the value in column A is a number, only the relevant cells are colored, and when you add more data to column A, the corresponding cell in column E adopts that formatting.

2 Using Different Fonts

When teaching people how to use Excel, I always emphasize the importance of consistency, and this is particularly true when it comes to font selection.

Mixing typefaces in Excel makes the spreadsheet appear cluttered, disorganized, and unprofessional, and can present challenges for people with reading difficulties.

Instead, choose a single sans-serif font that is easy to read, clearly differentiates between letters and numbers that might look the same in other fonts, and isn't too stylistic. Aptos, Arial, Tahoma, and Verdana are good choices for readable and professional spreadsheets.

Related

Which Fonts Should You Use in Excel?

Optimize your data's readability.

Posts

1 Formatting Tables Manually

In Microsoft Excel, you can apply manual formatting to data to increase its readability and make certain values stand out.

In this example, a border has been added between rows 1 and 2, the values in column C are bold, and every other row has been filled gray.

However, if you wanted to add data to row 13 or column D, you would need to duplicate the formatting manually.

What's more, if you were to insert a new row in the middle of the data, the banded row formatting would be out of line.

In fact, formatting your table manually could cause many other issues down the line, all of which would take time to fix.

Instead, select your unformatted data, and in the Home tab on the ribbon, click "Format As Table."

Related

Everything You Need to Know About Excel Tables (And Why You Should Always Use Them)

This could totally change how you work in Excel.

Posts 2

Then, in the Create Table dialog box, make sure the correct cells are selected, check "My Table Has Headers" if this is the case, and click "OK."

Now, your data is reformatted as an Excel table, and in the Table Design tab, you can add and remove row banding, filter buttons, a total row, and various other options.

What's more, when you add more rows and columns by clicking and dragging the table fill handle in the bottom-right corner, any table formatting will be automatically applied to these new cells.

If your data contains spilled arrays, you can't format it as an Excel table. In this scenario, keep your manual table formatting to a minimum, such as using bold font in the column headers. Also, rather than coloring whole rows or columns to make them easier to read, click "Focus Cell" in the View tab on the ribbon.

---

Ultimately, when using Microsoft Excel, aim to make your spreadsheet easy to read and your data easy to manipulate. Avoiding the formatting habits discussed in this article will go a long way in helping you achieve this.

The above is the detailed content of 6 Microsoft Excel Formatting Habits You Need to Ditch. For more information, please follow other related articles on the PHP Chinese website!