Excel's best tricks: How to make a calendar
Image: Rob SchultzSo, what else can Excel do? People ask this question all the time. The answer is “almost anything.” Excel has evolved into one of the most versatile programs available. In addition to spreadsheets that calculate everything, it’s also a database, a programming tool, a graphics program with charts, tables, drawing tools, photos, clipart, and even layout abilities, and it’s a limited, but functioning, word processor (with a spell checker, Thesaurus, grammar tools, research capabilities, translation functions, and more).
With its graphic features, you can create anything from a detailed drawing to an edited photo. You can make calendars, note pads, list pads, schedules, grid/graph paper, greeting cards, business cards, etc. If you’re wondering why anyone would use Excel over programs such as Photoshop and/or Illustrator, the answer is simple. Not everyone has access to graphics and/or photo-editing software. And, although Windows includes PC Paint as an Accessory program, it’s actually easier—in many cases—to use Excel for some projects. This week, a calendar; next week, note pads and lists. After that, we’ll tackle several other projects for those who prefer to use Excel for graphics.
How to start your calendar
Open a blank worksheet. Highlight Columns A through G, then roll your cursor down to extend the highlight through Row 7, making the range A1 through G7. From the Home tab, select the Cells group, and click Format > Column Width. Type 18 in the Column Width dialog box, then click OK. With the range still highlighted, select the Cells group, and click Format > Row Height. Type 75 in the Row Height dialog box, and click OK. Move your cursor to the Home position, A1. Change the Row Height to 118. Cursor down to A2 and change the Row Height to 30.
JD Sartain1-Select range, adjust column + row size.
Highlight the range A1 through G7 again. Select the Page Layout tab, choose Orientation from the Page Setup group, and click Landscape. Next, click Margins from the same tab and group, and select Custom Margins from the list. The Margins tab in the Page Setup window appears. Press the Tab key once, and your cursor moves to the first setting: Top.
TIP: It’s so much easier and faster to just tab and type through these settings, rather than trying to highlight each field box and cursor the up/down arrows.
In the Top field box, type .25, then press the Tab key and your cursor moves to Bottom. Enter .25, press the Tab key, cursor moves to the Left field box. Enter .25, Tab to the Right field box, enter .25. Press the Tab key, cursor moves up to Header, enter 0. Then tab again down to Footer, and enter 0. Press Tab again, and the cursor moves to the Center on Page panel. Check both Horizontally and Vertically, then click OK.
With the area still highlighted, click Print Area > Set Print Area from the same tab and group. It looks like nothing happened, but press the Home key once, and notice the solid (or dotted) line around the area you just selected. This is the area of your spreadsheet that Excel prints, as long as you don’t change the Print Area to something else. Highlight a new area: A2 through G7. Click the Borders button in the Fonts group, under the Home tab, then choose All Borders from the drop-down list.
JD Sartain2-Page Setup and Borders for your calendar.
Enter the days of the week in Row 2 (A2:G2), center horizontally and vertically, then enter the days of the current month on the calendar. Next, merge the top cells into one. Highlight Row A1 through G1, then select Merge & Center from the Merge button under the Home tab, Alignment group. Now, let’s add a fancy title (June 2015). Click Insert > Text > WordArt and choose a style for your text , then type June 2015 in the field box.
JD Sartain3-Merge cells and insert WordArt.
If you’d prefer a more ornate month and year, try some clipart. Delete the WordArt, then select Insert > Illustrations > Online Pictures, and type June in the search box. Choose an image for the title, then size to fit. Next, add some clipart images to decorate your calendar. Select Insert > Illustrations > Online Pictures again, type Summer in the search box, then choose from the filtered selection. Size to fit in the space provided. Note: Sizing down is usually no problem, but enlarging (or sizing up) will likely result in a blurred image.
JD Sartain4-Use WordArt or clipart to enter the month and decorate the calendar.
If you happen to select an image with unwanted areas, such as a picture with the wrong year, you can crop the image to remove that element, but only if it’s standing alone beside a border. First, the image must be selected so the Picture Tools/Format menus are displayed. Select Format > Crop > Crop. Notice the black “handles” around the image. Put your mouse cursor on one of the handles, hold down the left mouse button, and then slide the handle up, down, or over to crop out the unwanted sections. When satisfied, click anywhere outside the image borders and the image is cropped.
JD Sartain5-Crop unwanted parts out of the images.
Next, size and place. But the background is blue, and you want a transparent background. No problem. Select the image, (the Picture Tools/Format menus appear). Choose Format > Remove Background > Mark Areas to Keep. Use the handles on the image to adjust the border around the parts of the picture you want to keep. Since you’ve already cropped it, just stretch the borders towards the center, then click outside the image area and it’s done. The background is transparent, the wrong year is cropped out, and it’s sized to fit in the header space above your calendar.
JD Sartain6-Size, place, and remove the image background.
Last, add holidays and any special occasion days such as birthdays, graduation, or anniversaries. To add text to a date, position your cursor on the target date; for example, June 12th. Press the function key F2 to edit this cell. The cursor positions to the right of the number 12. Because the numbers are on the bottom-right corner—unless you want to change that—press the Home key to reposition the cursor before the day.
Now type the name of the occasion, then press Alt+Enter (hold down the Alt key, then press the Enter key, then release both keys), once for each additional line added—in this case, once to separate the text from the number, then three more times to move the text to the top of the calendar box. Now the data is on the right. No problem, just click the Align Right button on the Home tab, Alignment group and the data moves to the right.
JD Sartain7-Add special occasion days. Note: the image is cropped to fit this page. The original is a complete seven-day calendar.
Note: You cannot right-justify the number, then center or left-justify the text, because Excel doesn’t allow multiple paragraph styles in the same cell. You can click the Increase Indent button (also in the Alignment group) to move the data two pixels away from the right border. But you can use multiple font attributes such as Bold for the number and Italics or a different color for the text. Press F2 to edit, then highlight the number or the text and choose a new color or attribute. If you want both the same, then just position your cursor on that cell and choose an attribute and color.
