How to easily make a database analysis with Excel

In database analysis, quite powerful tools (SPSS, Tableau, Zoho Analytics for example) are available. However, these tools may require a certain number of hours of training and even specific knowledge, especially in statistics… what I call ‘mastering force’.

You may not know it yet, but in database analysis, you all have very powerful analysis tools at your fingertips; most likely, more powerful than you can imagine!  And that is why I wanted to publish a new version of the original post with more goodies in it for you.

So, here are a more tips to help you effectively analyze a database (e.g. sales analysis) with Excel.

How to properly organize your database

To make your life easier, consolidate information into one Excel sheet.

Also, clearly identify each of your columns to find your way easily.

To be able to use Excel effectively in data analytics, use this method:

  • 1 record per line
  • 1 information per column

For example: you want to analyze the current year’s invoices.

Here’s an example of what not to do:

ID facture Date Produit  vendu Totalclient
2000116 janvier 20204 XYZ400$Cie ABC inc.
3 DEF900$
2000323 mars 20206 ABX1200$Cie ZZZ inc.
1 STU200$
Print screen in French

When you try to create a pivot table, you will not be able to time these results or know the quantity by product. Also, analysis by invoice won’t not possible.

Now, let’s see what you should have done:

Print screen in French

Wishful thinking to arrive at such a structure? But no, Luke!

database

Excel formulas: the source of The Force

Excel offers you plenty of formulas to get you there without typing it by hand. Here are some examples of how to:

1- Separate the quantity in the Product field

  • Create a new column
  • In this new column, enter the formula: (= left (A2; 2))
  • What it does: we tell Excel to search cell A2 for the first 2 characters. You can easily replicate the formula to your whole database by selecting the cell with the lower right corner and dragging down.
base
Print screen in French

2- Try to seperate the Date field:

Sometimes it happens that instead of January 16, 2020, you have a series of numbers like this: 42385. Let me show you something awesome:

  • In a cell, enter the formula = year (A2). Here, you have the corresponding date! Try with the formula = month (A2) or = day (A2) it works too!

Also separate from information:

Another function can help you. Suppose you want to separate in 2 columns of information which is structured in the same way: ex: 4 xyz, 3 abc.

It is constant: the quantity is always separated by a space.

  • Select your data that you want to put on 2 columns.
  • Go to Data: convert.
  • Choose the option delimited, next, then check space.

There you have it, your data is automatically separated into 2 columns!

Print screen in French

Find, replace:

This function is magic! Suppose you want to replace periods throughout your column, with commas, its simple:

  • Select your data, go to the home tab then search for replace.
  • Enter the value you want to search for (in this case, the “.”)
  • Then enter the value you want to replace with (in this case, the “,”).

That’s it!

Once completed, if you want to convert your formulas into value, select your cells, have them copied, and then right click. Choose “Special Collage” and then choose “value” from the menu.

Use the power of pivot tables

When your database is ready, select all the data then go to the tab “Insert Pivot table”.

Click “Ok” then you will see an Excel sheet added.

In the right window, you can display the values you want in a row or column, so you can easily view online and column.

For example, sales by segments/products. You can see your product markets.

With these tables, you can even transpose everything into a graph. Not bad for your next presentations?

How about Google Sheet?

Want to share the info in real time with your colleagues Padawan or Sith? Google Sheet, for example, allows you to import data into a sheet and make collaboration easier.

And why not, a dashboard?

Need to visually submit a report? Try Google Data Studio. A free tool which allows you to create a dashboard with multiple sources of real-time data (e.g. Google Analytics, Google Spreadsheet, some social media, etc.).

Pro tip: use the ‘how to’ option…

To look like a pro, when you are looking for a solution with a formula in Excel, Google sheet or even in Google Data Studio… Look in Google! 

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

master
work

Blog Archive

More articles that might interest you?

Google SGE and B2B marketing: how to be the “Lisan Al-Gaib”

Yes, I know, the "meme" below will go out of fashion in no time. But we can't pass up the… Read More...

content

B2B content marketing: a look at usage and… AI!

I recently published on LinkedIn, part of the results of CMI's annual content marketing study, "B2B Content Marketing Benchmarks, Budgets,… Read More...

Marketing and sales alignment : see how AI can strengthen it to optimise your customer journey

Digital transformation has been shaking up traditional B2B marketing methods for several years now. But with the arrival of AI… Read More...