Microsoft Excel can be a very powerful tool for managing and analyzing data. In this section the Dimagi Field Staff members have provided some helpful tutorials and tools

  1. Excel Fundamentals and Shortcuts
  2. Some Common Functions in Excel
  3. Directory Mapping Macro
  4. Sorting and Auto Filters
  5. Using Lookups (Vlookup and Hlookup)
  6. Excel Gantt Chart
  7. Adding a horiztonal line to excel charts (target values)

 

Quick Excel Hints and Tips

Time Calculations from form/case export

This is particularly useful for calculating the length of time it took to complete a form.  The form submission export contains the "timeEND" and "timeSTART" values, but not in a format that's easy to use for calculations. To change the format of those two values use this excel formula:

For the time (hh:mm:ss) portion of the data, use this: 

=IF(ISERROR(VALUE(LEFT(cell_reference,10))),0,VALUE(MID(cell_reference,12,8)))

For the date (mm/dd/yyyy) portion of the data, use this: 

=IFERROR(VALUE(LEFT(cell_reference,10)),"-")

If you create an additional column next to the "form end time" column, you can put this formula into the first cell, and then copy it down the entire range. 

Serialized Dates between Mac and Windows

Excel stores dates as sequential numbers that are called serial values (days since a certain starting date). Note that there are two different starting dates that might be used, one for Windows (Jan 1st, 1900) and one for Mac (Jan 2nd, 1904). You may experience difficulty if you are uploading dates between these Operating Systems. 

For more information about changing between the date systems and correcting shifted dates, see http://support.microsoft.com/kb/180162

Hints for Working with CSV

Keeping All Digits/Avoiding Rounding Errors

The maximum number of digits you can have for a number in Excel is 15. When you convert csv to xls if you are importing a number that is greater than 15 digits Excel will round the number 15 digits. In order to work around this, you have to tell Excel to mark the bank account column as text instead. You can do this in the Text Import Wizard by setting the Column Data Format to Text.

Support for Unicode 

There are known issues for Excel support of Unicode characters. If you are finding that Excel replaces your text with strange characters make sure you are following the directions here: https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0