Microsoft Excel Formatting Tips: Collection of 72 Quick and Easy Tips for Handling Excel Formats

Endless Possibilities with Excel Formatting

Most Excel users are familiar with the software’s basic formatting abilities, such as changing the size and background color of cells and their contents. But, what most don’t realize is that these tools can be used for much, much more.

With this collection of 72 Excel formatting tips, Mr. Excel explains how you can use the software’s formatting capabilities to enhance other organizational and analytical tools. All of these tutorials are written in a sharp, clear manner with accompanying screenshots and examples so you can see the tools in action.

Table of Contents

• Create a Chart with One Click
• Change a Chart from a Chart Sheet to an Embedded Chart
• Customize Anything on a Chart with Right-click
• How to Minimize Overlap of Pie Chart Labels
• Add New Data to a Chart
• Add a Trendline to a Chart
• Display Profitability in a Profit Waterfall Chart
• For Each Cell in Column A, Have Three Rows in Column B
• Copy Formatting to a New Range
• Copy Without Changing Borders
• Leave Helpful Notes with Cell Comments
• Force Certain Comments to be Always Visible to Provide a Help System to Users of Your Spreadsheet
• Control Name That Appears in Comments
• Change Shape of Comment to a Star
• Add a Pop-up Picture of an Item in a Cell
• Add a Pop-up Picture to Multiple Cells
• Change the Background of the Worksheet
• Add a Printable Background to Your Spreadsheet
• Remove Hyperlinks Automatically Inserted by Excel
• Change Width of All Columns in One Command
• Control Page Numbering in a Multisheet Workbook
• Use White Color for Fonts to Hide Data
• Hide and Unhide Data
• Temporarily See a Hidden Column Without Unhiding
• Navigate to Each Precedent
• Paste a Live Picture of a Cell
• Monitor Far-off Cells in Excel 2002 and Later Versions
• Add a Page Break at Each Change in Customer
• Use Horizontal Page Breaks Even When You Use Fit to N Pages Wide
• Hide Error Cells When Printing
• Organize Your Worksheet Tabs with Color
• Copy Cell Formatting, Including Column Widths
• Why Does Excel Mark All My TRUE Cells with an Indicator?
• Debug from a Printed Spreadsheet
• Copied Formula Has Strange Borders
• Double Underline a Grand Total
• Use the Border Tab in the Format Cells Dialog
• Fit a Slightly Too-Large Value in a Cell
• Show Results as Fractions
• Color All Sales Green for a Day if Total Sales > $999
• Color Sales for a Day That Exceeds $999
• Turn off Wrap Text in Pasted Data
• Delete All Pictures in Pasted Data
• Draw an Arrow to Visually Illustrate That Two Cells Are Connected
• Add an AutoShape to Your Worksheet
• Draw Perfect Circles
• Draw Perfect Squares
• Draw More Than the Four Basic Shapes
• Change an Existing AutoShape
• Add Text to an AutoShape
• Use the Toolbar to Change Three Colors of an AutoShape
• Rotate an AutoShape
• Alter the Key Inflection Point in an AutoShape
• Add a Shadow to an AutoShape
• Add a 3-D Effect to an AutoShape
• Add Connectors to Join Shapes
• Join Two AutoShapes
• Change Properties of Only One AutoShape in a Group
• When Two AutoShapes Overlap, Control Which Is on Top
• Make Any Logo into an AutoShape
• Use the Scribble Tool
• Place Cell Contents in an AutoShape
• Draw Business Diagrams with Excel
• Draw Org Charts with Excel
• Add WordArt to a Chart or Worksheet
• Use MapPoint to Plot Data on a Map
• Add a Dropdown to a Cell
• Store Lists for Dropdowns on a Hidden Sheet
• Add a ToolTip to a Cell to Guide the Person Using the Workbook
• Configure Validation to “Ease up”
• Use Validation to Create Dependent Lists

More Excel Tips

The Excel Formatting Tips series is actually Part 4 of a much larger collection of Excel tips written by Bill Jelen, also known as Mr. Excel. You may also be interested in the following groups that make up the other parts of the entire collection.

• Part 1: The Excel Environment – The first part of Mr. Excel’s popular series takes a look at all the different ways you can customize the Excel interface, making your workflow process more productive and ultra efficient. This group of tutorials also includes printing tips and advice on how to use Excel’s automated features.

• Part 2: Calculating with Excel – Want to learn how to take full advantage of Excel’s calculation features? Check out this set of 91 tips that cover everything from calculating multiple loan payment scenarios to playing dice games, like Craps, in Excel.

• Part 3: Wrangling Data – Learn more about the data organization and analysis tools found in Excel, including Pivot Table tricks and What-If Analyses.

• Part 4: Formatting Excel – You can use Excel’s formatting features for much more than just making your final spreadsheet “look good.” You can also utilize these tools to add new data to existing charts, recognize exceptions in large reports, and much more.