If you have used Microsoft Excel then you will know how powerful it is for our business and also personal use. Sometimes you just need your spreadsheet to also look good.
Whether you enter data yourself or import it form another source, the information doesn’t always look good. Cells do not expand automatically to fit their contents, headings do not appear bold, columns may need split or combined, the list goes on.
All of these are easily rectified a bit of basic Excel wizardry. I want to share with you my favourite easy formatting tips.
1. Adjusting columns width and row height. Depending on the size of text or numbers you enter into Excel, the cells do not automatically expand. The fastest way to get ll of you column widths and height is to use the ‘double click’ method.
You can either adjust all of your columns and rows or just a few. To adjust all of your columns and rows, select all of your worksheet.
- Hit CTRL+A twice to select all cells on your work. book. (Hitting CTRL+A once will select any cells that contain data)
- Hover your cursor on any columns line, or on any row then just double click.
If you only want to adjust the row or column height of a particular cell
- Hover your cursor above the header border between the column you want to change and the neighboring column to it’s right.
- Double click.
2. View your page breaks. Before you even attempt to print your worksheet, one major time saving formatting trick I use is to view my page breaks. This option is actually the default setting in Excel 2007, but f you do need to display them for any reason:-
- Office Button
- Excel Options
- Advanced
- Show Page Breaks
Not only does this save a few trees with less waste paper, but time as well by getting the printout correct first time. If you ware using earlier version of Excel then hit
- Tools
- Options
- View
- Tick Page Breaks under Windows options
3. Use the Format Painter Option. This is one of my favorite formatting tips. It’s so easy to do that you literally kick yourself for all the time you wasted before you knew it. Seriously!
Either format one set of cells or a cell as you want, highlight it, hit the Format Painter option (it’s the little paintbrush on the Home Tab), highlight the cells you want to format and bingo, your cells become formatted. I use this sometimes if there is formatting on an Excel sheet that I like. There is no messing around trying to recreate it when you can in a few clicks copy it using Format Painter.
4. Freeze Your Column Headings. One of the most basic tips, is one of the most useful. Scrolling through a large spreadsheet with hundreds if not thousands of rows can become almost impossible when you lose sight of your column headings. To keep those ever helpful headings, just freeze them.
In Excel 2007
- View Tab
- Freeze Panes
- Freeze Top Row
In earlier versions of Excel
- Select the row directly below your headings
- Window
- Freeze Panes
Now you can scroll away without guessing what your column headings are.
5. Split One Column Into Many. This tip is useful if you have inherited or imported data from an external source which is not in the format you require. For example a name file may have both the First Name and Last name in the one column. With the Text To Columns option in excel, it is really easy to get your data exactly as you want. In this case into two columns, First Name and Last Name split into separate columns.
- Select your column of data
- Data Tab
- Text To Columns
- Delimited (unless all of your entries are the same length then used fixed width)
- Next
- Check Which options applied best to you i.e Space, comma, semi colon (in a CSV file for example it’s usually a Comma between the First Name and Last Name).
- Leave the Treat Consecutive delimiters as one checked.
- Next
- Finish
I hope you find these top five Excel formatting tips useful, I use them most days and they form the basis for my Excel worksheets to look professional as well as being easy to read and navigate.