How to easily analyze a database using Excel

Collaboration – May 17, 2016

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:

Invoice ID Date Product sold Total client
20001 January 16 2016 4 XYZ $400 CABC inc.
3 DEF $900
20003 March 23 2016 6 ABX $1200  ZZZ inc.
1 STU $200

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:

  A B C D E F G H
1 Invoice ID Month Year Product sold QTY Unit Price  Total client
2 20001 01 2016 XYZ 4 $100 $400 ABC inc.
3 20001 01 2016 DEF 3 $300 $900 ABC inc.
4 20003 03 2016 ABX 6 $200 $1200  ZZZ inc.
5 20003 03 2016 STU 1 $200 $200 ZZZ inc.

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 !

Blog Archive

More articles that might interest you?


12 differences between B2B and B2C marketing

Is the consumer or business marketing all the same? If it were, buying a molten metal skimmer would be just… Read More...


PPC B2B Marketing Campaign: 4 Lessons from Sensei Lawrence

Have you watched the spin-off series of the Karate Kid cult movies "Cobra Kai"? When it comes time for the… Read More...

B2B Marketing in 2020: Here are 9 predictions from our specialists

Can you believe that 2019 is already over? I love this time of year, when we are in a rush… Read More...