- Creating workbooks in Microsoft Excel is easier when you can quickly present your data in a visually appealing way.
- You don’t have to format each cell individually — there are several shortcuts that save you time and effort.
- Use clever formatting tricks in Excel like the fill handle and number formatting to make your workbooks look slick and professional.
Spreadsheets shouldn’t be dull and dry — if you want people to read them, that is. Learn a few tricks to make formatting your data easy and create Excel workbooks that get engagement from your colleagues.
Formatting Tricks in Excel 1: Using the Fill Handle
Step 1: Open an existing Excel workbook. Here we have an unfinished table showing profits for a small business.
Step 2: You want to complete the Month column, but you don’t want to type each month in manually. No problem, just click cell A2 (January) and hover the cursor over the bottom right corner of the cell. You’ll see a small black plus sign appear — this is the fill handle.
Step 3: Hold the fill handle and drag it down to cell A13 to fill in the months in order. However, if you had hundreds or thousands of cells to fill, this could take time. Instead, you can double-click the fill handle to complete all entries to the end of the table.
Formatting Tricks in Excel 2: Number Formatting
Step 1: You’ll notice that the numbers in the Profits column aren’t formatted to show what currency they represent. You could go through each cell individually and add a dollar sign, but you probably don’t have time for that. As a quicker alternative, select all the cells you want to change.
Step 2: Right-click the selected cells and look for the option Format Cells.
Step 3: Left-click format cells and choose Currency under the Number tab.
Step 4: Click Symbol and choose the currency you want to display, e.g., United States dollars.
Step 5: Adjust the decimal places to 2 and choose how you want negative amounts to appear (there are four options). There’s also a handy Sample section so you can see how your amounts are going to look in the workbook.
Step 6: Press OK and your numbers will have automatically transformed into amounts of money. This change is permanent unless you alter the formatting, so if you have to change an amount, you don’t need to reformat it again.
Formatting Tricks in Excel 3: Adding Graphics to Data
Step 1: Professional spreadsheets are rarely just text and numbers. It’s easy to add graphics that make your workbooks easier to understand and navigate. First, click on the Insert tab, then look for icons.
Step 2: This opens the Icons window which offers hundreds of free graphics you can add to your data. Use the search bar or view categories, e.g., Business.
Step 3: Click on the graphic (or graphics) you want and press the Insert button.
Step 4: Drag and drop the icon to where you want it on your table or chart. It’s positioned above the grid so it won’t interfere with your data.
Step 5: If you want to change the color of the icon, click the Graphics Format tab.
Step 6: Click the dropdown menu next to Graphics Fill and choose the correct color.
Step 7: If you want more than one color in your graphic, you have to convert the icon to a shape. While still on the Graphics Format tab, choose Convert to Shape.
Step 8: You can now select each element of the shape and color it accordingly using the Shape Format tab. You can also re-size graphics using the pull handles around the edge of the selection frame.
