Recently I helped someone who was having trouble entering data in a new installation of Excel 2007. When he entered 100, the value was automatically changed to 1. If he typed 1000, the value immediately became 10. Very mysterious, and frustrating for the user.
It’s a Feature
What the user had discovered was a relatively unknown feature [...]

Filed under: Data Entry, Excel | No Comments »

In yesterday’s post I showed formulas you can use to pull information from a date in Excel. In some workbooks you’ll enter dates manually, but often you’d like a date to be calculated automatically.
Calculate the Current Date
To show the current date in a cell, use the TODAY function:
  =TODAY()
This date will update automatically when you [...]

Filed under: Excel, Programming, VBA | 1 Comment »

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.

Here are [...]

Filed under: Excel | 7 Comments »

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.
Maybe you’ve had success with shared workbooks, and I’d love to hear about it [...]

Filed under: Excel | 11 Comments »

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 [...]

Filed under: Browsers, Excel | No Comments »

Another week, another wave of Excel Twitters. It’s always interesting to see what people are up to, using Excel in ways it was never intended. At least one person is hitting the Save button, so that’s an improvement.
And I’m pretty sure I know that guy from Columbia, with the Excel questions in FaceBook. Not the [...]

Filed under: Excel, Twitter | No Comments »

Excel reports often have blank rows that separate the sections. In this example, I needed a new formula to calculate the average price per order. After entering the formula in cell E2, I copied it down to the last row in the report. That was quicker than pasting the formula into each little section in [...]

Filed under: Data Entry, Excel, Keyboard Shortcuts | 2 Comments »

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.
If you used the SUM function in the grand total cell, the result won’t change if [...]

Filed under: Excel, Functions | 2 Comments »

Another week in Twitterville, with the usual whining about Excel crashes and lost data. Here’s a tip — save your work in Excel every 10 minutes. Don’t blame Excel when you lose a day’s work because you never bothered to hit the Save button. Geez.
Oh, and Excel comes with a Help file — don’t be [...]

Filed under: Excel, Twitter | No Comments »

While working on client projects, I enter all my timesheet data in Excel because:

Excel’s usually open, so it’s the easiest program for me to use
that’s the way I’ve always done it
other important reasons that are long forgotten.

However, I create my invoices in Access, so I have to move the data from Excel to Access, usually [...]

Filed under: Access, Data Entry, Excel, Time Management | 8 Comments »