Plugged In: 5 features to help you Excel

When thinking of Excel, many think of numbers and formulas and begin to have nightmares about high school math. However, this program can be used for so much more than number crunching and complex data models. While Excel can seem overwhelming and scary, it is one of the most powerful tools filled with time-saving options for many everyday tasks. This article examines five key features that can create efficiencies.

Splitting and combining fields

Sometimes the data format given to us is not useful, for example a single field with a person’s full name when we need it split into separate fields for first and last name or vice versa. Rather than manually retyping this data, let Excel do the work for you.

The easiest method for splitting or combining fields is a tool called “Flash Fill” that was introduced in Excel 2013. This tool recognizes patterns in your data and makes the entire process a cinch. Simply go to an empty cell and enter an example of the desired data for that row, such as only the city or both first and last name into the cell. Repeat this for the next couple of rows, Excel will automatically detect the pattern and “flash fill” the remaining rows in that column to either split or combine the data.

For those with versions of Excel prior to 2013, the functionality still exists in different features. To split a single field into multiple fields, use the “Text to Columns” tool located on the Data tab. This feature is easy to use and will divide the data into columns to the right of the current field. Data will be split based upon the selected delimiter (the character separating the data). For example “city, state” is delimited with a comma while “first_name last_name” is delimited with a space.

Combining fields in older versions of Excel requires the use of a basic formula. Simply enter an = into the field and click first cell then type & “ “ & and click the next cell and press enter. This tells Excel that you want the cell to equal the first cell and a space (denoted by “ “) and the next column. To reproduce this in the remaining rows, use the “Fill” command described below.

Quickly fill data

Because data entry can be monotonous and time consuming, the “Fill” command allows for quick entry of same data, repeating a formula or to create a data series instantly. Examples include the same state for multiple rows, duplicating a formula across rows or building sequential lists whether it be numerical, series of months or days of the week. This command can be used across columns or to span multiple rows. The “Fill” command can be executed three ways: the “Fill” button on the Home tab; by selecting desired cell and dragging from lower right corner to final cell; or by using keyboard shortcuts (Ctrl + D to fill data down or Ctrl + R to fill data to the right).

Filter

Although most users know how to appropriately sort data to rearrange and locate the desired data, often the “Filter” feature is a more efficient method. Filtering differs from sorting in that it only displays the items meeting the selected criteria while sorting displays all data arranged in a specified order.

The filter option can be turned on by selecting all columns and pressing Ctrl+Shift+L or by clicking “Sort & Filter” and “Filter” from the Home tab. Turning this feature on creates a small gray dropdown that allows data to be filtered by specific text, color or date range. Multiple filters can be applied to quickly narrow down a large spreadsheet to just relevant data.

Eliminate duplicates

Upon finding duplicates, many users often sort data and go row by row to eliminate the duplicate data entries. By using the “Remove Duplicates” feature on the Data tab, duplicates can be removed instantly. To use this function, place a check next to the fields that should be checked for duplicates and click “OK.” For example, checking “Name” and “Address” field removes entries that have duplicate values in both fields, or only selecting “Name” eliminates all duplicates in the name field regardless of the address field.

Data validation

Maintaining spreadsheets with consistent data can be difficult when multiple people are collaboratively working together, but without consistency we risk issues of garbage in and garbage out. A great way to help ensure consistency is to use the “Data Validation” tool available on the Data tab to restrict what type of data can be entered in each column.

The “Data Validation” tool allows for the following restrictions to be applied:

Allow only whole numbers

Allow only decimals

Allow only a value from a dropdown list

Allow only valid dates (before, after or during specific dates)

Allow only valid times (before, after or during specific dates)

Allow only specific text lengths (great for case numbers, etc., to ensure appropriate number of digits are entered)

Restrict the number of characters being entered (great for SSN or case numbers)

Restrict only data that meets custom formula criteria.

While utilizing Excel may seem a bit daunting, exploring new features can create time-saving efficiencies that make it all worthwhile.•

__________

• Deanna Marquez ([email protected]) is a co-owner of the Indianapolis-based legal technology company Modern Information Solutions LLC. Areas of service include traditional IT services, software training, and litigation support including trial presentation services. The opinions expressed are those of the author.