Quick Links
Excel is a powerful tool for handling data, but raw spreadsheets can often look dull and overwhelming. Luckily, a few simple formatting tricks can completely transform your data, making it more visually appealing and easier to interpret.
1Hide Excel Gridlines
Microsoft Excel displays a series of faint lines between cells, i.e. gridlines, by default. While gridlines can help you navigate the spreadsheet, they can also make your data look cluttered and less presentable. You can hide the gridlines to get a polished clean look.
To do this, simply navigate to theViewtab, and under theShowgroup, uncheck theGridlinesbox. Your data will immediately appear cleaner, making charts, tables, and formatted cells stand out. If needed, you canadd borders to specific cells or rangesto highlight critical data.
2Create a Big Header for the Spreadsheet Title
I also like to create a big, bold header at the top of the spreadsheet for the title. This header spans multiple cells and describes the purpose of the spreadsheet in a few words.
To create such a header, navigate to the top of the Excel spreadsheet and insert an empty row (Right-click, then selectInsert, andEntire Row) if you already have content in the top row. Next, selectA1and type the title of the spreadsheet, then select the range of cells to contain the spreadsheet title. Finally, navigate to theHometab, clickMerge & Center, and all the selected cells will be combined with your title at the center.
To make the title stand out even further, increase the font size, use bold formatting (Ctrl + B), and choose a distinctive color that complements your data.
3Format Your Data as Table
I like to format large datasets in Excel as tables. Excel tables appear less cumbersome and are easier to manage. Though table styling can appear generic, it is a useful trick that can be used to level up your Excel data presentation chops.
Toformat your data as a table in Excel, select the range of cells you want, go to theHometab, selectFormat as Tablefrom theStylessection, and choose theTable Styleyou prefer from the light, medium, and dark options.
If you are put off by the default options, you can create a new style by going toNew Table Styleand customizing the colors, borders, and fonts to match your preferences.
4Leave Space Between the Data and the Headings
Formatting doesn’t have to be complicated to make a difference. It can be as simple as leaving a bit of space between your data set and the spreadsheet’s row and column headings. When your data is crammed right up against the headings, it can look cluttered and harder to interpret at a glance.
To create this visual separation, you’re able to insert a blank row and column before your data starts. This can be especially helpful when presenting large datasets, as it prevents the information from feeling too dense.
5Keep Text Wrapped Within Their Cells
When dealing with data that contains lengthy labels or descriptions, you can use text wrapping to preserve the column width and make the text easier to read. By default, Excel allows text to overflow to adjacent empty columns or truncates characters if the next column contains data.
you may enable text wrapping by selecting the cell or group of cells with lengthy text strings, then navigating to theHometab, and clickingWrap Text. Text wrapping ensures that lengthy text remains within the cell, automatically wrapping onto the next line without altering the column width.
6Use Contrast to Make Text Easier to Read
It never hurts to keep basic graphic design principles in mind whenstyling your Excel spreadsheet. The contrast between the text color and the background color makes your spreadsheet easier to read.
Excel uses a high contrast combination for the text and background, by default. you’re able to change text color using theFont Coloroption and choose a different background color from theFill Colormenu. Excel will show a text preview and tell you if the color combination is high or low contrast. You can also check the box at the top to show high-contrast colors only.
7Use Special Formatting for Formulas
Another formatting trick I have found helpful is using special formatting for cells that have formulas. This makes it easier to distinguish between regular, inputted data and calculated results in complex spreadsheets.
To format formula cells, select the cells that contain formulas and highlight them using a unique background color from theFill Colormenu or bold (Ctrl+B).
Excel allows you to automatically locate all formula cells by pressingCtrl + G, selectingSpecial, and choosingFormulas.
Applying this formatting trick helps you and others quickly identify where calculations are happening, making it easier to audit the spreadsheet for errors. This trick is especially valuable when sharing workbooks with colleagues or clients.
8Apply Vertical Headers
Vertical headers are a lesser-known formatting trick for organizing spreadsheet data in a more efficient and compact layout. These headers can conserve space and make your spreadsheet easier to read. You can either turn your column headings into vertical text or create row headings with vertical orientation.
To use vertical headers, select the cells containing your headers, go to theHometab, click on theOrientationicon, and chooseVertical Text. However,Vertical Textadds spacing to the letters, which might push the text beyond the boundaries of the cell. You can correct this by going to theHometab, choosingFormat, andAutoFit Row Height. Instead ofVertical Text, you can also pick any of theAngleorRotateoptions in theOrientationmenu.
This trick works particularly well for tables with narrow columns and when you’re trying to fit everything on a single page without text wrapping.
9Use the Format Painter
The Format Painter comes in very handy when you need to reuse the same formatting—color, font style or font size—across different cells. It is one of those extremely easy-to-use features that willsave you time in Microsoft Excel.
The Format Painter is essentially copying and pasting for formatting. you may find it under theHometab in theClipboardsection. Select the text with the formatting to be copied, clickFormat Painter, and select the cell, group of cells, or row/column to apply the formatting to. Done!
Alternatively, you can use the shortcutsAlt+Ctrl+CandAlt+Ctrl+Vto copy and paste formatting.
Formatting makes the difference between a bland, cookie-cutter spreadsheet and one that is bold, clear, and professional. While aesthetics is important, clarity of communication is the main reason why you should be careful with your Excel spreadsheet’s formatting.
