Category Archives for "Excel 2007"

Weirdly popular post: How to put multiple lines into cells in Microsoft Excel

All of a sudden (and in addition to a surge in site traffic generally), I’m seeing an awful lot of people visiting my post, How to put multiple lines into cells in Microsoft Excel. There must be a lot of people wanting to do this:

A mailing address typed into Excel

Or this:

Text in an Excel cell with line wrap turned on

Hey, I’m always happy to help. Click here for the full tutorial.

2 Reader Question: Cannot select single cell, row, or column

One reader wrote in recently that she was having a problem selecting cells in Microsoft Excel:

When in a spreadsheet and I click on a line it selects at least four lines.

In other words, she couldn’t select a single cell or just one row in the spreadsheet. It was as if her mouse cursor had a mind of its own!

Well, this was certainly a new one for me. I personally had never run across that particular problem, so I had no solution off the top of my head.

So what did I do? I went to Microsoft Answers (http://answers.microsoft.com) and did a little search. And not one but two possible solutions presented themselves.

Keep reading →

1 Weekly Roundup: Popular Word fixes, Excel row headers, and Office for iPad

Now that it’s past the annual holiday season here in the US (Santa brought me a way-big monitor!) it’s back in the saddle again for the Weekly Roundup. This week: Microsoft Office blog does its own list of most popular posts (including a couple of issues that continually plague legal Office users), a quick-and-dirty Excel tutorial on printing title rows, and an exciting rumor for iPad users.

Keep reading →

Weekly Roundup: More Word, Excel and Outlook Tips

This week’s Roundup of the reading file is an embarrassment of riches from the usual suspects: TechRepublic’s take on the most important Microsoft Word skills, how to put time values into Microsoft Excel, Vivian Manning tackles Microsoft Word’s mail merge feature, making it easier to switch between Word documents, and how to share your Microsoft Outlook calendar. Click the “Read More” link for the details. Keep reading →

[Insanely] Popular Post: Printing Those Monster Excel Sheets

Because I’m always trying to make sure I’m posting tutorials that help as many readers as possible, I regularly check out the statistics on what current posts are getting the most eyeballs (so I can do “more like that“). Sometimes, the stats surprise me.

For instance, who’d have guessed that a post on how to print large Excel spreadsheets would be the #1 most popular post on this blog? Not me.

But it is. And don’t get me wrong: I’m glad it’s helping so many people, especially considering how much work went into it.

In fact, at the time I originally posted it six months ago, I had done a video tutorial to go with the illustrations and text. Unfortunately, I was still scaling the learning curve on my newly-purchased screencasting software, and since I couldn’t figure out how to get rid of the irritating background whine in the the audio portion of the video, I set the video aside and published the post without it.

Now, I’ve gone back and fixed the original video showing the 5 steps to formatting a large Excel spreadsheet for printing, and I’ve added it to the post. So if printing in Excel is a mystery to you, click here to check out the post and the new companion video. (There’s even a downloadable transcript of the video in case the narration’s not 100% clear — I had to talk a little faster than normal in some spots when re-recording the narration!)

Guest Post @ Attorney at Work: Four Microsoft Office Settings to Tweak

The editors at Attorney at Work reached out to me for some quick tech tips for their blog this week, and I was happy to oblige. Ranging across the most popular Microsoft Office suite applications, this guest post will show you how to:

  1. Set up your Status Bar to maximize its usefulness in every Microsoft Office application
  2. Improve the full-justification of text in Microsoft Word
  3. Make sure your Microsoft Excel sheets auto-calculate
  4. Start your Microsoft Outlook each day in the folder of your choice: Inbox, Calendar, Tasks, or even the Outlook Today overview

Click here to read these four useful tips.

4 Reader Question: Calculate difference between two dates in Microsoft Excel

A reader contacted me recently with a deceptively simple Microsoft Excel question: “How do I calculate the difference between two dates?”

I say “deceptively simple” because the answer depends upon the context, namely, whether the two dates being compared are actually embedded in cells within the Microsoft Excel spreadsheet.

Keep reading →

1 Weekly Roundup: A neat Excel trick, customize Show/Hide, discounted Outlook tools

For this week’s Roundup: how to put zeroes in otherwise blank cells in Excel (and not the long way, either), how to pick and choose which formatting marks Word shows you with Show/Hide, and a heads-up on some hefty discounts on several Outlook plug-ins.

Keep reading →

1 Summarizing Excel data with Pivot Tables

If you’ve ever been presented with an Excel spreadsheet with a gosh-awful number of rows and/or columns in it and assigned the task of making sense of all those numbers (grouping, summarizing, or making other calculations), you need to learn about Pivot Tables.

Okay, people, I hear yawning out there! Seriously, this is a good skill to have in your back pocket, even if you only work with Excel occasionally, because it saves so much time. So to motivate you properly, here’s a fun little YouTube introduction to the whys behind Pivot Tables:

Basically, a Pivot Table is a way to summarize columns and rows in a meaningful way. Instead of your having to manipulate rows and rows of data by hand (which, depending on the size of the spreadsheet, could take hours), you can select the data to be summarized, go to the Insert tab, click Pivot Table, and tell Excel how you want those rows summarized.

For example, say your client is involved in an employment discrimination suit. The employer has produced a very large spreadsheet showing all the time entries recorded, including this information:

  • Timekeeper initials
  • Date worked
  • Hours worked
  • Work code
  • Description of work performed

If you’re being asked to figure out how many hours timekeeper CAL (the plaintiff) clocked in Word Code 01 during the month of June, you don’t want to have to manually add those hours. Sure, you could sort the rows and put in a summary row, but even that’s not necessary with Pivot Tables.

A pivot table allows you to take a spreadsheet that looks like this (times several hundred or thousand rows);

And turn it into a summary table that looks like this:

And it just takes a few mouse clicks. Let Excel do all the work for you!

Here’s a video demonstration:

[To view this full-screen, click the button in the lower right-hand corner]

Where could you put this trick to use? Let me know in the comments below.

7 Customizing the Quick Access Toolbar

Want one-click access to the commands you use most in the ribbon versions of Microsoft Office? Then you need to be taking full advantage of the Quick Access Toolbar!

The Quick Access Toolbar really lives up to its name: it provides one-click access to virtually any command you want. All you have to do is customize it.

And one of the great things about the Quick Access Toolbar (or QAT) is that it’s virtually the same throughout Microsoft Office. Sure, the commands vary according to the application, but the way you update it is the same across the Office Suite.

Here are two ways to add your favorite commands to the QAT:

What commands would you want on your QAT?

27 Printing those monster Excel sheets

My friend Karen has issues.  No, I’m not talking about those kinds of issues.  She’s got issues with Microsoft Excel.

Every time her boss gives her one of those monster Microsoft Excel spreadsheets (the kind that span 10 pages across and have 20,000 rows of data) and says, “Print this,” she panics.  And then she comes to my desk and begs me to print it for her.

I can’t say I blame her.  Unless you’ve worked with Microsoft Excel a fair bit, the prospect of formatting something that large for printing is pretty daunting.  (I always felt the same way about Lotus 1-2-3 for DOS back in its heyday.  Yes, I am that old.)

I promised her I’d break this process down for her so, in case I’m on vacation one day when she really, really needs something printed now, she’ll know how to do it herself.

Keep reading →

7 Creating a custom timeline in Excel

Recently, a fellow reader, Jessica from Miami, asked if I would help her figure out a way to create an event timeline in a format her boss is partial to:

Example of a timeline created in Microsoft Excel She had tried to find templates online, but nothing really seemed geared to a legal context.

I tried creating a solution in Word, but it was less than satisfactory.  So, given that Jessica was pretty comfortable with Excel, I developed a template for her there.

Changing the orientation of text within cells (vertical, horizontal, or diagonal, as in the example above) is actually pretty easy — here, I’ll show you:

(To view in full-screen mode, click the button in the lower right-hand corner.)

There’s other formatting done here too — the cells are wrapped (the Wrap Text checkbox above), I shifted the vertical alignment to Bottom, and in some cases, to get the middle cell to look more “centered,” I added a hard return before the text (with ALT-ENTER).  There’s a fair bit of eyeballing that has to be done to get it to look right, and it’s all a judgment call according to your personal preference.

What uses could you find for this trick?  Let me know in the comments below.

(P.S.: Jessica seemed to be pretty happy with her new template last I heard!)

15 Customizing the Status Bar

There’s a whole host of ways you can make the various Microsoft Office applications easier to use. In fact, most users don’t take full advantage of the options for customizing these applications to make the Office suite work better for them.

Today, we’re going to talk about one of the easiest customizations: the Status Bar. Look at the bottom of any Office application and you’ll see a bar just above the Windows Taskbar at the bottom (like this example from Word 2007):

Status Bar from Microsoft Word

(If you need to see the above a bit bigger, click on it for a full-sized version.  Go on — I’ll wait here.)

Most users don’t know they can change the information listed on the task bar in any Office application (except Outlook, unfortunately). And it’s really easy:

1) Right-click your mouse anywhere on the status bar.

2) Select the option(s) you want (check marks on this example from Word 2007 indicate the option is already selected and showing up on the Status Bar):

Customize Status Bar right-click menu from Microsoft Word

I recommend, for example, always turning on the Track Changes indicator, and I personally think the Word Count is a handy piece of information to have.  Feel free to experiment with adding or deleting features — you won’t mess up your document!

3) Once you’ve made your choices, click elsewhere on the screen to close the Customize Status Bar menu and save your changes.

That’s it! (That may be the easiest Word task you’ll do all day!)

Now, why is this important? Here are some scenarios to consider:

1) Someone’s sent you a document to review/revise and left Track Changes on, so when you start typing, Word starts redlining the document. With the status bar set to show the status of Track Changes, you can simply click on that section once to turn it off. That’s much simpler (and faster) than going to the Review tab, dropping down the Track Changes menu, and turning it off there.

2) You’ve imported some text from WordPerfect and notice that the headers and footers mysteriously change mid-document. Why? The status bar gives you a clue: the section numbers at the left keep changing. (Text imported from WordPerfect often embeds random section breaks into a document, which can affect the headers and footers.) How much time would you have otherwise spent trying to troubleshoot that problem?

3) Ever wanted to get a quick sum or count of highlighted cells in Excel without creating a formula? Change the status bar to show Count and Sum. You can also get quick calculations of Averages, Minimums and Maximums in the status bar.

So, what items would you want to see in the status bar? Tell me about ’em in the comments below.

59 How to put multiple lines into cells in Microsoft Excel

If you use Microsoft Excel to organize data (say, a list of documents being produced), you may have run across The Cell That’s Too Small For Its Data.  You know, you’ve got a bunch of stuff typed into a cell (not because you’re rambling, but because you need all that information, dang it), and it just breaks out of the borders of the cell and keeps on going:

Text in an Excel cell not wrapped

And if that’s not annoying enough, if you have to type something into the cell to the right, then you’ve just cut off the last part of that other cell:

Text in an Excel cell that's not wrapped and is cut off

What you want to be able to do is have the information in the first cell wrap so it appears on multiple lines within that cell.  Right?

Here are a couple of different tricks to try:

Wrap text. If it really doesn’t matter where the line wraps (as long as everything stays within the same cell), then the thing to do is format the cell so that the text wraps automatically.  Although the different versions of Excel (from 2002 through 2013) have various buttons and commands to do this, the one foolproof method that works in all versions is this:

  • Right-click your mouse inside the cell.
  • You’ll see a menu that looks like this:

 

Excel right-click menu with "Format Cell" selected

  • Once you have the Format Cells dialog box open, go to the Alignment tab and check the “Wrap Text” box:

Excel Format Cells Dialog | Alignment Tab

  • The text in that cell will now wrap automatically.

Text in an Excel cell with line wrap turned on

In the Ribbon-based versions of Word, this is a one-click operation. Just go to the Home tab and click on Wrap Text:

excel-format-cells-align-wrap-ribbon

Good news: You can apply this formatting to more than one cell at a time.  You can select multiple cells by holding down the CNTRL key while clicking on them, select entire columns or rows by clicking on their headers (the “A, B, C” on top of columns or the “1, 2, 3” to the left of rows), or even select the entire spreadsheet by clicking on the upper-left-hand corner (where the A and 1 meet).  Once you’ve selected all your cells, then just follow the steps above.

Inserting a line break within a cell. But what if you want to control exactly where the line breaks (say, you want an address to appear like this):

A mailing address typed into Excel

Even easier!  After you type each line, just press ALT-ENTER on your keyboard to insert a hard return.

Controlling vertical alignment. By default, data in cells is aligned at the bottom.  This can create some readability problems if some of your cells have multiple lines:

Text in an Excel cell with line wrap turned on

If you have a particular preference as to whether all the data across the row lines up at the top, the bottom, or in the center, select all the cells you want to re-align, then right-click to get the menu (as we saw above), choose Format Cells, and go back to the Alignment tab.

Format Cells | Alignment Tab | Vertical Alignment

See that drop-down menu that says “Vertical” (above)?  That allows you to change the vertical alignment within the cells you have selected.  If you want everything to line up across the top, then choose “Top.”  If you want everything centered, choose “Center.”  You get the idea, right?

What cell alignment issues do you run into in Microsoft Excel? Let me know in the comments below.

Update: Copying cells that have hard returns

Down in the comments, there’s a bit of a controversy about whether you can successfully copy a cell into which you’ve embedded hard returns to another cell. Here’s a quick guide to both “how to” and “how NOT to”:

Working with Word 2007 & 2010 documents in earlier versions

It’s bound to happen sooner or later.  Someone — co-counsel, client, whoever — sends you a document with a .docx extension.  You try to open it in Word 2003 or even 2002, but you get an error message.

Don’t fret — it’s an easier problem to solve than you think.

Keep reading →