How to create a database in Excel

42
49953
Excel Spreadsheets

If you are a manager, analyst, trainee, entrepreneur, in short, if you have worked in any company in your entire life you may have heard about databases. For those who do not know, in a very brief way the databases are organized collections of data that relate in a way to create some sense (information).

Simplifying for good Portuguese and without going into more technical definitions, any worksheet in Excel with information on your sales, cash book with financial transaction data or even a complex inventory system can act as databases storing your information.

Excel Online Course

Excel Spreadsheets

1 - Database example in Excel with financial information

In the context of business, databases are essential because they will allow a better understanding of their reality, key indicators and what can be done from there.

To begin with let's look at an example of a financial database:

excel financial database

It's simple, an organized table, with fields of interest (payday, relating to that, value, etc.) in columns and individual (and complete) postings on the rows. So each row will work as a new entry in your database and ideally it should fill all the columns.

Now just having the database does not help in almost anything if you do not have the ability to parse that data. That's why I'm so fan of Excel databases. Because in the same interface, only with a knowledge of beginner and intermediate Excel you are able to develop tools of analysis of databases. Here is an example of a cash flow report retrieved through functions that fetched information from our initial Excel database:

cash flow report

As you can see, Excel allows easy navigation of your data, allows simple insertion of data, is flexible and manipulation of data can be done by creating reports, tables with formulas and functions, dynamic tables e graphics.

2 - Database example in Excel with sales information

Another example for using Excel database very common in companies is the storage of sales information, after all, if you have a company, you probably want to know who you sold for, how much, how much, which ones products and so on.

So let's look at an example database in Excel for sales information

Excel database for sales control

As we have already said, the database organizes information into columns. In our case, it is information such as the name of the customer, the company in which he works, who was the seller responsible, the service offered, value, date of first contact and so on. Each line will have a new entry in your database.

Excel Online Course

Obviously just having this database in Excel, raw, does not help much, as it would give a hell of a job to analyze sales for sale, especially if your company did many. So it's worth using the database as a tool to improve your reviews. In our spreadsheet, we generate some graphs from the database. Look:

sales charts generated from database in excel

3 - What are the databases for?

I think you got it right with our examples of Excel databases above, but the almost essential goal of any database is to transform the data contained in it into reports, indicators, charts and dashboards. Thus, you will have information that will aid your decision making.

When we are talking about a business environment, this decision process could be to decide between allocating funds between one area or another or reducing direct costs because of a low contribution margin of their products. If we take it personally, if you have a database compiling your spending and family budget, you can make a decision on how much and where to invest.

In this post I will not go into the database models that exist (such as the network model, relational model, plan, among others) and in more technical definitions of DBMS (database management systems), but it is worth searching a little more if you have interest (or tell me in the comments that I can make a post more forward).

Instead, I'll go into what programs you can use for developing and using databases:

  • Excel

I believe that the Excel is one of the most accessible database (information storage) tools and, most importantly, data manipulation and decision making. It is easy and practical to move, usually comes in the computer you buy and for those who want to learn has many courses and tutorials available. For those who want to learn the essentials, I recommend this one:

There are still many discussions about the fact that Excel is not a database itself. As I said from the beginning, I have a tendency to ignore this in 99% of cases, but I will talk about limitations of it at the end of the post.

  • Access

This is a database program with many simplifications compared to the more complex ones, which makes it suitable for beginners.

Here is another note, because there are a lot of people who mess with a database that does not consider Access as a database. I will not go into this level of specificity and controversy, I just think that if the program can store data in a logical way, separated intelligently and with a possibility to treat and work on this data, it is considered by me as a database.

database in access

  • MySql

mysql database

  • SQL Server

  • Postgre

  • Firebird

  • Oracle

Excel Online Course

4 - How to create your database in excel

  • 1 step - Think about the structure of a database in Excel

Remember what we say, a database will always have a structure of rows and columns, in which lines you will have your entries and, in the columns, the fields of interest. No matter what you do, you'll never run away from anything on this model:

  • 2 step - List the fields of interest

See that in the example below we have the fields product, chosen supplier, responsible employee, day of order made, quantity, value, cost per unit and date of delivery.

Always remember that these fields will never (or almost never will) be the same for your company. This will always depend on your reality and what you want to control.

Example of fields for database in excel

  • 3 step - Make Releases

In the above example we had 3 launches made: shirt, medium and short.

  • 4 step - Create reports and intelligence from the database in Excel

Always remember to maintain the consistency of your releases, maintaining the same type of posting and always filling all the lines of your Excel database. This way you'll be able to generate consolidated reports like this:

Purchasing report generated from database in excel
Being sincere, although a lot of people wring their noses at Excel, it can be (and usually is) perfectly applicable to the reality of 90% of companies in Brazil (I know this in practice) and I strongly recommend it for most cases .

Here are some ways to use an Excel database

If you want to go one step further in Excel itself, it pays to understand a little VBA (Visual Basic for Applications). I will not explain much of VBA, but for those who do not know it, VBA allows you to create a series of items that make it easier to fill and create your excel database like:

  • Formulários

This is perhaps one of the tools that you only do with VBA and that helps a lot in the use of the database. I say this because the form allows you to create a simple fill interface and, after filling in, automatically created commands update the information in the database.

Form for database in excel

  • Automatic messages

After filling in these fields you can create automatic messages telling you what to do next. For people who are unfamiliar with the excel database or the spreadsheet used, it can help by instructing what to do.

  • Improved data manipulation
  • Automation of some processes
  • Automatic Report Generation

These 3 items can be made in several different ways.

5 - Main Excel functions that you should know

If you already use Excel as your database and take advantage of the features of this incredible program to do your analysis, it's important to make sure you know everything you need to get the most out of your databases and analytics. So I recommend you to know:

  • create tables instead of using data selection
  • create pivot tables (not mandatory, but does good and helps)
  • use dashboards (dashboards)
  • functions such as PROCV, SE, E, OR
  • use advanced functions such as INDIRECT, OFFLOCK, CORRESP
  • apply filters and subtotals
  • search for items with search and find command
  • apply conditional formatting
  • use data validation

80% of this knowledge you can learn in our Basic and Intermediate Excel Course.

6 - Limitations of databases in Excel

Remember the discussions and controversy with people who do not consider Excel as a database? So what the defenders of this line that Excel is not a database advocate for is that you might even find that a table in a database looks like a worksheet, but there's one fundamental difference: in a database all the data in a column belongs to a single domain and this is guaranteed by the DBMS, whereas in a spreadsheet each cell can contain anything and this can cause inconsistency of the information.

Excel Online Course

This is true, but in my opinion, does not disqualify the fact that Excel can store and work data in a simple, practical and accessible. Anyway, it pays to be careful about some items:

  • Very large Excel databases may start to crash
  • Excessive use of formulas and functions can make your worksheet slow (I do not like working with spreadsheets over 5MB - in some cases this is simply impossible)
  • Database security in Excel is not the best (it's relatively easy to circumvent Excel's simpler protection standards)
  • You will need to have a concern with the data architecture and especially with the insertion of them (in some BDs this process is more protected against errors)

Regardless, as we show in our examples of this post, Excel can (and is) used as a database and is an excellent data analysis tool.

Excel Online Course

Excel Spreadsheets

42 COMMENTS

  1. Hi Fabio, one way would be to use a database in access to store the information and make connections to the drillthrough to have the analyzes, but I'm not an expert on the subject to direct much more than that…

  2. Dear,
    I have a phone bill control excel that has several pivot tables and a spreadsheet with a "database" containing the phone bill breakdown of the company's 1 year. This breakdown sheet currently has more than 500 thousand records. When I save always hangs for about 5 min before completing the operation.
    As I only add data to the 1 account breakdown sheet once a month when making the phone bill available, there is no need to have this huge spreadsheet along with the others that make up my pivot analysis tables.
    I would like to know which options are most viable and how would you separate and access the drilldown so that it is always separate from the dashboard?

  3. Oops, I forgot to put the Municipio column after the number.

  4. Rafael, good afternoon, I have a datasheet which I need to get a data and she will give the answer about it showing all the information. Example of my spreadsheet: Project No. Letra UF N. do Municipio, Ponto, Bairro, Distrito e Region. In the bottom line of each title comes the information. I need to take for example the UF - AC - open an option of Municipio and from it chosen all other information appears. Can you help me?

  5. Hi greetings, do you have a worksheet that allows you to manage a mobile phone store, where you can register the product of input, output and stok acts?

  6. Hi Mariana, the limit is the very limit of rows and columns that Excel gives you (more than 1.000.000 of rows and the columns go to XFD (about 65.500).) Now, if you use formulas throughout this range, you can have weight problems in spreadsheet

  7. Hello!
    I would like to know what is the maximum number of fields in a database in excel?

  8. Hi Oldimiro, I do not know Sisma, but in general it is possible to import data from a wide variety of systems, depending on the type of output that this system can give you.

  9. Hi Cláudio, this is possible with VBA programming or using macros, which is still a form of programming, but simpler to use

  10. Hello,
    in programming it is possible to accumulate values ​​in the same "cell" type a = a + b. In Excel is this also possible without programming? For example: in F14 I have the value 100. But as soon as I change to 50, in G14 would appear the result of the sum = 150. And whenever F14 is changed, the result in G14 would also change.
    Thank you in advance.

  11. Good afternoon, I have a doubt, I have to create a spreadsheet for customer consumption, which at any moment can be changed or added, I would like to know what formula or base I can use so that when I enter the customer name, or even looking for it to make a change?

    example: ricardo bought 1 bread
    eduardo bought 1 water

    I would like to return in the purchase of Ricardo and add something without erasing what Eduard consumed. so you can search for or add any customers that appear and add the products. having another table like PROCV to locate such customer and know how much he spent and what he consumed?

  12. Hi Dayse, I believe it is possible, but since I have no experience with sql, I would not know exactly how.

  13. Good afternoon, I would like to select the data that is typed into the Excel cells and insert into a database. Would it be possible to create an 4 fields with "combos" (predefined information) so that I can then in another cell generate an insert sql command with the content that was chosen in the spreadsheet?

  14. Hi Victor, as you want a spreadsheet specific to your need, it's important to make it from scratch. In our site - https://luz.vc/ - We have very good ready models. It pays to take a look to see if someone catches you, but as I said, it's harder when you have a specific need.

  15. Hi, how are you?

    Do you have any type of spreadsheet or model for data storage? How can this kind of work be done? The data is from a project and each day we will update this data, adding information ... Do you have any idea how this can be done?

  16. Hi Ipolito, how are you? This will depend a lot on your need and what you need to do. I would try to use only one tool. If possible, use Excel as a database. If it is not, some possibilities are Access or MySQL. You can also use some software that already does this.

  17. Dear Rafael, I work with inventory control of a hospital, I would like an integrated database to excel to carry out the exits and entries of the products, do you have any suggestions?

  18. Hi Arivano, unfortunately we do not have such a spreadsheet ready. If you are interested in developing a customized spreadsheet, Xavier (gxavier.leonardo@gmail.com)

  19. I would like to know if you would have a worksheet for requesting purchases of materials? In my case I would need to make a large product list available.

  20. Hi, do you have any spreadsheets I can use to manage fleet maintenance?

  21. Hi Renato, how are things good?

    I suggest you take a look at our complete financial management worksheet - https://luz.vc/planilhas-avancadas/planilha-de-gestao-financeira-completa-3-5

    It allows more than 5 cost centers (I imagine that despite the different nomenclature, the goal is the same)

    If it is not exactly what you want, unfortunately we do not develop custom work, so I suggest you send an email to Planilhapersonalizada@gmail.com with the details you need in your spreadsheet

  22. I need to set up a database and financial control worksheets for monthly cash flow that supports the existence of 5 Accountability Centers.
    I need someone to develop this.
    Email: renatorioblues@gmail.com
    tel: 021-98855-2787 and 021-3603-3142

  23. Hi Helio,

    Yes, you can. This summary should probably be done via SOMASES or CONT.SES depending on the number of variables you are analyzing and the structure of your worksheet

  24. I have a spreadsheet that controls the production of employees every day, with different products and different prices, I make the payment fortnightly, would I get a summary by employees of the products and prices within this fortnight.

  25. great tips, I would like to make a database with my blog, the http://www.guascaletras.blogspot.com.br. How do I indicate in a song in the same record more than one name attached to it, in the case the performer or composer, who usually has more than one ever? I would like to use excel for this or even Access

  26. Hi Paul,

    is it possible for Excel to do scheduled queries in an external database, so it seems to me that what you want to do is feasible but I did not understand what you meant by generating a CUBE ...

  27. It is possivel in the following way, an application that inserts the information in a database, I advise SQL Server or Oracle, the second step would be to communicate with this server by executing the queries via ODBC query within excel itself, thus generating a CUBE and customizing as needed.

  28. Hi Paul,

    possible even believe it to be, because nowadays there is almost nothing impossible to do, but the main issue is where you will put the information on the mobile to move to the worksheet. So viability does not seem to be one of the best

    This could be done with an application (expensive development), using google spreadsheets (bad usability on the cell phone) or with some sight where you stored the information and generated some data integration directly, but as I said nothing that seems simple

  29. Hello! I have a spreadsheet in excel that controls sales in a restaurant. I would like to know if it is possible (viable) to create a communication between excel and an android cell phone, through wifi, creating a common database where the android informs the data and the spreadsheet pulls the informed data and feeds the excel information .

  30. Thank you very much Álvaro. If you want to see other posts just tell me your preferences. Big hug

  31. Hi Jackson, how are you?

    I have never used an android tablet, but I imagine that if you have the Excel program installed you will be able to follow the same steps if you have all the features installed normally.

    The question is to check if you have these Excel features and Excel itself in its latest version (2013) over there.

LEAVE AN ANSWER

Please, write your comment
Please enter your name