Excel and Movember Moustaches

Recently, my daughter, Sarah, broke her foot, and has had two surgeries, with orthopaedic specialists trying to put all the pieces back together. I stayed with her for a few days after the latest surgery, to help her out.

One night, just as I was dozing off, I got a message on my iPhone. It was my daughter, texting from the next room.

"Are you still awake?" "Yes, what do you need?" "I need help with Excel."

That made me laugh, and I went in to see what help she needed.

Event Planning with Excel

Sarah is the event planner

Continue reading Excel and Movember Moustaches

Create a Line Column Chart on 2 Axes in Excel 2010

When you create a chart in Excel 2010, you can select a chart type on the Ribbon's Insert tab.

 

For some charts, instead of selecting one type, you might like to combine two different chart types, like the line-column chart shown below.

The Old Chart Wizard

In Excel 2003 and earlier versions, there were combination chart types available in the Chart Wizard. You could click on one of those, to quickly create your combination chart.

In Excel 2010 and Excel 2007, these combination charts, such as Line-Column and Line-Column on 2 Axes, aren't available in

Continue reading Create a Line Column Chart on 2 Axes in Excel 2010

Restrict Date Entries with Data Validation

With Excel's data validation, you can restrict the dates that can be entered on a worksheet. For example, you could specify start and end dates on the worksheet, and only dates within that range can be entered.

In the screen shot below, start and end dates are entered in column E, and dates in column B must be within that date range.

Set Up the Data Validation

After entering the start and end dates on the worksheet, follow these steps to set up the data validation:

Select the cells where the data validation will be applied – cells B2:B6

Continue reading Restrict Date Entries with Data Validation

Error 1004 When Pasting Filtered Data

It should have been a simple task in Excel VBA – copy a filtered range, and paste it into a new workbook. How many times have you written code to do that, and it always runs without problems?

However, last week a client sent me a file where that simple code wasn't working. While copying and pasting the filtered range, an error message kept popping up:

Run-time error '1004': Paste method of Worksheet class failed.

Look For the Obvious Problems

I figured there was some simple and obvious reason for the error, and went through the code, looking for

Continue reading Error 1004 When Pasting Filtered Data

Spreadsheet Day 2012

Do you have your party plans finalized? Remember, tomorrow, October 17th, is Spreadsheet Day, in honour of the date that VisiCalc was first shipped.

Last year, the theme was spreadsheets for students, and I posted a student time tracker in which you can plan your projects and track your class and lab hours.

 

In 2010, I posted my top 5 Excel tips, that I had seen posted on Excel blogs over the previous year. One of those tips was Jon Peltier's tutorial on making vertical bullet graphs.

 

Top 5 Excel Tips for 2012

This year, I'm going back

Continue reading Spreadsheet Day 2012

Find Text With INDEX and MATCH

Is there a harder working team in Excel, than the reliable duo of INDEX and MATCH? These functions work beautifully together, with MATCH identifying the location of an item, and INDEX pulling the results out from the murky depths of data.

Last week, Jodie asked if I could help with a problem, and INDEX and MATCH came to the rescue again.

Find the Text in a String

Jodie sent me a picture of her worksheet, with text strings in column A and codes in column D. Each text string contained one of the codes, and Jodie wanted that code to

Continue reading Find Text With INDEX and MATCH

Make All Queries Replicable in Access

Recently, I've been making changes to an Access database that someone else built. In most cases, that's enough of a challenge, but this database has an extra hurdle – it's set up for replication.

One copy of the database is set up as a Design Master, and replicas are made from that copy. Everyone can make changes and additions in their copy, then all the copies are synchronized, to pull all the data together.

I hit a snag, right near the end of the development phase, so I'm posting this solution, in case it helps someone else -- or me,

Continue reading Make All Queries Replicable in Access

Midnight Times Missing in Excel Worksheet

Last week, I was working on a client's time sheet file, and noticed something strange. The worksheet was used to record shift start and end times, and I was testing the calculations, to make sure that everything was working correctly.

Shifts that run past midnight can cause problems, so I tested that scenario, and I also tested shifts that started at midnight. I wanted to be sure that everyone would get all the pay they were entitled to!

In column D, I calculated the hours worked, by subtracting the start time from the end time, and adding 1 to the

Continue reading Midnight Times Missing in Excel Worksheet

Conditional Formatting in Excel 2010

If you're upgrading to Excel 2010, you've probably noticed that the Conditional Formatting feature has changed quite a bit. Now you can apply more than 3 rules, and there are fancy features like data bars and icon sets.

Intro to Conditional Formatting

I've updated my Intro to Conditional Formatting page, to show Excel 2010 instructions, and made a new video to show the steps. If you're looking for Excel 2003 instructions, they've been moved to this page: Excel 2003 instructions.

Watch the Excel 2010 Conditional Formatting Video

To see the steps for creating two conditional formatting rules in Excel

Continue reading Conditional Formatting in Excel 2010