When using Microsoft Excel in your law office, you need to be careful about keeping the information clean, consistent and searchable, according to Monica Sandler, a learning and operations consultant and Microsoft certified trainer.
“If you are going to lay out a nice, clean data table, you can’t put garbage information into a field,” she says, adding that each column represents a field of information that you need to keep separate, searchable and sortable.
Each individual piece of information needs to be contained in its own column. When you think about planning your data, Sandler says you need to keep in mind your primary key fields, such as employee identity numbers, names and their locations within your firm.
“If you have a column intended to be a date, then you need to enter into it a full date. If you are going to have a year, that needs to be (in) a separate column. The data in a given column has to be consistent.”
If data isn’t entered properly, Excel will eliminate it as text when you try to do a search of your spreadsheet.
“Your results are going to be incomplete and you may not know it. That will really mess you up,” she says.
Other examples of bad data entry include having empty columns, gaps or too much data in one field.
How to properly plan and use data fields in Excel
In planning your fields, each column in your table will represent one field of data to be tracked. Each row represents one record and there should only be one table per worksheet (or tab) in a workbook, according to Sandler.
In laying out the table, enter the labels of each column in row one. Then format these labels so that Excel recognizes them as being separate from the actual data—for example, bold, italic and/or underlined, or even shaded.
Sandler says at that point you need to keep in mind that sorting only works from left to right. That means you will have to plan the order of your fields accordingly. If some records have a certain piece of information and others lack that information, there must be a field for it in order to track it. It is okay for a record to have an empty field.
If the data has any sort of ‘family’ or ‘group level’ naming, include that as one field.
Add the data
Your table is now ready for data using basic data entry skills. Sandler suggests using the right arrow key or to using Tab to move across a row. Shift+Tab can be used to move backwards.
“Consistency is very important,” says Sandler, adding that dates should all be formatted the same way, as should references to states, months or any other information that might be repeated in many records.
Some helpful key strokes include:
- Ctrl+; (which inserts the current date, and)
- Ctrl+’ (which copies the contents of the cell immediately above the active cell.)
Some helpful formatting tips
Sandler suggests using the Alignment group from the Home ribbon as a white space tool.
Another useful tool is freeze pane, which allows you to freeze the row where the labels are, making it easy to never lose track of which data is which. Keep control over this tool by using the drag n’ drop method, as follows:
- Balance on the small bar at the top of the vertical scroll bar.
- Click and drag it down the spreadsheet and drop it underneath the row of field labels.
- Drop down the Windows menu and select Freeze panes.
Getting the answers
Filters provide one of the fastest and easiest manipulations to work with in a table of data, according to Sandler. Click on the cell containing the first field label, drop down the Data Menu, Filters, and select Auto Filter. This should apply small drop-down arrows to each field.
Filters allow your data to be searched, sorted and queried based on existing data and some basic Boolean parameters. They can be applied to more than one field at a time; the first being the first group, then the second filter being applied within the first and so on, narrowing down your results.
Your filter results can still be sorted any way you desire and will only print the results, not the whole table, says Sandler.
Subtotals are another useful tool, especially when used with very large tables. This feature allows you to create sub-sums, averages, counts and other functions, so it is useful in dealing with straight text data.
To use this tool, turn off AutoFilters if they are turned on. Then sort the table by the column to be referenced. Select >Data>Subtotals.
Your decisions are to ask “For each change in (what group) do something for me (use which function) and show me that answer in (which column.)”
Generally leave the bottom check mark defaults in place and click OK. Your table is now broken out and has extra rows. Notice the familiar expand and collapse (+/-) signs.
One important thing to know is that in order to make changes or to remove your subtotals, the active cell must be somewhere in the data and not off to the side somewhere.