Web Worker Daily

Throughout the years I’ve covered software, I’ve noticed a quirk that has to do with Microsoft Excel. Specifically, many people use Excel for tasks that don’t fall into classic spreadsheet usage patterns.

I know countless people who like the neat-and-tidy, row-and-column format in Excel for managing lists, due dates and other project management tasks, and more. I even know a talented web designer who uses huge, multi-page, tabbed Excel sheets to store representations of buttons, slider bars, graphics and other assets for his web page prototyping. Using this Excel-based bank he can quickly combine the various graphical assets in a new Excel sheet, hand it off to a Dreamweaver jockey, and say “make this HTML page.”

Software users like to use what they’re familiar with, and many are more familiar with Excel than they are with project management applications, list managers and the like. In this post, I’ll round up several useful tips for free-form Excel use. They’ll save you time, and hopefully expand what you can do with Excel.

Use Shortcut Keys. Ctrl+Z will undo your last cell entry. Once you’ve copied a cell you want to paste in multiple locations, use Ctrl+V to do a multiple paste. Find, or find and replace anything in a sheet quickly by hitting Ctrl+F. Use Ctrl+X to cut material. Use Ctrl+arrow keys to move to the edge of any selected region. Use Ctrl+A to select all cells in a sheet. Use Ctrl+Spacebar to select entire columns. Use Shift+Spacebar to select entire rows. Use Ctrl+b for bold. Use Ctrl+u for underline. Use Shift+F3 to insert any Excel function from a list.

Auto-Extending Dates. If you’re creating an Excel sheet that will track due dates for deliverables, and you want to automatically populate a column with dates in chronological order, type in a date in, say, cell A1. At the bottom of that cell, you’ll see a small black square, or handle:

Put your cursor directly over that black square until it turns into a cross, then drag the cross down to, say, cell A15 and let go of the mouse button. Excel will automatically fill in your column of chronological dates.

Auto-Extending More Chronological Data. You can use the same tip above to drag the black square to populate consecutive cells with consecutive integers (type 1 in cell A1 and drag the square down to create 2 in A2, etc.), months (type Jan in cell A1 and drag the black square down to create Feb in cell A2, etc.), and more. You can also drag the black square across cells in a row to horizontally auto-create cell entries.

Use the Format Painter. The Format Painter is a fast and easy way to take formatting you’ve applied to cells and apply the same formats to other cells. The Format Painter icon is on the Formatting toolbar and looks a paintbrush:

Click on the icon while in the original cells, then click on the paintbrush, and then click on the cells you want formatted the same way.

Sorting Information in Rows and Columns. Type the numbers 1 through 5 in cells A1 through A5. From the Data menu click Sort. A dialog will appear that allows you to sort in ascending or descending order, or in other ways. You can also use the Sort dialog to sort dates, months, and more.

Hide and Unhide Columns. If you track a lot of information in any one spreadsheet, it can be very useful to hide columns in the sheet, so that you can see just the columns you want at any given time. To hide a column, select a cell in the column and hit Ctrl+0 (that’s zero, as in hide, not the letter O). To unhide it, select the cells at left or right of the column and hit Ctrl+Shift+0.

Use Print Preview and Setup. When they go to File, Print to print out a sheet, many Excel users don’t use Print Preview at all. After you hit File, Print, hit the Preview button in the Print dialog box to look at your page’s formatting. To make any changes, choose Setup and adjust the look.

Capture High-Resolution Charts. If you like to capture charts and graphics within Excel, you may often be disappointed by the resolution you get when you take them to other programs, such as Word. If you’re doing a brochure or other document where you want really high-res graphics, hit Ctrl+PrtScrn on your keyboard to capture the page the graphic is on, then go into a graphics editor (IrfanView is a free download on the Web that works well, or Photoshop or any other good editor) and do an Edit/Paste to drop the graphic in. The graphics editor will let you up the resolution, and then you just copy and paste it to the program you’re putting it in.

View Several Workbooks at Once. If you have several workbooks open at once, pick Arrange from the Window menu and make sure you’ve selected Tiled. Click OK. You can adjust the size of any tiled window by going to the edge until your cursor shifts shape and stretching the edge.

View Different Parts of the Same Sheet. A fast way to view several parts of a sheet is to select Split from the Window menu, which gives you four divided panes showing segments of your sheet.

Quick Sum Formulas. The SUM formula is the most commonly used formula in Excel. To create one quickly at the end of a row or column of numbers you want to add up, hit the Alt key and type the equal sign.

Close Several Sheets at Once. Did you know that if you have several Excel sheets open concurrently, you can save and close them all quickly? To do so, hold the Shift key down as you pull down the File menu. You’ll see a Close All option on the File menu as you do this.

Do you use Excel for non-standard spreadsheet tasks? Do you have any good Excel tips?