#272 Automatic Separator Lines in Excel

This weeks tip will show you how you can automatically add a border line between groups of data. For example, using the data shown below you can automatically add a line between each date change by using Excel’s Conditional Formatting feature.

How It Works

1. With the range of cells you want to format selected, go to the Format menu and select Conditional Formatting…

2. In the Condition 1 box, set the drop-down to Formula Is then enter =$A2<>$A3

3. Click the Format… button to specify the type and color of border you want.

4. When you’re all done you should get something like this.

Excel 2010

It’s a bit different in Excel 2010 but just as useful. Check out the details here.

This is similar to one of my favorite tricks, which we’ve seen before, that uses conditional formatting to shade every other row.

#271 Add a Watermark to an Excel Worksheet

If you ever need to share a spreadsheet with others before it is “officially” done, it’s useful to add a DRAFT watermark to a worksheet. Or sometimes a sensitive document needs to be marked by adding a CONFIDENTIAL watermark.

Unfortunately, unlike Word, Excel doesn’t come with a built in way to add a watermark. But never fear, there is a pretty easy way to do it: just place a pre-built .PNG graphic file in the header of your Excel worksheet, and it displays as a watermark.

To help you on your way I’ve included a couple images for you to use below: (Follow the link then just do a right-click » Save as… to save it.)

Grab this “DRAFT” image to use as a watermark.

http://officeimg.vo.msecnd.net/en-us/files/003/316/AF101880439.PNG

…and/or this “CONFIDENTIAL” image.

http://officeimg.vo.msecnd.net/en-us/files/165/003/AF101880440.png

How to Add an Excel Watermark in Header

1. Start by opening the View menu then click Header and Footer.

2. In the Page Setup dialog box, click Custom Header or Custom Footer.

Note: This example inserts the watermark image in the Left section.

3. In the row of buttons in the Header or Footer dialog box, click Insert Picture Button image and then find the graphic you want to insert.

4. Now you should see &[Picture] in the header/footer area you chose.

When you’re done, take a look at your Print Preview and you should see your watermark.

Excel 2010

It’s a little bit different in Excel 2010 but just as easy. Check out the details here.

#270 Demystifying Excel Pivot Tables

For as long as pivot tables have been around, they’re probably one of Excel’s least understood, and therefore least-used features. I think most people are simply unclear how they work and often a bit intimidated by pivot tables.

If you can think of your data in terms of groups, values, and filters you can master pivot tables and harness their power to work much more efficiently. Let’s work through one simple example to illustrate.

Looking at our data, let’s consider three things: logical groupings, values, and filters.

1. Columns to group on:
Do you see any columns you may want to use for grouping? By product, By Region or By month are some logical groupings.

2. Values to be used in calculations:
Now what values would you want to do calculations on? No. of customers, Net Sales or Profit/Loss are likely candidates right?

3. Columns to filter by:
Finally, how would you like filter the data? By salesperson? By product? etc

Once you know the questions you want to ask of your data, it’s really quick to get the answers using a pivot table.

Scenario

Let’s say we want to use this data, to look at ours sales grouped by product and region with the ability to filter on each salesperson. How would you do all of this with basic formulas? Or would you give us and phone a friend for help? A pivot table can give you this result below in about 30 seconds.

Getting Started

1. Start by launching the Pivot Table & Pivot Chart Wizard from within the workbook containing your data.

2. Indicate that your data is in a “Microsoft Excel list or database” and that you want a “Pivot Table“

3. Specify the range of data you want to work with.

4. Finally tell the wizard where you want to put your new pivot table; on a new or existing worksheet.

Now you should see something similar to this:

Making the ‘Magic’ Happen

I think most people get stuck at this point. From here the trick is to drag a grouping to the row area, another grouping to the Column area, the values to the data area and, if you want, something to the page field area at the top.

Hopefully with this insight and some practice you’ll soon be impressing your boss with your pivot table wizardry!

Diving Deeper

Pivot Tables 101 from Microsoft
http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx

Great tutorial with practice files
http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

More complete, printable article on pivot tables: http://oreilly.com/lpt/a/6082

#269 Word AutoText

Do you have any phrases or blocks of text that you use over and over? For example, the same lengthy disclaimer for a report or maybe a closing signature block for letters. If so, you may be interested in using Word’s AutoText feature. Think of this as the electronic equivalent of a rubber stamp. Word comes with a set of built-in entries for you to use and of course you can create your own.

Insert Existing AutoText

1. Click in the document where you want to insert the AutoText entry.

2. On the Insert menu, go to AutoText, and then point to the type of entry you want to insert.

3. Click the name of the AutoText entry you want and Word “stamps” that text into your document.

Create New AutoText Entries

The pre-defined ones are OK but you can really save even more time is by setting up your own customized “stamps”. You can even include formatting and graphics in these AutoText entries.

1. Select the text or graphic you want to store as an AutoText entry.

To store paragraph formatting with the entry, include the paragraph mark (Paragraph mark ) in the selection.

2. On the Insert menu, point to AutoText, and then click New.

When Microsoft Word proposes a name for the AutoText entry, accept the name or type a new one.

NOTE: If you plan to insert the entry by using AutoComplete, make sure the name contains at least four characters because Word inserts an entry only after four characters have been typed. This lets you simply type the name of the AutoText entry and Word will automatically insert your “stamp” and you never need to touch the menus.

Using the example shown above, I could now just type the word “signatory” in any document and Word would “stamp” my entire, formatting signature block.

#268 Comparing Word Documents

Have you ever needed to compare two different versions of a Word document and then merge the differences into a single one? For example, if you ask someone else to help you write or edit a document, and now you need to compare his edited document to your original to see what changes they made.

By the way, it should be noted that A MUCH BETTER APPROACH is to put the document in a location where everyone can edit the same copy and skip this whole process altogether — but that’s a story for another day!

How It Works

1. Start by opening the first document to be compared in Word.

2. From the Tools menu, click Compare and Merge Documents.

3. Locate and select the file that you want to compare.

Use the Merge down arrow, control where to save the merged changes. Choose “Merge into new document” to keep original copies of both

4. Any changes made to the original document are indicated and you can use the Reviewing Toolbar to accept/reject the differences. (We’ve seen those reviewing marks before. )

How It Works: Word 2010

1. Open the documents you want to compare side by side.

2. On the Window menu, click Compare Side by Side with.

3. On the Compare Side by Side toolbar, do any of the following:

» If you want to scroll through the documents at the same time, click the Synchronous Scrolling Button

» If you want to reset the document windows to the positions they were in when you first started comparing documents, click Reset Window Position Button image.

4. Click Close Side by Side to stop comparing documents.

#267 Display Multiple Excel Worksheets Side-by-Side

Several people have asked about how to see multiple Excel worksheets side-by-side at the same time. Depending what you want to do, here a few tricks you can try to save yourself some toggling back and forth.

2 or More Worksheets of Different Workbooks

1. Open the workbooks you want to view.

2. On the Window menu, click Arrange.

3. Under Arrange, click the option you want.

2 or More Worksheets of the Same Workbook

1. From the Window menu, click New Window.

2. Switch to this new window and click on the sheet/tab you want to view.

3. From the Window menu, select Arrange and check the Windows of active workbook check box.

3. Then just, click the option you want and hit the OK button.

Voila! Two windows displaying different tabs of the same workbook.

Make Use of That Second Monitor

If you’re fortunate enough to have a second monitor, one trick you can try to see a different worksheet on each screen is a bit counter-intuitive. The steps are the same as above but before you do that UN-maximize your Excel application window and size it so it spans across both monitors.

You just need to use the Restore button from the top right corner of the Excel window (shown below) and then resize so it fills BOTH screens.

#266 Copy/Paste vs Inserting Images

I’m guessing that a pretty big percentage of images used in PowerPoint come from the web. And
I’m also guessing that most people may realize that most images on the web have been compressed in some way to provide a balance of quality & small file size.

What I don’t think many people realize is that when you copy & paste images directly from the web into PowerPoint that you lose much of that compression and wind up with a bigger file size than you need to have.

The reason is that whenever a picture is copied to the computer’s clipboard and then pasted into PowerPoint (or Word, etc) the picture is pasted as a bitmap—regardless of the file format of the original picture. Bitmap file sizes are much larger and have less detail which leaves you with a much larger Word or PowerPoint file than necessary, particularly if you have used multiple images this way.

So instead of copying & pasting directly from the web into your document, you should save them first and then Insert them via the Insert menu instead.

I did a quick test with the following results:

Method	File Size (kb)
Copy & Paste	2,027
Copy & Paste (Compressed)	440
Insert	300

Notice that even after using PowerPoint’s option for compressing the pasted images, the file size was still larger than the file using inserted images.

#265 If I only knew how to…

Although I always welcome your questions it’s been awhile since I’ve asked you directly. So here’s your chance to share the biggest challenge you’ve been facing lately by completing this sentence…

If only I knew how to __________________ I’d be happy.

For example:

Leave your answer as a comment below (go head it will only take a few seconds and it’s amazing what you can learn simply by asking.) While you’re there, how about taking a quick look at what others are wrestling with to see if you can ‘pay it forward’ by helping them out with a solution. (It’ll give you lot’s of good karma!)

Be sure to check the “Notify me of follow-up comments via e-mail” box if you’d like to get other comments in your in-box instead of having to repeatedly check the site to see if anyone has answered your question.

#264 Word Formatting Detective

If you’ve ever struggled with formatting a Word document, you might be interested in learning how to figure out exactly what formatting has been applied to any/all parts of your document.

The details available in the Reveal Formatting pane, located under the Formatting menu, can help figure out just why in the heck your text doesn’t look like you want it to.

SHORTCUT:
SHIFT + F1 is the quickest way to access this. In older versions you’ll have to click on the text you’re analyzing. In newer, versions it will analyze the selected text.

This handy little trick will give you lots of details about your text, such as the font, paragraph and line spacing, styles, and language. You can expand or collapse these details as needed to focus on the specific details you’re interested in.

Some of the things you can do while you’re poking around in there include:

Change Existing Formatting

Simply click any of the hyperlinked text (the stuff with a blue underline) to open the appropriate dialog box and change any settings you want.

Learn How It Got That Way:

To determine whether the formatting was manually applied or if it comes from a style, select the Distinguish style source check box.

Compare It To Another Selection:

If you check the Compare to another selection check box and then select another block of text Word will automatically tell you the differences between two.

#263 Word Ruler Shortcuts

If you ever feel like you’re spending more time on formatting your documents than you should, you should check out these shortcuts you can access by double-clicking in Word’s ruler.

Obviously these won’t work if you don’t have the ruler displayed. If you don’t see it you can turn it on by selecting Ruler from the View menu.

Page Setup

Double-click in the grey area of either ruler (horizontal or vertical) to a open the Page Setup dialog.

Paragraph Settings

Paragraph settings are accessible by double-clicking any of the indent markers located to the left of the horizontal ruler.

Table Properties

Double-click the Move Table Columns marker (the small squares you see when you’re in a table) on the horizontal ruler to display the Tables Properties dialog.