|
|
Last week, we saw the steps for entering project start and stop times in Excel, by using keyboard shortcuts. Then, Excel formulas can calculate the total project time, based on the start and stop times.
One problem with using the keyboard shortcut to enter the current time is that it doesn't include the seconds in the time. All the times are entered as 00 seconds, as you can see in the screen shot below.
So, if you're keeping track of very short tasks, or need precision to the second, the keyboard shortcut for entering time won't help you.
Enter Current Time with Excel VBA
Instead of using the default shortcut for entering the current time, you can use Excel VBA to enter the time.
For example, you can select a cell on the worksheet, and run the following macro, to enter the current time in the active
Continue reading Track Time in Excel with VBA
If you're working on a project, you might need to keep track of the time that you spend on its tasks. Later, you can tell a client how much time you've spent on their project, and get paid for your work. Or, you can use the time data to see how much of your day is spent on productive tasks, and how much is spent Googling and tweeting.
Enter the Current Time
To quickly enter the current time in Excel, you can use the keyboard shortcut: Ctrl + Shift + ;
When you start a task, use the shortcut to enter the start time in one cell. When you finish working on a task, use the shortcut in an adjacent cell, to enter the end time.
In the screen shot below, the Task start times are entered in column B and the end times are entered in column C.
Continue reading Keep Track of Time in Excel
There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. You can click a button, and a sheet is created for each sales rep, with that person's orders.
There is another version of the file, and it creates a sheet for each sales rep name that is visible, after an AutoFilter has been applied.
How It Works
When you click the worksheet button, a macro runs. It sets up a list of sales rep names, then loops through that list, creating a worksheet for each name.
To get the orders onto each sheet, the macro uses an Advanced Filter, setting up a criteria range in cells L1:L2 on the worksheet.
In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.
Advanced Filter Criteria Problems
The filter works
Continue reading Filter Excel Data Onto Multiple Sheets
What happens when good Excel formulas go bad? A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010.
Even more mysterious, the calculations worked fine on some machines, but not others. So, what was the problem?
Hint: It wasn't one of the obvious solutions.
Check for Automatic Calculation
When someone tells you that formulas aren't calculating, it's probably because the Calculation setting has been changed to Manual, instead of Automatic. That can happen if the first workbook that you open in an Excel session was saved as Manual calculation. That setting affects all the other workbooks the you open during that session.
In this case, the workbook calculation was set to Automatic on all the machines – that's the first thing we checked. To check the setting, click the Formulas tab on the
Continue reading Excel Formulas Not Calculating
When I saw the signs in this shop window, I laughed, and snapped this picture.
How confused are your customers, if you need a sign that says, "This is the door", and another sign that points to the door handle?
Design Flaws
Later, I realized that it's not a customer problem – it's a design problem. A substantial number of people had trouble finding the handle, which looks more like a box. And I don't remember exactly what the front of the store looked like, but the door must have been hard to identify too.
How about your Excel workbooks – are they easy to understand and navigate? I'll admit that some of the spreadsheets I've built might have similar navigation problems. I've added arrows that point to data entry cells, and text boxes with user instructions. Not much different from that door!
Navigating a Workbook
What can
Continue reading Efficient Navigation in Excel Workbooks
To show instructions to users in your Excel files, you can add comments or text boxes with notes.
Another option is to put the notes in an Excel Userform, and add a Help button on each worksheet. The button can open the Help form to a specific page, and show the relevant Help information.
Download the Sample File
You can download the sample file from the Contextures website: http://www.contextures.com/excelfiles.html In the UserForms section, look for UF0014 - MultiPage Excel UserForm
Watch the Video
To see the steps for creating the UserForm and Help buttons, you can watch this Excel video tutorial.
Or watch the video on YouTube: Show Specific Info in Excel UserForm
__________
Continue reading Show Specific Info in Tabbed Excel UserForm
Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.
AutoFilter Search in Excel 2010
There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.
AutoFilter Search in Earlier Versions
In earlier versions of Excel, you can filter for text, but it's a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box
In Excel 2003, use the Custom option on the AutoFilter drop down.
Roger Govier's FastFilter
If you'd like to enter the AutoFilter criteria on
Continue reading Excel AutoFilter By Typing Criteria
In Excel 2007 and Excel 2010, you can use the new SUMIFS function to sum items using multiple criteria.
For example, sum the orders where an account status is Active, and the number of visits is greater than or equal to 10. You can type in the criteria, but it's better to use cell references, where possible.
I've updated the Excel SUM page on the Contextures website, to include the SUMIFS function example and video.
Watch the SUMIFS Video
To see the steps for creating a SUMIFS formula, and using cell reference, you can watch this short Excel video tutorial.
Or watch on YouTube: Excel SUMIFS Sum With Multiple Criteria
_____________
Continue reading Excel SUMIFS Sum With Multiple Criteria
|
|
Recent Comments