Contextures Blog - Page 123 of 137 - Excel tips and tutorials

Working With Dates In Excel

This week I’ve been working on date formulas, from very simple ones, to complex formulas that calculate workdays per month, based on start and end dates that can span several months.

Extract Information from a Date

Many times I need to pull a bit of information from a date, such as the year, month or weekday.

In the section below, I’ve listed the sample Excel formulas I would use, to calculate specific dates in Excel.

For all formulas, the date — December 29, 2008 — is in cell A2.

Date Calculation Formulas

Here are the formulas to extract information from a date in cell A2.

To Calculate	The Formula	The Result
Year	=YEAR(A2)	2008
Month Number	=MONTH(A2)	12
Month Name (short)	=TEXT(A2,”mmm”)	Dec
Month Name (long)	=TEXT(A2,”mmmm”)	December
Day of the month	=DAY(A2)	29
Weekday Number	=WEEKDAY(A2,1)	2
Weekday Name (short)	=TEXT(A2,”ddd”)	Mon
Weekday Name (long)	=TEXT(A2,”dddd”)	Monday
Year Month	=TEXT(A2,”yyyy mm”)	2008 12

Using Calculated Dates in Pivot Table

If I plan to create a pivot table from data that contains a date field, I usually calculate the year and month in the source data.

Then I can add those fields to the pivot table, instead of the individual dates.

Yes, the pivot table could automatically group the individual dates by year and month, but that can limit other functions in the pivot table.

For example:

if two pivot tables are based on the same data, grouping one pivot table by month would cause the other pivot table to also be grouped by month.
if a field is grouped, you cannot add calculated items to the pivot table

pivot table error message - cannot add a calculated item — pivot table error message – cannot add a calculated item

Video: Pivot Table Grouping Tips

This video shows how to group pivot table dates by month and years, and how to group text items manually.

There are written steps, and an Excel workbook, on the How to Group Pivot Table Data page, on my Contextures site.

Pivot Table Grouping

For more information on Excel pivot table grouping, go to the How to Group Pivot Table Data page, on my Contextures site.

There are examples for grouping dates, number and text fields. You’ll also see solutions for fixing pivot table grouping problems, such as the error message, “Cannot group that selection”

Avoiding Shared Workbooks in Excel

Occasionally a client asks me to create a shared workbook in Excel, so two or more employees can work in it at the same time. It sounds good in theory, but I always try to come up with a different solution.

There are many reasons for avoiding shared workbooks in Excel.

Note: See a newer version of this article: Shared Workbook Limits in Excel 2010

Continue reading “Avoiding Shared Workbooks in Excel”

David McRitchie’s Excel Pages

For many years, David McRitchie has been collecting and posting Excel information on his web site.

With over 200 pages, your can find the answer to almost any Excel question on David’s site.

There’s a list of Excel articles, and many links to other sites which have Excel tutorials, downloads and advanced Excel topics.

David’s interests have shifted over the past couple of years, and he’s now documenting Firefox features. You can find his Firefox Lessons in a new section on his web site.

Meeting for Lunch

Last week David was visiting relatives in Canada and we met for lunch on Friday. We had a great chat, and I learned quite a bit about his very interesting work history.

I had previously met David at a Microsoft MVP Summit in 2001. That was the first year I received the award, and David had been an Excel MVP since 1999.

It was good to see him again, and I hope he enjoys the rest of his visit.

Excel expert, and former Microsoft MVP, David McRitchie

=============================

Create Quick Equations in Word 2007

Word 2007 has a new Building Blocks feature that lets you quickly add items, such as cover pages, text boxes, watermarks and page numbers. If you’re writing a scientific document, you can also insert equations.

Insert an Equation

On the Ribbon, click the Insert tab.
In the Symbols group, click on Equation, then click the equation that you want to insert.

Save an Equation

After you insert an equation, you can modify it, then save it to use again later.

Click on the equation in the Word document, to activate it.
Click the arrow at the bottom right of the equation box, and click on Save as New Equation.

Enter the details for your equation, then click OK.
When you close Word, you’ll be prompted to save the changes to the Building Blocks template. Click Yes to save your changes.

Insert a Saved Equation

Saved equations are listed in the Building Blocks Organizer. Open it and select the item you want to insert.

On the Ribbon, click the Insert tab.
In the Text group, click on Quick Parts, then click Building Blocks Organizer

In the Building Block Organizer dialog box, click on the Gallery heading, to sort the list by type.
Scroll down to the Equations, and click on an equation to select it.

Click the Insert button.

Using Windows Live Writer

If you have a blog, I’d highly recommend that you write your articles in Windows Live Writer. It’s a free download, available on the Windows Live web site.

You’ll need a Windows Live account before you can use the program, so if you don’t have an account yet, it’ll take you a couple of minutes to set that up.

I’ve been using Live Writer for a couple of weeks and find it much quicker and easier than logging into my WordPress blog, and writing an article in the tiny window there.

Set Up a Blog

You can set up one blog, or several blogs, and publish to any of them from the same Windows Live Writer session.

It only takes a minute or two to set up a blog in Live Writer. Fill in the URL, name, password, and select from a few options.

Then, select a blog from the dropdown list, and start writing.

Write an Article

Live Writer has a nice big writing area, so it’s easier to see what you’re doing, and view more of the current article.

There’s a formatting toolbar, with most of the common tools. Align is on the Format menu though, instead of the toolbar.

A few more features that keep me using Live Writer:

Spelling mistakes are underlined, so you can correct them as you work.
From the sidebar, you can insert objects such as a table, map, or video.

For articles with pictures, I keep Windows Explorer open, and drag the images in from there. That’s much quicker than using the Upload feature in WordPress.
At the bottom of the article window you can set the categories or add new categories.
The Set publish date feature lets you schedule articles for publishing later.

Improvements

There are a couple of things that I’d like to see improved in the next version.

Tags — There’s an Insert Tags option, but I don’t see the tags in the published article on my web site.
Align — Add an Align button to the formatting toolbar.
Pictures — Don’t insert all the images as thumbnails. Maybe there’s somewhere to change that setting, but I haven’t found it yet.

Those are minor annoyances though, and the benefits far outweigh them.

Try It!

Whether you write a couple of articles a month or several per day, I think you’ll like Live Writer. If you do try, let me know what you think of it.
_________________________

Go To Special Sections of an Excel Worksheet

From what I’ve seen during visits to my business clients, Excel reports often have blank rows that separate the sections.

Those blank rows make a printed report easier to read, but they make extra work if you’re trying to work with the data!

One example of the extra work that blank rows can cause is shown below.

Adding a Formula Column

While working on a client’s Excel report, I needed a new formula in each row, to calculate the average price per order.

Here are the steps that I followed:

First, I entered this formula in cell E2, to calculate the average price per order:
- =C2/D2
Next, I selected cell E2, and formatted it as a number, with 2 decimal places
Then, I selected cell E2, and copied it down to the last row in the report.
- That was quicker than pasting the formula into each little section in a long report.

Formula Error in Blank Rows

Now all the blank rows have a #DIV/0! error in the new column, because Excel doesn’t like to divide by zero.

I didn’t want the formula in those rows, so I used Excel’s Go To feature to quickly clear the cells that contain errors. (steps in next section)

Excel Go To Special

Here are the steps to select all the cells with formula errors.

First, select column E, where the average order formula was added.
On the Excel Ribbon, click the Home tab.
Next, in the Editing group, click Find & Select
In the drop-down menu, click Go To Special.
- In Excel 2003 and earlier versions, click Edit►Go To, and click the Special button. The keyboard shortcut for Go To is F5.

Go To Special Dialog Box

When the Go To Special dialog box opens, follow these steps:

In the Select section, click the radio button for Formulas.
- You want to go to the cells that contain a formula, where the formula results in an error.
Under Formulas, remove all the check marks except for Errors
Next, click the OK button.

Go To Special Dialog Box - Formula Errors — Go To Special Dialog Box – Formula Errors

Clear Selected Error Cells

When you go back to the Excel worksheet, only the formula error cells are now selected.

To clear the selected cells, press the Delete key on your keyboard.

formula error cells selected on worksheet

Other Uses for Go To Special

As you can saw in the Go To Special dialog box above, there are many types of special cells you can select on a worksheet.

The video below shows one “Got To Special” technique that I use frequently – finding blank cells in a column, and filling them in.

For the written steps, and an Excel workbook, go to the Fill Blank Cells page on my Contextures site.

________________________________

Total a Filtered List in Excel

In Excel, you might have a long list of orders with a grand total at the end. If you filter the Region column, so the list only shows one region’s sales, you’d like the total to include only those items. Here’s how to total a filtered list in Excel.

Continue reading “Total a Filtered List in Excel”

I Need More Storage Space

Yes, I definitely need more space for all this office stuff. At least that was my first thought as I looked around my office on the weekend.

Even though I cleared out a few bookshelves recently, there’s just not enough room in here for all the remaining books, files, computers, printers, gadgets, project binders, and penguins.

So, I thought about a trip to Home Depot, to buy a shelving unit that would make better use of the corner space, and maybe hide some of the tangle of wiring.

Fortunately, I came to my senses and realized that the problem isn’t storage space. The problem is stuff.

Do I really need those notes from projects I worked on in 1995. (No, I’m not exaggerating.)
Why am I keeping those disks for CorelDraw 7? I haven’t installed it on my past 4 machines.
When was the last time I used a floppy disk? And why do I have all those boxes of them in the storage closet?

Fire Up the Shredder

This week I’ll be keeping the shredder busy, as I clear the file drawers and storage boxes of old documents. The old floppy disks and CDs with client information will have to stay for now, until I figure out a way to securely dispose of them.

Any ideas on how to get rid of them? My shredder is only designed for paper, so maybe I’ll have to buy one of those fancy new ones that eats anything.

In the meantime, if you need me, I’ll be under that pile of paper in the corner. And don’t worry, the penguins are safe.

Hidden Word Shortcuts

In Microsoft Word, recording a macro is just a double-click away.
Normally, to record a macro in Word, you’d click on the Tools menu, then click Macro, then click Record New Macro.

A much quicker way is to double-click on the REC box in the status bar, at the bottom of the Word window.

The Record Macro dialog box immediately opens, and you can begin recording.

Double-click the REC box again, to turn off the recorder.

Other Shortcuts

There are other double-click shortcuts in the status bar:

TRK: Toggle the Track Changes feature
EXT: Toggle the Extend Selection feature
OVR: Toggle the Overtype feature (does anyone use that?)
Page Number (or any area in the left end of the Status Bar): Open the Go To dialog box

Welcome Your Subscribers

If you use Feedburner to manage your blog’s email subscriptions, you can customize the activation letter that goes out to new subscribers. Instead of using the default address, subject line and email body, you can use your own greeting. I just discovered this feature, so if you got a boring, generic greeting from me, I apologize!

How To Customize the Email

In the list that appears below Email Subscriptions, click on Communication Preferences

In the Communication Preferences page you can modify your From address, the Subject line, and the Body text. There are instructions below each box to help you.

Even if you just add a few words of your own, like “please” and “thanks”, it will improve the default message.

You could also add the URL of your main site (it won’t be clickable), in case the subscriber hasn’t found that yet.

When you’re done, click the Save button, and log out of Feedburner.