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.
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:
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:
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
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.
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:
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:
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.
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.
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.
- 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:
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
- Inventory control
- Cash flow
- Strategic planning
- Control of action plans
- Customer base
- Employee registration
- Purchasing Management
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:
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.
- 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.
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.