Excelde endirimli qiymeti nece ala bilerem

2. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 do not contain exactly triangle.

3 ways to calculate VAT using Excel – step by step

Did you know that over a million companies around the globe use the well-known suite of office tools, Microsoft Office 365? Moreover, one of the best known and most popular tools in the sector, Microsoft Excel, is used on a daily basis by around 1.2 million users.

These figures demonstrate just how important this tool is today, hence many sectors often require workers to have intermediate or advanced Excel skills.

Above all, they’re looking for know-how of some of the most commonly-used functions, such as calculating VAT using Excel. This is an application that any company or person is likely to need at some point. And for that reason, below we explain exactly what VAT is and how to calculate it easily using Excel.

What is VAT and why is knowing how to calculate it so important?

Before explaining how to use Excel to calculate VAT, it’s vital to know what exactly Value Added Tax (VAT) is.

VAT is a tax applied to the sale of goods and services, in all EU countries as well as the UK. Mind you, it is important to note that each country charges different rates of VAT.

For example, in Spain, general VAT is set at 21%. However, in the same country there is a reduced VAT rate for certain products and services, such as food or books, which is set at 10%, as well as super-reduced VAT (4%), applied to basic necessities.

It should also be noted that VAT is not paid by the seller, but rather by the buyer. In other words, if a person buys a product costing €100, they have to pay €21 VAT, making the full price to be paid €121, with €100 being the product price and €21 being the VAT.

As we’re dealing with a tax that applies to a wide range of goods and services, it can be really handy to know how to calculate VAT in Excel. For example, if you are self-employed or own your own business, knowing how much VAT to add to the price of your products is fundamental when it comes to setting prices, issuing invoices or filing tax returns.

But thanks to the advanced formulas featured in this office tool, you can easily automate VAT calculation in Excel, saving you time and reducing the chance of making mistakes.

3 ways to calculate VAT using Excel

Although Excel was devised as a simple spreadsheet aimed mainly at accounting, nowadays it is a comprehensive tool that can do just about everything. Its amazing versatility and infinite functions have made Excel a software used in almost every field, and calculating VAT in Excel is just one of the innumerable tasks you can perform.

Below, we explain three different ways to calculate VAT in Excel, so that you can choose whichever best suits your needs. Additionally, with a little imagination, you can employ these formulas to optimise your financial management skills.

Basic VAT calculation using Excel

The easiest way to calculate VAT with Excel is with one of its formulas, which can be activated in a cell by typing the equals symbol (=).

Let’s look at an example to make things easier: if the VAT to be applied is 21% and you want to know the final price of a product or service that without VAT would be €50, then you need to calculate 21% of €50. Here’s how:

In a cell, add the price of the product without VAT (A1), then in another cell (B1), divide the VAT sum by 100. In this case, in cell A1, type 50 and in cell B1, 21/100.
Then, multiply the price by the VAT/100. In other words, A1*B1. You can also multiply the price without VAT by 0.21, i.e., 50*0.21. It’s important to press Enter to get the VAT result, which in this case would be 10.5.
Lastly, to this result you need to add the initial price of the product or service = (Initial Price *VAT) + Initial Price. So, the formula to calculate VAT using Excel would be =(A1*B1)+A1, giving you the total price including VAT. Following this example, type in =(50*0.21)+50. The final result will be 60.5.

Calculating VAT in Excel using %

The above method to calculate VAT using Excel could be described as rudimentary. However, there are other more efficient and practical ways to do the calculation. One such way is to use the percentage symbol (%). To type it, press Shift + 5.

This method is similar to the previous one, but you just multiply the initial price by the percentage of VAT, i.e.:

Automatically calculate VAT in Excel

The two previous ways of calculating VAT using Excel are great if you only need to perform a couple of calculations. But what if you need to calculate sums for 100s of products? Doing that one by one would be a waste of time, but luckily Excel features the necessary tools to automatically calculate prices including VAT.

To do this, for example, in column A, you have to enter all the prices excluding VAT. In the next column, in this case B, enter the VAT percentage you’re going to be working with (21%). Lastly, after applying the formula that we explain below, in column C you will automatically see the final result.

So, to get the results in column C, you need to do the following:

all prices excluding VAT (column A) and the VAT rate (column B)

As you can see, this not only saves you time by calculating multiple prices, but also makes it possible to adjust to potential VAT rate changes. For example, imagine that the product you’re selling, which currently has 21% VAT applied to it, changes to the reduced rate of 10%. In this case, all you need to do is change the cell with the VAT rate (column B) and Excel will recalculate all of the prices automatically. Give it a go!

You’ll soon find that calculating VAT in Excel is just one basic example of the potential the tool offers, because Excel features many mathematical functions of all kinds. That’s why mastering Microsoft Excel is vital when it comes to being efficient at work.

Microsoft Excel, the tool that has democratised data management

It’s common to read that Excel has democratised data management. And it’s no wonder because, as a widely used tool, it provides the means to create databases, registries, tables and charts in a matter of seconds.

Efficient data management is a crucial task at any business, and while there may be more comprehensive or specific programs for this job, Excel’s versatility makes it the go-to option for many organisations. In fact, over a million companies use it on a daily basis.

Excel is undoubtedly the tool that everyone should learn to use, because thanks to digitalisation, it’s only becoming more and more important. In this sense, having Excel know-how is increasingly necessary, both for professional development and to enhance job opportunities.

Want to find out what else Excel is for? Banco Santander is launching 5,000 Becas Santander | Excel for All 2023, with which you can quickly and thoroughly gain the necessary skills to handle Excel spreadsheets.

With this 15-hour, 100% online course, you can go at your own pace to acquire all of the necessary know-how and master Excel in record time, applying that knowledge to your daily life both academically and professionally. All of this will be done via practical exercises designed to reinforce learning, with instructors specialised in Excel who can answer any questions you may have. Additionally, you will learn everything via Udemy, the leading platform for digital training.

Moreover, depending on your goals, you can choose from two different courses:

Complete Excel – From beginner to advanced: designed as a journey from the most basic to the most advanced features of Excel. In this course, you will learn about all of the tools the program offers, meaning that in record time, you can master the most commonly-used Excel functions that companies look for. You will cultivate your skills in the most advanced functions, task automation, and creating reports using pivot tables.
Advanced Excel – Macros and VBA: learn about all the tools Excel offers and master pivot tables, programming language and new graphics with VBA + Access or Power Tools such as Power Query and Pyvot.

Both courses, available in Spanish, English and Brazilian Portuguese, are delivered by top-tier experts and are free to recipients. And you don’t need to have a degree or be a Banco Santander customer.

Are you a professional over 18 who’s looking to master Excel in record time? Apply for Becas Santander | Excel for All 2023 and acquire one of the most important, cross-industry assets. And remember, never stop learning!

(At this moment, the Santander Scholarships | Excel for All 2023 call for applications has ended, but we encourage you to take a look at the Santander Scholarships portal to find the training that best suits you and give a boost to your professional career. Take advantage of the opportunity).

SUMIF

The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples.

Numeric Criteria

Use the SUMIF function in Excel to sum cells based on numbers that meet specific criteria.

1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10.

2. The following SUMIF function gives the exact same result. The & operator joins the ‘less than or equal to’ symbol and the value in cell C1.

3. The SUMIF function below (three arguments, last argument is the range to sum) sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain the value 25.

4. The following SUMIF function gives the exact same result (second argument refers to cell D1).

Text Criteria

Use the SUMIF function in Excel to sum cells based on text strings that meet specific criteria. Always enclose text in double quotation marks.

1. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly circle.

2. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 do not contain exactly triangle.

3. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly circle + 1 character. A question mark (?) matches exactly one character.

4. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain a series of zero or more characters + le. An asterisk (*) matches a series of zero or more characters.

5. The SUMIF functions below sum values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly triangle or circle8.

Date Criteria

Use the SUMIF function in Excel to sum cells based on dates that meet specific criteria.

1. The SUMIF function below sums the sales after January 20th, 2018.

Note: the DATE function in Excel accepts three arguments: year, month and day.

2. The SUMIF function below sums today’s sales.

Note: today is August 3rd, 2018.

3. The SUMIFS function (with the letter S at the end) below sums the sales between two dates.

Note: the SUMIFS function in Excel sums cells based on two or more criteria (first argument is the range to sum, followed by two or more range/criteria pairs). Adjust the dates to sum the sales in a specific month, year, etc.

And Criteria

Summing with And criteria in Excel is easy.

1. For example, to sum the cells that meet the following criteria: Google and Stanford (two criteria ranges), simply use the SUMIFS function (with the letter S at the end).

Note: remember, when using the SUMIFS function, the first argument is the range to sum, followed by two or more range/criteria pairs.

Or Criteria

Summing with Or criteria in Excel can be tricky.

1. The formula below sums the cells that meet the following criteria: Google or Facebook (one criteria range). No rocket science so far.

2. However, if you want to sum the cells that meet the following criteria: Google or Stanford (two criteria ranges), you cannot simply use the SUMIF function twice (see the picture below).

Note: cells that meet the criteria Google and Stanford are added twice, but they should only be added once. 10 is the answer we are looking for.

3. The array formula below does the trick.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces <>. In Excel 365 or Excel 2021, finish by simply pressing Enter. You won’t see curly braces. Visit our page about Summing with Or Criteria for instructions on how to create this array formula.

How to Create/Add a Currency Symbol in Excel

In this tutorial, we will discuss how to add a currency symbol to your numbers in excel completely.

There are three methods to add the currency symbol. All of them use the format cells feature with a different format for each. We will also explain different characteristics for each cell format you can use to add the currency symbol here.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

How to add a currency symbol in excel 1: currency format
How to add a currency symbol in excel 2: accounting format
How to add a currency symbol in excel 3: custom format
How to add an international currency symbol in excel (e.g. USD)
Exercise
Additional note

How to Add a Currency Symbol in Excel 1: Currency Format

The first method we will discuss for the currency symbol addition process is the currency format. This format, like other formats, can be accessed and activated through the format cells menu in excel.

Excel will place your currency symbol right in front of your number
You can set the number of decimals and the negative number form you want for your numbers

You can see those characteristics in display below.

How to implement this currency format on your numbers in excel? Here are the detailed steps.

the cells

The Format Cells… choice in the right-click menu:

The More Number Formats. choice in the Number Format dropdown: