Is Excel old fashioned? Not at all. The program is one of the most used planning tools in everyday office life. The fact that a community has built up around Excel shows that the program is still highly relevant – but also that it is not always understandable at first glance.
We will show you the basics, tips, and tricks you should know about Excel in the following article.
What is Excel?
Microsoft Excel is the best known and most widely used spreadsheet program. Calculations with formulas and functions can be created in Excel, whereby the commercial, statistical, and date functions are particularly versatile for companies.
The program is part of the Microsoft Office 365 package and is available for both Windows and macOS.
What do I need: Excel Online or the Office Package?
Due to the different packages and similar names of Microsoft products – Office 365, Office 2016, Office Online – it is not always easy for users to directly understand precisely what the differences are. If you don’t already have the Office 365 package, you will probably want to find out which version of the program suits you best. The price question at this point: is the free Office Online enough?
There is no clear answer to this question. Just because Office Online is free, it still has its justification. You can efficiently perform less complex Excel tasks such as the visualization of functions and diagrams.
In addition to Excel, the online world of Office also includes other Microsoft products such as Word Online or PowerPoint Online. But it is also clear that the version is limited:
So only 5 gigabytes (GB) can be stored. And in terms of design, users have to work with a template so that the online version reaches its limits when it comes to more complicated and individual presentations.
Who Excel would, therefore, like to use regular or establish it in his company is well-advised to afford the Office 365 package – for private users or beginners, the online version can once be a good start.
How does Excel work?
Excel is still one of the most widely used software in the world because it offers so many uses. Everything can be calculated and planned in one central location.
With the large scope of services of the basic equipment and the formulas that each user can also create themselves, a program is a beneficial tool for companies, especially in project management, controlling, and other commercial areas.
The highlight of Excel: formulas and functions
Before we move on to the most important of the hundreds of functions that Excel already has without your intervention, let’s take a look at how to create your own formula in Excel .
A simple and common example of creating a formula is to calculate percentages :
Let’s say we have a product price of 90 euros and would like to add sales tax to it. It ‘sIt’s straightforward because you just multiply the amount you want to calculate by a percentage and then add that result to the original amount. This can be used to practice the principle of Excel, which, once understood, can be used for all possible functions.
The formula for calculating a price including VAT is as follows:
VAT =
Net product price * (1 + VAT rate)
Now enter in Excel:
In the A1 field: 90 euros
In the field B1: 19%
Now click on the empty field C1 and then on the function symbol – fx (directly above the table). At this point, you need to create a formula.
The entry of the formula always begins with an is-equal sign. Then create the rule according to which the calculation should work. In our example, we base this on the formula for VAT above:
fx = A1 * (1 + B1)
Click Enter – and you’re done! If the displayed gross price is 107.10 euros, you have done everything correctly.
Of course, Excel can do more than just calculate sales tax – that would also be easy with a calculator. However, you now have the option of linking any formula for individual fields using the same principle.
You go one step further by using add-ins. We will now explain what this is all about.
Excel: add-ins and macros
First of all, we need a definition: What are add-ins? And what are macros? Let’s bridle the horse from behind:
Macro is a term from software development. It describes a summarized sequence of instructions so that they can be carried out with a simple call.
Add-ins, in turn, are ready-made functions or macros that are integrated into software applications and can be fully integrated into the main program. Add-ins are external services that can be integrated into Excel so that you can do even more with the program.
Accordingly, these optional commands and features for Excel are not immediately available but must be installed and activated. Some are free, for some you have to pay. The good thing about it: You will find add-ins for all possible purposes, which are provided by different providers.
Add-ins: example of search engine marketing
Let us move on to marketing: In the area of search engine marketing, for example, add-ins are often used to evaluate SEO projects efficiently and in a time-saving manner.
Such add-ons are available from various providers. The best known is called SEO Tools for Excel. With the add-in, you can create great SEO analyzes in Excel. All you have to do is install the add-in in Excel and import your data from Google Analytics into Excel using the add-in.
You can easily do this by:
1) unzip the zip file after downloading,
2) then open Excel and
3) Drag the SeoTools.XML file onto the Excel datasheet.
“SeoTools” now appears in the menu bar, and you can work directly with the add-in. Even if you want to work with add-ins from other providers – the principle remains the same, and you are usually guided through the process with explanations.
Not only search engine marketing is an excellent example of how marketers can use Excel in everyday life. In the following, we will introduce you to other areas and uses for which Excel is a helpful and widely used tool.
How does Excel help me in my everyday work?
Excel is mainly used in project management, purchasing, or controlling. One of the uses for which the software is undoubtedly used the most is the income-expenditure overview.
Income-expenditure overview: Excel bundles it
In controlling or the accounting of larger companies, the income-expenditure summary reaches its limits when it comes to the fact that the presentation is also sufficient for a tax audit later on. Still, the overview is particularly helpful for small businesses.
For individual projects, the income-expenditure overview with Excel has a great advantage that it clearly shows personal budget items.
This includes:
- recording income and expenses ;
- the automatic addition of expenditure and income, with which you form a surplus or deficit ;
- Deduct input tax and sales tax paid ;
- Determining an expected sales tax burden ;
- Creation of the annual, monthly or project evaluation and
- the visualization of income and expenditure in a diagram.
What sounds complicated is based on the most straightforward functions of Excel, such as the percentage calculation or the sum function. The table can then look different depending on the area of application.
Use for project management & Co.
In project management, in particular, Excel is a widely used tool to support everyday tasks and plan campaigns.
A particular Excel chart is famous for monitoring lead and traffic numbers: the waterfall chart. This allows you to determine interim results and present them. Based on these numbers, you can find out at a glance whether you have made progress and whether you are on track to achieve your goals.
Another table is interesting for marketers – and anyone else who works with data – the pivot table. This is a special kind of meal: Not only can it summarize data, but also highlight and research trends. This is particularly useful if you have long rows or columns whose values you want to add up or compare.
The trick is not to add data, numbers, or information to the table, but above all, to reorganize it so that you get valuable new perspectives. Hence the name “Pivot”, which means “pivot point” or “turn”.
Determine values: Typical questions / FAQ
Although each user and each area of responsibility uses Excel in their way, some functions and tricks are used very frequently, are always useful, and are part of the multiplication table of Excel.
1) standard deviation
The standard deviation measures the spread of data – or, to put it more simply, it indicates the extent to which the measured values deviate from the average cost.
Assume that you have determined the click-through rate of ten ads: The standard deviation gives you an idea of the difference in success between the ten ads.
A small standard deviation shows that all ads have approximately the same success. A high standard deviation, on the other hand, should make you pay attention because it means that some ads are more successful than others.
2) VLOOKUP function
The Excel link helps you to combine two data records in different worksheets into a single table. When could that be useful? We give an example:
For example, in a situation in which you have the names and email addresses of contacts available in one table and the same email addresses in a second table, but only the name of the respective company in addition to these. You now want to create a chart that shows all three components, i.e., names, email addresses, and the individual company.
With the VLOOKUP formula (= VLOOKUP), you can now merge the two worksheets without having to copy individual columns or fields laboriously.
3) Sum function
As the name suggests, add values with the sum function. You can add individual costs, the reference to select cells or ranges, as well as a combination of all three. What sounds complicated is very simple, and certainly one of the most used functions in Excel.
We give two examples:
1) The formula:
= Sum (A2: A10)
adds up the values in the cells from A2 to A10.
2) The formula:
= Sum (A2: A10; C2: C10)
adds up the values in the cells from A2 to A10
and in cells C2 to C10.
4) Create “IF” conditions
The IF formula can be used to create conditions: is the queried value identical to the expected value or not? If this query is true, a specific case is to occur. If the query is incorrect, another case should occur. So the formula follows logic:
If A, then B, otherwise C.
We will explain it to you using an example.
The basic formula in Excel is as follows:
= IF (check; then value; otherwise value)
Check: There must be a condition here.
Let’s say you want to measure the success of a campaign; your goal is achieved when there are 100 conversions. So your test could be:
Higher than or equal to 100 (> =).
Then value: Here, you enter what should happen if the queried condition is true. You can enter values, words, or other functions here.
But let’s make it easy for this example: When 100 conversions have been achieved, the words “Campaign successful” should appear in the next column. The then value is “campaign successful”. It is important to put the value in quotation marks (“value”) .
Otherwise value: This third value is optional. Let’s say the next column should say “Campaign unsuccessful” when 100 conversions are not achieved. The otherwise value is then “Campaign not successful”. Don’t forget the quotes here, too!
Your if the formula could then look like this:
= If (A1> = 100; “campaign successful”; “campaign unsuccessful”)
So there are reasons why Excel is still one of the most used applications in everyday office life despite many alternatives, such as special planning tools:
The diverse application options for different business areas from project management to accounting make Excel a valuable tool for companies – regardless of whether their area of activity is in marketing or completely different industries.
And yes, we know it too: Sometimes users are just annoyed when one or the other function doesn’t work right away. But the reasons for this are usually found quickly. And with this article, you already have an overview of the most important functions and formulas at hand.
