Excel tips

Whether you are a mature Excel user who wants to explore the application’s powerful decision making tools or an Excel novice wishing to improve productivity in daily numerical and presentation tasks, “Excel Tips” is here to give you the answers you need. Written by professionals who use Excel for taking decisions, planning resources, automating routine number – crunching work or just preparing reports in a presentable fashion. 

Question: How are dates dealt with in Excel? How easily can one subtract between dates to find the number of calendar days or working dates?

Excel Tip: Excel follows a simple numerical approach to dates: It considers 1/1/1900 as being equivalent to number 1 and counts serially all subsequent dates, taking into account, of course, leap years etc. If, for instance, you type 15/1/1900 and give the entry a non-date numerical format (e.g. by clicking the comma style on the formatting toolbar), you will see that Excel gives the number 15. When, therefore, you ask Excel to subtract one date from another, it simply subtracts one integer from another and gives the result. You then change the formatting of the result from a date to a number.

If you want to have the number of working days rather than calendar ones between two dates, you will need the NETWORKDAYS function. This is offered when you choose the Tools – Add-Ins… command and then select the Analysis ToolPak box. The function is based on a 5-day working week.

 In the following example, we calculate the number of working days between the 20^th of December 2006 and the 20^th of January 2007 (cells E14 and E15 respectively), taking into account the 3 holidays falling in this date-bracket (which we enter in cells G14:G16). The function in cell E17 is NETWORKDAYS(E14;E15;G14:G16):

 If you want to disregard the holidays, you just omit the range G14:G16 from the function.

Question: I have to type my company’s 5 departments as headers in tables quite often. Can I automate the entry?

Excel Tip: You may, of course, use a macro to do this, but there is an easier way by using the command Tools Options Custom Lists.

1. You first type the 5 departments, either horizontally or vertically, in the order you would like to present them.
2. Select the 5 cells
3. Choose the Tools Options Custom Lists command
4. Click on Import

Having stored the entries in the preferred order, it is a matter of entering just any one of them, and then applying the Auto Fill technique:

1. Type Accounting.
2. Position your cursor (fill handle) on the bottom right corner where you see a tiny black square.
3. When a black cross appears, click, drag towards any direction for another 4 cells and then drop.

 Question: How can I open simultaneously certain files which I frequently use together?

Excel Tip: The general technique to open 2 or more files simultaneously is to first select them within the File Open command and then click Open. To select them use the SHIFT key (or drag the mouse around them) if they are consecutive on the list, or the CTRL key if they are non- consecutive.

However, if the files you want to open are usually dealt with together (for example a set of files comprising a budget report), then the best way is to utilize the File Save Workspace command. You make sure that the files comprising the set (and only these) are open in memory, then select the File Save Workspace command and replace the suggested name resume with your own. What this new file really does is to provide a shortcut to all the files comprising the workspace – hence its small size. Next time you want to use the same files, just open the newly created file rather than its individual components. Note that the file thus created has an XLW extension, unlike the usual XLS.

Back..