You probably even get the creeps when you hear “Excel,” right? But relax, you already have the Excel tutorial you’ve always dreamed of!
We know that the vast majority of professionals are also suspicious when it comes to spreadsheets. They seem very complicated with their indecipherable formulas and complex functions.
Especially for Marketing professionals, who are not familiar with this tool, I will show you why Excel – and its similar version, Google Spreadsheets – are fantastic tools.
I’m also going to teach you how to use them and take your analytical skills to another level.
Table of Contents
Excel Tutorial: The Logic Of A Spreadsheet
When you open Excel, you face an unfriendly interface. In the end, it is a very different interface from the ones we are used to, for example, this blog that you remain reading now and those of Word.
But don’t worry, because using Excel is much simpler than it sounds. In a spreadsheet, the basic block of information is called a cell. Each cell contains data, a date, a number, a percentage, formulas, and other options.
The cell is part of a sheet, and each worksheet can be made up of several sheets, each with its purpose and range of cells.
Thus, our hierarchy is:
File (name.xls or name.xlsx)> Sheet (sheet)> Cell (cell).
It’s a pretty simple hierarchy, governing whatever dataset you’re calculating.
A practical example:
- Spreadsheet: Marketing Investments
- One sheet: August
- Cell: Investments in AdWords.
It is is better understood.
How To Navigate A Spreadsheet
Working with spreadsheets, you can reference (or search) cell information elsewhere to perform calculations, comparisons, or other operations. tanu weds manu songs Thus, each cell has a unique “address” on its sheet.
That direction comprises a coordinate, which contains its row (the vertical dimension) and its column (its horizontal orientation).
The rows are identified by whole numbers, increasing order, and the columns, by letters, in alphabetical order.
Do you want to see how that works?
In “column A” the posts are listed, and in “row 1”, the interactions that each one of them obtained. See how easy that information is to navigate:
The “row 3” reports the interactions of “Post 2”. The number of page views of that post is in “B3”, the number of FB Likes in “C3” then thus the logic follows …
That way, you know that all the information related to “column D” are about “Tweets”, and that if we go down to D2, D3, D4 and D5, you can see the information of “Tweets” for the different Posts, identified in “column A”.
You can also reference an entire interval using a colon (:) as separators between the beginning and the end of the break. Look at the example:
- A2: A5 – The vertical range comprising A2, A3, A3, A4, and A5.
- And Also, A2: E2 – The horizontal interval containing 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 starting at A2 and reaching the end of column A.
Now that you can identify any cell on a sheet, it’s time to take the next step.
How to work with multiple sheets in the spreadsheet
Go back to see the same previous print screen with a more open vision:
Now, more than the cell information, you can also see the file’s name (spreadsheet) «Post Excel – Keyword research». Looking below the print, you can also know that I am working with several sheets named “Examples for the post” and “keyword.io”.
And the best thing is that you can make external references (links) from one place to another.
So it is possible to have a “number of the year” sheet, for example, that gathers the sum of each month in summary.
The syntax for that is Sheet_name! CellAddress. Thus, the full address of the pageviews of “Post 1” is an example of the post! B3.
Now that you know how to identify the cells and the other sheets, let’s see what can be done.
Registering numbers correctly in cells
To tell Excel what kind of numbers we are giving it, we have to select the cells, click on “Format”, and then on “Number”.
Automatic” is the standard, which tries to guess the type of information, but may have flaws.
Plain text ignores the number and treats it as neutral information, part of a text, but without any mathematical meaning.
How to use Excel to make your work easier
If you’ve made it this far, you’re ready to start learning about one of the most interesting parts: formulas.
As we have seen, a formula is stored in a cell. It governs the value that will be displayed at that address and can use information from various places for that, that is, external references or links.
There are many possible functions, which can be combined in infinite ways to automate – or simplify, at least – 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 text blocks or their letters separately.
Date / Time: Operations related to calculating time, the interval between dates, business days and the identification of days, weeks and months.
Math / Trigonometry: Beyond arithmetic operations, you can calculate complex equations, constants, generate random numbers and change rounded numbers, convert units and operations with angles.
Statistics: For the analysis of sets. Automatically identify percentiles, means, medians, distributions, counts, permutations, and more.
Logical functions: Allows other parts to be combined and analyzed based on a rule described by the user.
Information functions: To assess the result of other parts. They can tell if a cell is blank if there is an error, like a date or text. They also identify and manipulate the format identifiers (date, time, percentual, and so on).
Database: They deliver values related to a group of cells (range). They can be used for counts or the number of items.
Financial: They are used to simplify the calculation of interest, depreciation, investment periods or the number of payments, for example.
Engineering: Used to convert numbers between their various forms of representation, such as binary, octal, and other units of measurement.
Referential / Lookup: They sweep a certain space in search of a value given by the user. Perfect for finding relationships between different indices that have elements in common.
With so many possibilities, you are probably wondering where you should start. Or perhaps, you are also wondering which ones you should use to facilitate your work.
But take it easy! For that, I chose some of the most used formulas. Do you want to know what they are? To start, let’s go back to our dataset example:
Basic Excel formulas
To learn how to use Excel, it is essential to understand and know how to use its formulas. Therefore, now we present some of the most used basic procedures:
SUM (ou SUM)
The SUM function adds a group of values determined by the user. To sum the total number of pageviews in the four posts in our example, the function would written similar this:
= SUM (B2, B3, B4, B5).
In short: = SUM (B2: B5)
The same result would obtaine by writing:
= B2 + B3 + B4 + B5
In a larger data set, this notation would be much more difficult and susceptible to errors.
AVERAGE (ou MEDIA)
It gives you the numerical mean of a set. Applying that function to the same previous interval, we have:
= AVERAGE (B2: B5) or
= AVERAGE (B2, B3, B4, B5)
The result here would be 1812.50, representing the sum of the four terms, divided by the number of addends, 4. 7250/4 = 1812.50.
MEDIAN (ou MEDIUM)
Medium and median are easily confusing, but they have different functions. The mean is the sum of the values divide by the number of addends. Already the median is the central value of a set.
That means saying the point where half of the values are above it, and the other half are below.
In an odd set, the median value is list directly. To execute this function, use:
= MEDIAN (A2: A5)
COUNT, COUNTA, COUNTUNIQUE and COUNTBLANK
This function is use to count how many elements exist in a set. COUNT will only find cells that have numbers in the selected range.
COUNTA already reports the number of elements of any nature. It can use to know how many URLs are in a list, for example.
COUNTUNIQUE also does a list calculation but ignores repeating items.
Finally, COUNTBLANK lists the blank cells in a range. Use it to identify faults in large assemblies.
The syntax for all of them is similar:
= COUNT (interval to search)
DATEDIF
This function calculates how much time exists between two dates. Its syntax is:
= DATEDIF (start date; end date, unit of time)
The start or end date can insert directly into the function or reference elsewhere.
Already the time unit changes according to the desired time indicator. To list in year, it is “Y”, in months “M” and days “D”.
These are just some of the basic Excel operations. Later, we can learn more sophisticated formulas.
Excel formulas: intermediate
Now that you’ve learned the basic formulas, it’s time to move a little further!
VLOOKUP or PROCV
This formula is a great ally for when you need to compare data from two different sources. For example, if you have the names and emails of a group of people on one sheet and another, you have their dates of birth.
If you have to send them a happy birthday email, how will you collect all that information?
The VLOOKUP checks an interval from top to bottom and horizontally to find a value that you have determined.
