How to easily analyze a database using Excel

For database analysis, some pretty powerful tools are available (SPSS, for example). However, these tools may require many hours of training and even special knowledge (such as in statistics) … in short, for lack of great experience, we must master the force!

(Btw the images in this article are .gifs. Click them and see!)

You may not realize it, but within your reach is  a very powerful analysis tool; likely more powerful than you ever imagined: your mouse!

Here are some tips to help you efficiently analyze a database, such as in a sales analysis, by using something as simple as Excel.

How to organize your database

To make your life easier, try to consolidate information into one single Excel sheet. Also, clearly identify each of your columns to easily find information.

To effectively use the Excel data analysis, always use this golden rule:

• 1 piece of information per line
• 1 piece of information per column

For example, let’s say you want to analyze invoices for the current year.

Here is an example of what not to do:

If you attempt to create a PivotTable, you wont be able to put these results in a time frame or know the amount by product. Analyzing by invoice wont be possible either. This is what you should have:

Excel formulas: the source of the force

Is it wishful thinking to achieve such a structure? No! Excel offers plenty of formulas to help you get there without typing everything out by hand. Here are some examples:

• How to separate the quantity in the ‘Product’ field:
  • Create a new column
  • In this new column, enter the formula : (=left(A2;2))
  • What this does: it tells Excel to fetch the first 2 characters from cell A2. You can easily replicate the formula to your entire database by selecting the cell at the lower right corner and dragging down

• How to separate the ‘Date’ field:
  • It may sometimes occur that instead of the date displayed like this  “January 16, 2016” , you have a type of serial number displayed instead: 42385.

Let me show you something awesome:

In a cell, enter the formula = year (A2). Tada; you have the corresponding date! Try the formula = month (A2) or = day(A2), which works also!

• Separating information :
  • Another function that is quite helpful! Suppose you want to separate information that is always structured the same way (ex: 4 XYZ, 3 ABC) into 2 different columns,. Here is the constant: the quantity is always separated by a space. Select the data that you wish to put into 2 columns. Go to Data, Convert. Select the Delimited option, Next and, click Space. Here, your data will automatically separate into 2 columns!

• Search and Replace:
  • This function is magic! Suppose you want to replace all of your column periods with commas. It’s simple! Select your data, go to the Home tab, go to Find and Select, and click on Replace. Enter the value you want to search (in this case, period “.”), and enter the value you want to replace it with instead (in this case, a comma “,”). That’s it!

• When finished, if you want to convert your formulas into a value, select your cells, click Copy, and then right click with your mouse. Select “Paste Special” and choose “Value” from the menu.

Use the power of Pivot Tables

When your database is ready, select the data and go to the ‘Insert’ tab, then, ‘Pivot table’. Click “Ok” and you will see an Excel spreadsheet to include. On the right hand side window, you can display the values you want in a row or column. With these tables, you can convert the whole chart. Wouldn’t this be a cool idea for your next presentation?

Tip: to look like a pro, when looking for a solution with a formula in Excel, turn to Google!

Ex.: • How to convert a date field in Excel

• How to do XYZ in Excel

The “Excellers” community is pretty creative. And you too will eventually master Excel, like a Jedi !