So, you would like to be a Virtual Assistant. That’s nice! Do you like tables and spreadsheets? You better! They are about to become your life!
We’re joking, of course.
There are so many wonderful things about being a Virtual Assistant or any other sort of online worker. However, tables and spreadsheets are such a handy and efficient way to keep track of everything in regard to your job that it’s not wonder you will use them a lot. So, don’t you think it’s a good idea to learn more about them?
We are about to show you what Microsoft Excel is and how you can perform some of the basic formatting and how to use some basic functions. That should be enough for you to be able to do different calculations, organise data and even create great charts.
Does this sound familiar? Reminds of something? Google Sheets, right? There are many similarities, but also many differences between these two tools.
Let’s define Microsoft Excel and move on to its functions.
What Is Microsoft Excel?
Since you already know quite a lot about Google Spreadsheets, you will be happy to know that Excel is another tool that deals with the tables and the manipulation of such data. This means there some similarities.
As soon as you open a Microsoft Excel you will recognise it looks quite similar to Google Spreadsheets.
Take a look at this video that features some of the basic functions and the general appearance of the Microsoft Excel.
So, how is this different than Google Sheets? First of all, the functions are not entirely the same. But, secondly, and more importantly:
Since Google Sheets is an online application, you always need an Internet connection to use it, manage it and even access it. You can buy and use Microsoft Excel offline. Simply install it on your PC. It is a part of the Microsoft Office Suite.
If you want to store and access your documents online, you have to store them on a cloud. OneDrive is the Microsoft’s cloud option, but it’s not necessary to use that one. That should be agreed upon with your employer.
Read more about the Excel working environment here: GCF Getting started with Excel.
There, you will find out which part of your Excel workbook is called Backstage, what is a Ribbon, how to customise your Quick Access Toolbar and how does Tell me work. All those details are important to be fully acquainted with how Excel operates.
Now that you know what it is and how it looks, let’s go on and learn about the basics of this great tool.
Table Formatting Basics
Tables are usually packed with data and data is hard to read if it is not properly formatted. Excel gives you some very useful formatting options that can make your work easier and faster.
Basically, formatting means changing the way your cells look, they way the numbers and text are displayed, as well as their colours, backgrounds and fonts.
For example, you can predetermine the type of data you want to enter in a range of cells: general number, currency, text, date, time and many more. Meaning – you say to your Excel sheet: “This cell contains only numbers.” Or text, or dates…whatever you may need.
Why do you want to do that?
This helps you when you want to manage and manipulate your data. Some functions work with numbers, but don’t work with texts. Sometimes, you want the Excel to recognise something as a string of characters rather than just simple text, etc.
You may be asking yourself: “Can’t Excel make a difference between a letter and a number?” Sure it can, but what about when you use numbers as a part of the text. In addresses, for example: 235 St. George Street.
You don’t want Excel to treat this as a number ready for different mathematical operations or as a percentage, do you? Then learn how to format your cells by reading this carefully: Excel Easy: Format Cells
You will notice that the previous article deals mainly with the number data. In order to understand how to format text in your cells, make sure you read this: Desk Bright: Formatting Cells in Excel
For a more thorough explanation of each of the data types and how to manipulate them, take your time and learn from Microsoft: How to control and understand settings in the Format Cells dialog box in Excel. This is a very important and detailed explanation, so don’t rush through it.
Quite frequently, you will manage different spreadsheets, so being able to use conditional formatting when you do so will be of great help. Luckily for you, Excel is an amazing tool when it comes to data management.
Here is an example of conditional formatting, conducted on a spreadsheet that looks a lot like those that you’ll probably work on.
Once you go through these articles, you will know how to format your data and make it more usable. That is just the moment when you need to learn about the most commonly used Excel options – sorting and filtering.
You have probably noticed that these videos feature drop-down lists. They seem useful, don’t they? They are also very easy to use. Here is how:
Lastly, if you need some more advanced formatting functions, take a look at this: Excel Exposure: Basic Formatting Techniques.
Now you have all your data entered and sorted. It is time to start dealing with the functions and formulas.
Functions and Formulas
Let’s explain some basic terms first. Namely, let’s start about explaining what is a function and what is a formula. Mainly, a formula is a user-based calculation, while functions are already built in.
For example, you can choose to add two values from two different cells and display it in a third cell. For that to happen, you’ll add the following in the third cell: =A1+A2
On the other hand, you can choose to use a predefined function that Excel offers and get the same result. You just need to type =SUM(A1, A2)
You can enter, edit or even copy/paste a formula. If you copy a formula from one place to another Excel will do its best to adjust the cell references so it works well in its new place.
For example, if you used the formula =SUM(A1, A2) in a cell A3 and then you copied it and pasted it in the cell B3, Excel will automatically change your formula to match. It will be =SUM(B1, B2). Great, right?
Read through this article to get good, illustrated explanations about how functions and formulas work: Excel Easy: Formulas and Functions
It will also remind you about the sequence of math operations. Remember those? Multiplying before adding and such? Don’t worry, we won’t tell your math teacher.
Basic Functions and Formulas
Want to know what can Excel do? Just consider this: there are over 450 functions in Excel!
Take a deep breath, don’t worry! You don’t have to learn them all! We’ll deal with the most commonly used functions and leave you enough resources to find the instructions for using the others that you may encounter.
Here are some of the most commonly used functions in Excel:
SUM – adds the numbers in a range of cells you choose. You can use it to add just two numbers or an entire range. For example,
=SUM(A1,A2) will add up the values from A1 and A2
=SUM(A1:A7) will add up the values from all cells from A1 to A7
=SUM(A1,A7) will add up the values from cells A1 and A7
As you can see, putting a colon between the cell references determines the entire range, while a comma between them means that only the values from those two cells are used for the operation.
For a bit more advanced use of this function, check out how to use SUMIF. It adds only the values from the cells that meet a certain criteria.
AVERAGE – gives you the arithmetic average of the value in the cells you use. Again, if you separate the cell names with the colon that means you want the entire range between those two cells included in the operation.
If you use a comma, it means that only the cells you put and separate by commas are included in your calculations.
=AVERAGE(A1:A7) will calculate the average of all cells from A1 to A7
=AVERAGE(A1,A7) will add the values of cells A1 and A7 and divide them by 2
MIN/MAX – determines the minimum or the maximum values from the range of cells of your choice.
IF – this is a logical function and it allows you to order Excel do something if a certain criteria is met.
For example, let’s say your employer told gave you a budget of $500 and asked you to find out which products on a huge list are within that budget.
Here is your function:
=IF(A1>500, “Over budget”,”Within budget”)
Put this in the cell B1. Then grab the lower corner of this cell with a function in it and drag it down, until you reach the last cell in the B column that is parallel to the last cell in the product list (we’re presuming the list is in the A column). It will automatically adjust the values to match the cells.
VLOOKUP – for this function to work, you need to have your data organised vertically in the rows. Once you do that, this function will help you find any sort of data that matches your criteria.
Imagine you have to extract information about one customer from a list of thousands of customers, having only the customer ID. Have a look!
COUNT – This function does just what it says it does. It counts cells, data, blank cells and other items on the list. Again, excellent variation of this function is COUNTIF. Can you guess what it does? Yes! It counts only the items that match certain criteria. Here is a list of COUNT function variations.
DATE & TIME functions are not that numerous and they are extremely useful for a VA who has to calculate the working days, today’s date and perform many other similar tasks. Read the article carefully!
These are the functions of our own choice. Other authors will deem other functions as the most commonly used.
To gain confidence and truly be able to say you know your way around Excel, you definitely need to read these two articles and learn your way around functions and formulas.
Microsoft: Formula Tutorial – you have to download the spreadsheet from this page and click the enable editing in order to use it. It contains all you need to know about formulas and you can even try them. And you should.
Microsoft: Excel functions by category
Adding That Special Professional Touch to Your Excel Skills
Now that you know all these formulas and options, you are all set! And then your boss says – make me a report on this and that and suddenly, you feel overwhelmed!
But worry not! There’s a function for that! Meet your new best friend – pivot!
You think you breathtaking report needs some more zing to it? How about some lovely charts and graphs. Yes! Excel does that, as well.
Take a look and learn how to make them:
These may look a bit intimidating at first, but the more you practice, the easier they get. How about creating a graph right now showing how much time you spent working, studying, sleeping and eating last week?
With this, we complete our take on Excel Basics. This should be enough to get you covered for the most commonly performed tasks in eCommerce. However, if your employer uses Excel, find the time to perfect your skills.
Here are the best tutorials and courses you should pick from.
- Spreadsheeto
- GFC Learn Free
- Excel Exposure
- Excel Easy
In Summary
Microsoft Excel is a part of the Microsoft Office Suite. While it also deals with managing of spreadsheets and tables, it is not quite the same as the Google Sheets. The main difference is that the Google Sheets is an online application, while Excel can be used without the Internet and you need a cloud option if you want to make it available online.
To master the basic options Excel offers, you should learn its basic elements like the Ribbon, Backstage, Quick Access Tool and the others. They offer some amazing functions and make your life easier and your work quicker.
Start by learning some formatting basics. That will give you a feel about how Excel really works. You should also master the conditional formatting to further automate this process.
Now that you have your feet wet, take the plunge and start learning about the formulas and functions. Formulas are user created calculations, while the functions are already built-in.
There are over 450 formulas in Excel, but you don’t need to know them all by heart. Start by learning how to use SUM, AVERAGE, IF, VLOOKUP, COUNT and several Date and Time formulas.
Once you feel comfortable with functions, you need to take your time and explore the Pivot function. Given that it is used to create reports on large sum of data, it will come handy for assembling different reports for an eCommerce business.
Moreover, you can add different types of charts and graphs to present your reports, as well.
Frequently Asked Questions
1. What is the difference between the Microsoft Excel and Google Sheets?
a. They are the same thing but created by two different companies.
b. Google Sheets needs Internet connection to be accessible.
c. Microsoft office requires OneDrive to be accessible.
2. What is the best option to create the reports based on your data?
a. Report
b. Pivot
c. Chart
3. Which function should you use if you want to calculate the arithmetic mean of a range of cells?
a. COUNT
b. VLOOKUP
c. AVERAGE
4. If all the cells in the column C are of the same value, which is 5 (meaning, C1=5, C2=5, C3=5, etc), what will be the result of this function =SUM(C2:C5)
a. 20
b. 10
c. 5
5. What happens when you copy/paste a function?
a. It adjusts to its new cell and tries to adjust references to new cells.
b. It remains identical to the copied function.
c. It is impossible to paste a function to a new cell.
6. What is the difference between the functions and formulas?
a. Functions consist of two or more formulas.
b. Formulas consist of two or more functions.
c. Functions are built-in while formulas are user-generated.
7. What does ‘wrap text’ do to a text in a cell?
a. It allows it to toggle text wrapping in a cell on or off. On makes it possible to show text in more than one line.
b. It allows you to wrap the text around the cell, leaving it blank
c. It allows you to change the background colour
8. Why is it useful to determine the type of data in your cell by formatting?
a. So you don’t forget if it’s currency or a plain number
b. Because different formulas can be used more easily on different types of data
c. Data expressed in numbers must be formatted
9. Where do you find the ‘Save’ command?
a. Backstage
b. Quick Access Toolbar
c. Ribbon
10. What is Page Break View used for?
a. You use it to see the how to Excel sheet will look when printed
b. You use it to add headers and footers
c. You use it to change the location of page breaks
11. What kind of system does Excel use to display the most commonly used commands?
a. Traditional menu
b. Ribbon with grouped commands
c. Tabs with commands
12. If all the cells in the column C are of the same value, which is 5 (meaning, C1=5, C2=5, C3=5, etc), what will be the result of this function =SUM(C2,C5)
a. 20
b. 10
c. 5