• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Fixing 5 data formatting nightmares

by on
in Excel Training,Office Technology

Numbers stored as text

Numbers stored as text can be irritating, whether it’s just for aesthetics or these mixed numbers making your VLookUps misbehave. The following fix may seem strange to start out, but it will have all your numbers working like numbers in no time.

  1. Type a 1 in a blank cell and copy it using Ctrl+C, right-clicking Copy or selecting the Copy button on the ribbon (Home tab, Clipboard group).
  2. Select the column of mixed numbers and numbers stored as text.
  3. Click the dropdown arrow below the Paste button, and choose Paste Special.
  4. Click the radio button next to Multiply in the Operations section of the Paste Special dialog box.

Voila! All numbers!





Zero front-filled numbers

If you’ve just done the operation above or imported data from another system that leaves off required leading zeroes, try this operation to put them back. Caution: This particular method works only with fixed-length fields.

  1. Select the cells or the column of cells that could have leading zeroes.
  2. Click the dialog box launcher (small down-right-facing arrow) on the Number group.
  3. Select Custom.
  4. Type in the number of zeroes that represent the field length of your data in the Type: field. And, they’re back!

Conflicting date formats

If you’ve ever wasted time trying to fix multiple date formats in the same column, you’ll appreciate this tip.

  1. Select the cells with the mixed date formats.
  2. Click the Clear button in the Editing group on the Home tab (far right).
  3. Choose Clear formats. Don’t panic! Your dates have been converted to serial number date format.
  4. Select the desired date format from the Number format dropdown field, Number group, on the Home tab.

Now, all your dates are in the same format.

Double-space alternative

Perhaps you need to generate a printed report from your very voluminous list. If you think it might be easier to read double-spaced, you’re probably right. However, achieving double-spacing by inserting blank rows robs you of some of Excel’s greatest features! Here’s a better alternative.

  1. Select the cornerstone, which selects all cells on the worksheet.
  2. Position your mouse cursor be­­tween two row numbers so that you see a two-headed black double arrow. (Arrows will be pointing up and down.)
  3. Click and drag the height of the cell so that it is roughly double the size.

Now you have all the visual impact of double-spacing, without losing any functionality!

Blank column alternative

Similar to the above issue, many people insert blank columns to achieve better vertical spacing. For the same reasons that inserting rows is not the best choice, inserting columns is not the best choice either.

  1. Select the right-most cell you wish to separate from its counterpart column on the left.
  2. Click the Increase Indent button in the Alignment group on the Home tab.

Be sure your column is wide enough to take the additional spacing.


Contributor: Melissa P. Esquibel, Microsoft® ­Certified Trainer

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/35956/fixing-5-data-formatting-nightmares "

Leave a Comment