Tips for Excel, Word, PowerPoint and Other Applications

Quick Tip: Need to apply sheet level formatting (e.g., page orientation, margins, headers, etc) from one worksheet to another? Simply select the sheet from which you want to take the formatting, hold SHIFT and select as many other worksheets as desired, and then go File: Page Setup, OK. Now, all of your selected sheets should have the same page formatting.

Quick Tips

Quick tips are just that, quick time-saving or frustration-saving tips that can be put to immediate use and are too small to qualify for a full article. Here they are, in no particular order.

Quick Tip: A ' character before a formula or a numerical string forces Excel to display the contents as text string instead of a numerical string. So, you can use '000123 to show the contents as 000123 instead of 123 or show =sum(A1+B1) as the fomula instead of the results of the calculation. This can be problematic if you receive a file with a lot of these values since a normal search & replace (CTRL H) won't work, nor will TRIM or a RIGHT statement, since the ' isn't recognized as a discrete value. So, how do you convert values formatted this way? You can use a VALUE formula to convert a text string that represents a number to a number. Unfortunately, this won't convert formulas back to their values.

Quick Tip: If you're trying to search and replace * symbols, you can't use * (wildcard) as your search criteria otherwise you'll replace everything. If you use ~* as your search criteria however, the ~ acts as an escape character that enables Excel to treat * as any other character instead of a wildcard.

Quick Tip: If you need the count of unique values, that's a little more difficult. It requires an array formula, but it's really quite elegant. Assuming your list is in cells B4:B17, here's what it looks like. Don't forget to hit CTRL+SHIFT+ENTER to activate the array when you're done.

SUM(1/COUNTIF(B4:B17,B4:B17))

Quick Tip: Want to know how many instances of a value exist in a list of values? Assuming your first list entry starts in cell A1, enter the following formula in cell B1:

COUNTIF(A:A,A1)

Copy this formula down the list as necessary and you'll get a count of repeated values for every entry in the list.

Quick Tip: Need to apply sheet level formatting (e.g., page orientation, margins, headers, etc) from one worksheet to another? Simply select the sheet from which you want to take the formatting, hold SHIFT and select as many other worksheets as desired, and then go File: Page Setup, OK. Now, all of your selected sheets should have the same page formatting.

Quick Tip: I found this tip at Just Pivot Tables under the July 4th entry ... hmmm, another person posting tips on the 4th of July. Anyways, if you're a user of pivot tables, but you hate having all of those subtotals messing up your data table, there is a really easy way to get rid of them ... it's built into the Pivot Table Wizard. When you're in the layout view, simply double click on one of Row elements, and you should see the following box pop up. Simply click the radio button to none, then OK, and you're done. No subtotals.

Quick Tip: The AutoFill function, the small black grab handle on the lower right hand side of the active cell, can save you a lot of time. You already know that you can grab it and drag in any direction to copy a cell's contents and formatting to adjacent cells. However, there is a very cool shortcut if the column to the right is already populated. If this is the case, format the cell at the top of your target column as desired, then double-click on the AutoFill handle. Your cell will get copied as far down as the column to the right has continguous cells. This also works for standard series. If you have an odd series, like 1-3-5-7 etc, then, you can enter the first 2 values, highlight, and then double-click the AutoFill handle. I'll write up a tip on this in the near future, but thought it was interesting enough to share.

Quick Tip: If you have Adobe Acrobat and a PDF that was created from the original Word document, you can use the Save As function to convert the PDF back into a Word document for easier manipulation and edits.

Quick Tip: Have you ever received a .DOCX, .PPTX, or .XLSX documents and had no idea what they were or how to open them. These documents were created in the 2007 versions of Office and not openable with the 2003 or earlier versions without a compatability pack from Microsoft. However, you can easily go to the Microsoft page and download the compatability pack for free. This may only pose a problem if you don't have admin privileges to install files.

Quick Tip: If you need to select a large number of rows quickly, you can combine 2 shortcuts. CTRL + SHIFT + Arrow lets you select a continuous column of data. SHIFT + Spacebar, changes that selection into full rows, which you can then delete or format at your leisure. CTRL + Spacebar does the same thing for columns.

Quick Tip: In PowerPoint, if you want to resize an autoshape, but you want to keep the same size ratio, hold down SHIFT and then drag one corner to the size you want. This also works when you're adding a new shape, for example a circle. Hold down SHIFT while you describe the size and it'll will stay a perfect circle.

Quick Tip: If you need to paste a section of an Excel workbook as a graphic, you can do this in one of two ways. The first way is to highlight the cells you want to copy, Ctrl C to copy, and then in your target application, go Edit: Paste Special and the select Paste As Picture. Alternatively, you can hold SHIFT and then go the Edit menu, where you will see a Copy Picture option. Choose the As shown on screen and Bitmap options. I found this one in PC Magazine.

Quick Tip: SHIFT + F11 will insert a new worksheet in Excel.

Quick Tip: Do you find yourself always searching for the same folder (maybe a network folder) or application (Character Map or Calculator)? If you want to put a shortcut right on your desktop, find the target object, then right-click on it. From the menu bar, select Send To, then Desktop (create shortcut) and a new shortcut will be created.

Quick Tip: Need a quick graphical view of your data? Highlight the data series, click the Chart Wizard button, select a chart type, and then click on the "Press and Hold to View Sample" button to preview the chart. Click Cancel to exit the chart wizard.

Quick Tip: If you need to take a screencapture, you can use PrtScn (above your Backspace key) to take a snapshot of the entire desktop. Better yet, use ALT + PrtScn to take a screencapture of just the active window. Paste into Word, PowerPoint, or MS-Paint to manipulate further.

Quick Tip: Need to archive important e-mails and store them with other related files? If you have Adobe Acrobat, you can select the Print: Print as PDF option, which will create a PDF file that you can rename and store anywhere you want. If you don't have Adobe Acrobat, Microsoft Vista has some Document Writer options from the print menu or you can download a free reader like Cute PDF.

Quick Tip: Holding down CTRL while using the mousewheel will zoom you in and out of just about any document. This works with Word, Excel, PowerPoint, WordPad, web pages and PDFs. It will not work with .TXT documents.

Quick Tip: In Excel, CTRL+1 brings up the format cells menu. It's a much faster way of getting to this than Main Menu: Format: Cells

Quick Tip: Did you know that SUMIF and COUNTIF work with inequalities. If I have values 1, 2, 3, and 4 in cells A1:A4, I can use =COUNTIF(A1:A4,"<3") to return a value of 2 and =SUMIF(A1:A4,"<3",A1:A4) to return a value of 3. This also works with dates, which would look like this: =COUNTIF(A1:A20,"<7/20/2008"). Your inquality has to be encased in quotes in order for this to work. If you forget the quotes, you'll return a value of 0.