New to Analytics? — Start here

Think you are new to analytics? You may have a start. Most of us in business, and especially accounting are quite adept in Excel. This makes the tool a perfect place to start. You cannot use the largest data sets, but with new Excel tools such as PowerPivot, that is constantly increasing.

Analyzing data in Excel helps you learn the process of determining which questions can be answered from the data and using an analytics technique to summarize. If you can do a Pivot Table in Excel, you are on the road to learning more about analytics.

For more information, see this article from Computerworld:

Excel Tip: Easily enter Absolute references

When entering formulas, use the F4 key to toggle between absolute and relative references.  When typing the above example, after typing “A1”, press F4.  Keep pressing F4 until the combination of absolute/relative references appears. 

Press F4 to make cell value Absolute

For a refresher on the differences between Relative and Absolute references, go to:

Excel Tip – Flash Fill

Here’s your Monday Excel tip to help you become an Excel whiz!

Excel 2013 has a great new Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes.  Unlike a formula, the new columns are static, so if the original information is updated, the data won’t change.  If you need to have the new columns update, then Text Formulas are the better choice. 

In the following example, flash fill was used to complete all of the information after the 1st row was complete:

To use the Flash-fill:

  1. Type in the pattern of the information you wish to extract.  It must be in a column next to the original data.
  2. Press Enter
  3. From the ribbon, select Cells > Fill > Flash Fill or press CTRL + E

In the following examples, names of individuals were imported into a single column. The Flash Fill can be used to separate the names into 3 columns, but generate an email address, based on the pattern in the first cell of the column.

Enter the data in the first cell. Press Enter, then CTRL + e
The first name is complete.

Auto-fill is used to fill in the rest of the data, including:

This feature can be used in a lot of different situations. This YouTube has 20 more ideas of how you can use Flash Fill.

Excel Tip – Keyboard shortcuts

The fastest Excel users have one thing in common They rarely use the mouse. Most all actions in Excel can be done with a keyboard shortcut. Here are some of my favorites:

CTRL + HOME — Go to Cell A1

CTRL + END — Goes to the last cell used. 

CTRL+SHIFT + right arrow then down arrow — will select all contiguous cells

CTRL + A –  Selects entire spreadsheet

F4 – Changes cell address to Absolute

CTRL + T – Converts data range to a table

CTRL + E — Flash Fill 

CTRL + ; — Inserts the current date.

Excel Tip: Quick Fill Down

Excel is THE tool for accountants. There is rarely a day that will go by without using Excel. Each Monday, this blog will post a time saving tip that is helpful. Enjoy the first tip!

In larger spreadsheets, I used to spend lots of time dragging the cursor down a column to extend a formula for hundreds of rows. This trick will save lots of frustration and TIME!

By simply double-clicking on the Fill Handle in the lower right-hand corner of the cell, the formula will copy down the column.

NOTE: The column to the left of the formula needs to contain data. If there is a blank cell in the column to the left, the formula will stop at that row. In other words, the data in the adjacent column must not contain empty cells within the data.

I hope this tip saves you as much time as it has saved me!