step by step to take your analytical skills to the next level – WAU
Tired of seeing wonderful spreadsheets in Excel and not knowing how to make one too? Your problems are over. Check out our complete guide on how to master the tool!
You probably feel a chill when you hear the name “Excel”, right? Okay, rest assured. Here is a safe place! O Excel tutorial you’ve always dreamed of, it’s already a reality!
We understand that most professionals are afraid of using spreadsheets. They look really complicated, have indecipherable formulas and complex functions.
Especially Marketing professionals, who are not very familiar with the tool.
But do not need be like that!
Excel – and, more recently, Google Spreadsheets – are fantastic tools. I’ll show you how to use them and take your analytical skills to the next level.
See everything you need to know How to user Excel:
- Understand the logic of a spreadsheet
- Understand how to navigate a spreadsheet
- Learn to work on multiple sheets in spreadsheets
- Use cells correctly
- Learn the basic, intermediate and advanced formulas
- Understand how to make charts in Excel
Download this post by entering your email below
Powered by WAU Convert
All this you can see now:
Excel tutorial: the logic of a spreadsheet
When you open Excel, you are already faced with an interface that is not very friendly.
After all, it is very different from the most common interface of our day to day, of text pages, like this blog that you are reading now, and also in Word.
But don’t worry, working with Excel is much simpler than it looks.
In an Excel spreadsheet, the basic information block is a cell. Each cell contains information.
It can be a date, a number, a block of text, percentage or formula, among others. This cell is part of a leaf.
Each worksheet can be made up of several sheets, each with its own set of cells. Thus, our hierarchy is:
File (name.xls or name.xlsx)> Sheet> Cell.
* Note: in Calc (from OpenOffice and BrOffice) the spreadsheet extension is .ods. In Google Spreadsheets, it is identified by an internet address, such as “docs.google.com/xxxxxxxxxxxx”
Back: within a spreadsheet, you can have several sheets, within each sheet, several cells. It is a very simple hierarchy, which governs any data set you are planning.
A practical example would be:
- Spreadsheet: Marketing Investments
- Leaf: August
- Cell: Investment in Adwords.
Much simpler, right?
Navigating a spreadsheet
Working with spreadsheets, you can reference (or search for) cell information in other locations to perform calculations, comparisons, and other operations. Thus, each cell has a unique “address” within its own sheet.
This address consists of a coordinate, which contains its line (the vertical dimension) and column (its horizontal orientation).
The lines are identified by whole numbers, in ascending order.
The columns, by letters, in alphabetical order.
This extremely versatile system allows you to organize your information and search your data from place to place in a very agile and practical way.
This system is consistent with Excel, Google Drive, Calc and any other spreadsheet software.
See the example below:
In “column A” are listed the posts and in “line 1” the interactions that each of them had. See how easy it is to navigate this information:
- The “line 3”, gives the interactions of “Post 2”. The title of the post, has the address “A3”. The number of pageviews for this post is in “B3”. The number of FB Likes for this post is at “C3”. And so on…
In this way, I know that all the information related to “column D” is “Tweets” and, as I go down to D2, D3, D4 and D5, I see the dimension “Tweets” of different properties, identified in the “column THE”.
You can also reference an entire range at once, using “:” (colon) as a separator between the beginning and end of the range, so we have these examples:
- A2: A5 – The vertical range comprising A2, A3, A3, A4 and A5.
- A2: E2 – The horizontal range comprising A2, B2, C2, D2 and E2.
- A2: E5 – The square that has a vertex in A2 and another in E5.
- A2: A – The vertical interval that starts at A2 and runs to the end of column A.
- A2: 2 – The horizontal range from A2 to the end of line 2.
Now that you can identify any cell on a sheet, let’s take the next step:
How to work on multiple sheets in spreadsheets
See the view of the print screen above, a little more openly:
Now, in addition to seeing the cell information, you also see the file name (spreadsheet) “Post Excel – Keyword research”. Looking at the bottom of the print, you also see that I am working with several sheets. “Examples for the post” and also “keyword.io” are visible.
It is very common to use different sheets to aggregate data in common, such as the numbers for a specific month, department or action.
The cool thing is that you can reference them from one place to another.
So it is possible to have a “numbers of the year” sheet, for example, which brings together the sum of each month in a summary.
The syntax for this is (without quotes) “SheetName! CellAddress”. An exclamation separates the sheet name from the cell address.
So, the full address of the “Post 1” pageviews mentioned above is Examples for the post! B3.
Jewel! Now you know how to identify every cell, on every sheet, in any spreadsheet you make. So let’s take a look at how to do useful things with this:
How to use a cell correctly
Now that you know how to identify a cell within your spreadsheet, it’s time to understand how to work with it correctly. Most people don’t understand the passage below, and they fail for it!
A cell can contain information of three different natures:
- Text (labels): A set of letters and numbers (string) used only to identify or organize a set. In the example above, B1, C1, D1 and E1 are text cells, as well as A2, A3. A4 and A5.
- Numbers: Composed only of numbers, a “value” contains data that can be understood in several ways, such as number, percentage, date, duration, among others. The nature of a value needs to be stated to prevent Excel from misinterpreting it! To understand this, think of the number “100586”. This pure number can be understood as:
- Currency: 100586 is R $ 100,586.00
- Date: May 10, 1986
- Date: October 5, 0086
- Realize that all interpretations are correct. What is missing is context!
- Formula: It is an instruction, which tells Excel or Spreadsheet what to do to occupy that place. All formulas start with the sign “=” (equal) and somehow transform the contents of other cells.
- A formula can count several functions together. Within a function, the parameters for its use are placed in parentheses, with a comma or semicolon separating them, depending on the language of your Excel.
[Indicador de função (fórmula) no Excel e Spreadsheets]
Registering numbers correctly in cells
As we have already seen, it is often necessary to tell Excel what kind of number we are giving him. To do this, just select the cells, click on “Format” and then on “Number”.
[Escolha de formato, no Spreadsheets]
[Escolha de formato, no Excel]
“Automatic” is the default, and will try to guess what kind of information is there. I particularly do not recommend using this mode, as it is very prone to failure.
“Plain text” ignores the number, and treats it as neutral information, part of a text, but devoid of any mathematical meaning.
A barcode is a good example of a “plain text” number. It is just a sequence of digits.
Again in our example:
Here, I clicked so that each cell was seen with the identifications:
- B2: “Number”. The number 400 gained two decimal places. (The number of decimal places can be configured)
- B3: “Currency”: The number 1000 won a dollar sign and also a marker for cents
- B4: “Date” The number “250” was converted on September 6, 1900. (250 days after 01/01/1900.)
- B5: ”Scientific” The number 5600 in scientific notation. In other words, 5.6 * 10 ^ 3 (5.6 times 10 to the cube)
You don’t have to worry too much about understanding each of these identifications now!
The most important thing is that you know that, depending on the notation delivered to the program, your number will be treated differently.
Knowing this, you can certainly choose the correct notation as you work.
How to use Excel to make your job easier
If you made it this far, congratulations! You already understand more about how Excel (and Google Spreadsheets, Calc and other spreadsheets) work than most people!
It’s time to bring the coolest tool into the conversation then: formulas.
As we have already seen, a formula is always stored inside a cell. It governs the value that will be shown at that address (and, in some cases, at adjacent addresses as well) and can use information from various places for this.
There are hundreds of possible functions, which can be combined in infinite ways to automate – or at least simplify – virtually any task. To understand the variety of operations available, let’s look at their main categories:
String / Text: To join, separate, combine or manipulate blocks of text or their letters separately.
Date / Time: Operations related to the calculation of time, interval between dates, working days and the identification of days, weeks and months.
Mathematics / Trigonometry: In addition to arithmetic operations, it allows calculating complex, constant equations (such as Pi), generating random numbers and changing numbers with rounding, converting units and operations with angles.
Statistic: For cluster analysis. Automatically identifies percentiles, averages, medians, distributions, counts, permutations, variance, among others.
Logical functions: Allows other functions to be combined and analyzed according to a rule described by the user. Returns “TRUE” or “FALSE” for functions that check for a given condition and take action according to the result.
Information functions: They are basically used to evaluate the result of other functions. They can answer if a certain cell is blank, it is an error, a date or a text, for example. They also identify and manipulate the format identifiers (date, time, percentage, currency, etc.) mentioned above.
Data base: Deliver values related to a group of cells (range). The number of elements can be used for accounts, to point out their biggest (or smallest) member, the variance within the group, among others.
Finance: The name says it all! They are used to simplify the calculation of interest, depreciation, investment periods and number of payments, for example.
Engineering: Basically used to convert numbers between its various forms of representation, such as binary, hex, octal and some units of measurement.
Reference / Lookup: Scan a certain space in search of a value given by users. Ideal for finding correspondences between different data sources, as well as relationships between different indexes that have elements in common.
I want to make something clear. I’m quite sure that the above list, at first, may have left you a little confused.
After all, with so many possibilities, it’s hard to know: where to start With so many options at hand, which one should I use to make my job easier?
Do not worry. I selected some of the most commonly used formulas and we will go through them together now.
For this, let’s remember our sample data set:
Basic Excel formulas
To learn how to use Excel it is essential to understand how to use your formulas.
Here we will present some of the most commonly used basic formulas in the tool.
SUM (or SUM)
The SUM function adds a group of values determined by the user. To add the total number of pageviews in the four posts in our example, the function would be written like this:
= SUM (B2; B3; B4; B5).
Briefly: = SUM (B2: B5)
In this case, the result would be “7250”, the sum of all the terms indicated.
The same result would be obtained by writing:
= B2 + B3 + B4 + B5
However, in a larger data set, this notation would be much more laborious and error-prone.
AVERAGE (or AVERAGE)
Returns the numerical average of a set. Applying this function to the same previous interval, we have:
= AVERAGE (B2: B5) or
= AVERAGE (B2, B3, B4, B5)
The result here would be 1812.50, which is the sum of the 4 terms divided by the number of terms in the set, which is 4. 7250/4 = 1812.50.
MEDIAN (or MEDIAN)
Mean and median are routinely confused, but they have different functions. As we have seen, the mean is the sum of the values divided by the number of terms. The median is the central value of a set.
I mean, it is the point where half of the values are above it and half are below it.
In our example (which has an even number of terms), we would find the average by adding the two central values (400 and 1000) and dividing by 2, with a result of 700.
In an odd set, the median value is listed directly. To perform this function, simply use:
= MEDIAN (A2: A5)
COUNT, COUNTA, COUNTUNIQUE and COUNTBLANK
These two functions are used to count how many elements there are in a set. COUNT will fetch only the cells that have numbers within the given range, and return how many entries there are.
COUNTA returns to the number of elements of any nature. It can be used to find out how many URLs are contained in a list, for example.
COUNTUNIQUE also does a list count, but ignores repeated items. So if you are looking for unique entries in a set where there are duplicates, this is your choice.
Finally, COUNTBLANK lists only the blank cells in a range. Use it to identify flaws in large sets or errors in filling.
The syntax for all of them is similar:
= COUNT (range to be searched)
This function calculates how much time is between two dates. It works like this:
= DATEDIF (start date; end date, unit of time)
The start or end date can be typed directly into the function or referenced from other places, as we saw above.
The time unit needs to be chosen from a few options. To list in years, it is “Y ;. Whole months “M” e, days is “D”.
These are just some of the basic operations of Excel. Moving forward, we can use this knowledge to build more sophisticated formulas.
Excel formulas: intermediate
Now that you’ve learned the basic formulas it’s time to move on!
VLOOKUP, or VLOOKUP
This formula will be a great ally when you need to compare data from two different sources. For example, on one sheet you have the names and emails of a group of people, and on the other you have the names and birthdays.
Now, if you want to send a birthday email to each one, how can you gather this information?
VLOOKUP will scan a range from top to bottom, horizontally, in search of a value that you have determined.
Once this value is found, it returns the contents of any cell that is ahead of that value.
See a sheet with an example similar to ours:
Imagine that these two lists (A: B and D: E) actually have hundreds of names. It would be a nightmare to bring each birthday girl’s email, right? See how VLOOKUP solves it.
= VLOOKUP (value to search, range to search in, return column, [tipo de busca])
Calm down, it’s not complicated! Let’s look at each term:
- Lookup value: In our case, it would be the name. So, if we want to find Luis’s email, this term is D2.
- Interval to search (table array): Where the search should be made, including the cells where the information sought is. This is A2: B4.
- Return column (index): What is the horizontal distance between the search and the value we want to return, where “1” is the column itself. Here, as Luis’ email is just to the right, this value is 2. This value only accepts positive numbers, so you can’t search on the left, just to the right of the search term.
- Search type (is_sorted): If this parameter is 0 (zero) or “FALSE”, it only results if the identification is accurate. “Luís” would not be returned in place of “Luis”, for example. If TRUE, Excel assumes that the list is in alphabetical order, and will treat incomplete equivalences as true.
Replacing in our formula, we have:
= VLOOKUP (D2, A2: B4,2, TRUE)
This formula undoubtedly returns “[email protected]” as a result!
Tip: there is a HLOOKUP (or PROCH) function that works in exactly the same way, but performs the search horizontally, instead of vertical. Use the appropriate variation depending on your data!
An “IF” formula allows us to test how a cell or range responds to a logical test. A logical test can only bring two answers: TRUE or FALSE. That is, it checks whether a condition is true or not.
Despite the apparent simplicity, this formula is one of the most versatile, since there are a multitude of tests that can be done in this way.
Let’s look at the formula syntax:
IF (logical test, What to do if TRUE; What to do if FALSE)
Let’s use an IF to find out if Hugo’s email is [email protected] Would be like this:
IF (B2 = ”[email protected]”, “This is Hugo’s email”, “This is not Hugo’s email”)
In a logical test, we don’t need to check just equality. The major and minor symbols () are also very useful for making logical comparisons. To use “equal or greater” and “equal or less”, just put the sign “=” in front, as in “> =” and “<=".
Follow a more advanced example with me: I want to know in a group of people, who is over 30 years old.
So I’m going to compare birthdays (using DATEDIF to find out which results are greater than 30. I’m also going to use a new function, TODAY (), which just returns today’s date.
Check it out:
In these examples, my result for the logical test brought only a text as a result. But it is also possible to use any formula to perform an action in both negative and positive cases.
COUNTIF, SUMIF and AVERAGEIF
Now that you know how to use COUNT, SUM, AVERAGE and IF, you can combine them into three new functions. In all of them, the logic is the same.
The function is only executed when a logic test returns “TRUE” for a condition.
The syntax for all of them is the same.
For example, if I want to know how many people in a group are under 30, I can use:
= COUNTIF (Interval to be checked, condition to check in the interval)
In our example, using the same functions as before, it would look like this:
Advanced Excel tricks
When we only have two quantities organized in a spreadsheet, its own worksheet (or worksheet) organizes all the information you need.
After all, every spreadsheet has two dimensions: rows and columns. But it is often necessary to go beyond this. For this reason, there are some tricks for dealing with a larger number of variables without losing control.
In fact, these functions go further!
With the resources below you can order your data in different ways, and you can gain insights from the new visualizations that would be a pain in the face of disorganized data.
Let’s go to them:
SORT (or ORDER)
The sort function organizes, in ascending or descending order, a column or range of data. For data in numbers, dates, percentages and currency, the order will be…. Well… numerical!
In the case of alphanumeric data, containing any combination of letters and numbers, alphabetical ordering will be used.
It is possible to activate the SORT via a formula cell. In this case, you use the syntax:
= SORT (range, sort)
This trigger is useful when you don’t want to tamper with the original data, as it brings the ordered range to another part of the sheet, or even to a different sheet in your file, preserving the initial set intact.
For when you want to order the original data, you can activate the SORT in the menu, clicking:
Note that whenever you use a range for your SORT, you need a reference column. Thus, the relationships between columns are always maintained with their correct associations. A great hand on the wheel!
How to make DYNAMIC TABLE (or PIVOT TABLE)
A pivot table allows you to intelligently group and analyze data with a few clicks. It is ideal for when there are different variables in the same data set.
In practice, it makes it easy for you to separate and group your numbers into specific segments, counting only the relevant values by sum, average, count and other mathematical functions.
Calm! It is not as complicated as it looks.
See the dataset below:
<img class = "aligncenter wp-image-5369" src = "https://rockcontent.com/wp-content/uploads/2016/06/como-usar-excel-9.png" alt = "Excel: Pivot table "width =" 630 "height =" 131 "srcset =" https://rockcontent.com/wp-content/uploads/2016/06/como-usar-excel-9.png 654w, https://rockcontent.com / wp-content / uploads / 2016/06 / how-to-use-excel-9-