Category Archives for "Formatting"

Top 29 Best Pivot Table Tips

Analysis , Excel , Formatting , Pivot Table and PowerPivot , Top Best Tips

It is easy to say that Pivot Tables and Power Pivot are the two most powerful features in Excel. I don’t mean time saving as with Excel VBA macros, but rather they are powerful because the decisions made from Pivot Table (and Power Pivot) results drive key business decisions. I’ve built tons of Pivot Tables […]

Continue reading

The Best Excel Checklist

Checklist , Excel , Formatting , Personal Performance , Productivity Improvement

This Excel checklist template has a lot of features. It’s easy to create and modify and it could save you from serious mistakes. If you or your co-workers do important, repetitive tasks where a missed step could cause injury, failure, or serious problems, then you need to use a checklist. Download the Best Excel Checklist […]

Continue reading

Creating Custom Date, Number or Percentage Formats

Excel , Formatting

Excel already has many numeric and date formats built into its Format Cells dialog box. But, you can create any format you need, including custom formats for, turning numbers or dates into text displaying the day of the week adding text to numbers making numbers that are invisible turning decimals into fractions adding international currency […]

Continue reading

Custom Numeric Format Layout

Excel , Formatting

Look at the Type list in the Number tab of the Format Cell dialog box. These format specifications follow a pattern you must know if you want to use them or create a custom format. Look at the custom formats in the Type list and notice that they can have up to four parts. Each […]

Continue reading

Custom Numeric Formatting Symbols

Excel , Formatting

Excel has custom formatting symbols for just about everything, including colors, text, and special characters. I’d recommend you click on some custom formats in the Type list, then modify them with these custom formatting symbols to see the effect. Symbol Format Effect General Unformatted cells. Displays numeric precision to match cell width. # Placeholder for […]

Continue reading

Predefined Date and Time Formatting Symbols

Excel , Formatting

Before you begin creating custom date and time formats it’s a good idea to look through the predefined date and time formats in the Format Cells dialog box. These are the predefined date and time formatting symbols, Format Example m/d/yy 4/1/16 d-mmm-yy 1-Apr-16 d-mmm 1-Apr mmm-yy Apr-16 h:mm AM/PM 6:15 PM (12-hour clock) h:mm:ss AM/PM […]

Continue reading

Custom Date and Time Formatting Symbols and Examples

Excel , Formatting

You can make almost any date and time format you want with Excel’s custom formatting. For example, I don’t like the dashes in between date characters and I do like to use a three letter abbreviation for month, so my favorite custom format in the US is, mmm d, yyyy Apr 1, 2016 Date and […]

Continue reading

Change All Formats on a Worksheet While Using Minimal Memory

Excel , Formatting

If you want to change most of the number, date, or time formats on a worksheet, don’t change them cell by cell that takes memory in large worksheets and can reduce speed. Instead select the entire column by clicking on the column header (or press Ctrl+spacebar) or the entire row by clicking on the row […]

Continue reading

Hiding Numbers with a Custom Format

Excel , Formatting

If you want to hide a number in the display, but keep it for use in calculation, then create a custom format with no formatting codes between the segment. For example if you want to only show positive numbers create a custom format like this, #,##0.00;;

Continue reading

Hiding Zeros in Cells or Worksheets

Excel , Formatting

As you would expect in Excel there are a couple of ways of hiding zeros. In fact, there’s a bunch of ways to hide zeros. Use the one that’s best for your circumstance. To hide zeros throughout a worksheet in Excel 2007/2010 (which usually isn’t a good idea), 1. Choose File, Options to display the […]

Continue reading