3 time-saving tips for Microsoft Excel
1. Setting up a time sheet
All MS Excel versions have a Time Card template with the basics. If you need to base the time sheet on check-in and check-out times, you may want to develop formulas with total hours for a day. It’s fairly simple from a math perspective.
For example, if Cell A1 had your start time and B1 had your end time, your formula would look like this: =(B1-A1)*24. The reason we multiply by 24 is that the result of B1-A1 will be expressed as a percentage of a 24-hour day. Multiplying it by 24 gives you the multiplier to use against an hourly rate to come up with total pay.
Tip: Use the shortcut Ctrl+: (Ctrl+Shift+;) to “punch in” and “punch out.” It will instantly put the current time into a cell. It is literally a time stamp.
2. Creating drop-down lists
You can accomplish this a couple of ways.
If you have a specific set of choices you’d like work sheet users to choose from, and you want it restrictive (only these values), use the Data Validation tool (on the Data tab, Data tools group). Choose the List selection from the Allow: field. From here, you can type your values separated by a comma (like Yes, No, NA) or refer to a cell range or named range for your choices.
To help users, complete the Input Message and Error Alert tabs, too. Alternatively, if you just want users to select from entries already entered in the column, all they have to do is right-click and choose Pick from Drop-down List.
3. Navigate anywhere
Proficient Word users probably know about Bookmarks. Bookmarks make it easy to return to certain locations in a document without scrolling or navigating with multiple Find Next actions. You can create a bookmark in Excel with a Named Range.
For example, if you use formulas on multiple work sheets to arrive at “net profit,” name the cell containing the final number Net Profit. Then, by clicking the drop-down arrow in the Name Box (the little box to the left of the Formula Bar), you can quickly navigate there by selecting Net Profit from the list anywhere in the workbook.
