Formatting cells in Excel
We've learned how to navigate Excel spreadsheets, resize rows and columns, and format numbers. But how do Excel experts get their sheets to look so beautiful? To answer that question, we'll take a look at the options that Excel includes for cell formatting.
Basic text formatting
If we select any cell and head to the
Of particular note are the font color and cell background color drop-downs, which allow us to style cells as we see fit:
To the right, we'll see a number of paragraph options, including cell vertical alignment (top, middle, or bottom); horizontal alignment (left, right, and center); and indentation:
The "wrap text" option on this page allows us to toggle text wrapping within a cell on or off. If toggled on, text within a cell will automatically wrap to multiple lines rather than being cut off or just appearing on one line:
And, the "merge and center" button on this same tab allows us to merge multiple cells together to create headers. Try selecting a block of cells and hitting the "Merge" button to transform it into one larger block. We usually use this option at the top of charts, like so:
Looking for more cell formatting options? Try right-clicking a cell or selected range of cells; then hitting the
A cell formatting dialogue will appear with a number of tabs that allow us to format our spreadsheet in a ton of different ways:
- The Number tab allows us to change number formats with some more expansive options, like commas (,) and number of decimal places.
- The Alignment tab allows us to choose vertical and horizontal alignment; angle text; wrap text; and merge and center cells.
- The Font tab allows us to modify text with features like colors; font styles; underlines; bold; italic; superscript; subscripts; strikethroughs; and different sizes.
- The Border tab allows us to add various borders to our cells, including double borders, thick borders, and colored borders.
- The Fill tab allows us to fill the backgrounds of cells with custom colors or patterns.
Take some time to explore the Format Cells dialogue to get a feel for all of the formatting options we have at our fingertips. We can combine these formatting options to beautify our spreadsheets in many different ways — so have fun and experiment to get a sense for what works well for you!
Excel also has a number of built-in Excel styles available for easy access. Check out the
How we format our spreadsheets
Take a look at the below spreadsheet, which we've formatted using our standard procedure. Of course, formatting is totally subjective — so you can make your spreadsheet look however you want it to look — but consider the following as inspiration for where you might want to start.
In the above sheet, we've:
- Formatted column headings with bold styling and a light blue cell background for easy viewing. We've also added a thick white border to the cells to differentiate them from each other.
- Used Merge and Center on top of column headings, where appropriate.
- Used appropriate number formats to accurately represent the quantities we're analyzing.
- Indented titles appropriately in the first column of our data set to represent the various components of different quantities. For example,
Units soldand Sale price per unitare both components of Revenue, so they're indented below it to represent dependency.
- Used preset Cell Styles to represent key assumptions and outputs on our sheet.
Save three hours of work a day with these 5 simple Excel hacks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.
- Why the TRUE and FALSE functions are so important...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...