Microsoft Excel 101 – Terminology, Shortcuts and Most Useful Functions
Microsoft Excel. Anyone who has encountered it in the past probably wouldn’t find too many nice words for it – after all, I felt the same way. In recent years, however, I’ve begun to realize myself that I couldn’t function on a regular basis without Excel, both professionally and in my personal life. What can this essential piece of equipment for all computers provide us, why should we use it, and how can we achieve maximum efficiency while working with it? You will learn all about it in this article.
What is Microsoft Excel?
Microsoft Excel is a program for creating and editing spreadsheets. It is part of the Office suite developed by Microsoft. Microsoft Excel allows you to format, organize and calculate data.
A little bit lower in this article, you will read about alternatives to Microsoft Excel that are free. Excel itself is paid, you must have a Microsoft Office license to use it. However, considering all that Office has to offer, it is a great investment. Personally, I can’t imagine my life without programs like Word, PowerPoint, or Outlook. Free alternatives exist, of course, but Microsoft Office Suite is without a doubt the most advanced.
Although it is a program developed by Microsoft, its functionality is not limited to Windows operating systems. You can use Microsoft Office programs on a range of other devices that run on macOS, iOS and Android. Unfortunately, Microsoft Office is not supported on Linux. However, there are third-party resources that allow Microsoft Office, and therefore Excel, to run on Linux.
Microsoft Office: Which Apps Are Included?
In addition to Microsoft Excel, Microsoft Office contains other very useful applications.
Mac and Android versions include Word, PowerPoint, Excel, Outlook and OneNote.
How can Microsoft Excel help me?
When users find that the built-in apps in their operating system are insufficient for their needs, they frequently purchase a Microsoft Office suite. Microsoft Office offers a plethora of features that are used by many people in different roles. While a simple alternative might be enough for the average user, professionals, as well as those who want to be sure of a job well done, choose Microsoft Office.
Microsoft Excel is much used by organisations across the world, including at the top levels of course. Excel allows you to collect and validate business data, perform market analysis, data analysis, performance reporting, strategic analysis, accounting and budgeting, administrative and managerial management, project management, as well as office administration.
Although I will try to make Excel as familiar as possible, I admit that it offers enough features to cover a book saga. Personally, I use Excel on a daily basis to keep track of work done – both mine and my employees’, for efficiency reporting, and it’s also perfect for more complex data work. Further, Excel is perfect for creating budgets, both household and company-wide. I have used Microsoft Excel to evaluate large-scale quantitative sociological research, and while many might say there are better programs for this, personally Excel has offered me great work experience and reliable evaluation of results.
This is why I think everyone should know how to use Excel. The possibilities that Excel offers are endless, and every person can use them for their life, and they will find that it can make their whole life a lot easier.
Best Microsoft Excel Alternatives
If you want to know more about the alternatives that exist for Microsoft Excel, I recommend you to read this article where we have presented in detail the best alternatives to this spreadsheet editor. Nevertheless, I will reintroduce at least a few of them here.
If I want to create a spreadsheet to share with other people – which is not at all an exceptional situation – I always choose Google Spreadsheets. Google Spreadsheets are great in that they are both free and easy to share with other people. When sharing, you can also set who you share them with and how. You can set who has the right to edit, who can comment, and who can only view the document. In terms of features, they are very similar to what Microsoft Excel can do. You’ll just have to get used to their different naming. From personal experience, Google Spreadsheets are good for sharing simpler data. Myself, if I want to create some more complex charts, I prefer to reach for good old Microsoft Excel. The big drawback can also be that you need to be connected to the internet to use Google Spreadsheets.
Microsoft Excel Online
If you don’t want to get used to the names of other functions, then Microsoft Excel Online is tailor-made for you. You’ll find pretty much everything in “full-featured” Excel. However, it does have a few problems – its features are quite limited compared to the desktop application. You can handle the basics here, but for more complex functions I recommend using the desktop version. Another problem is that it works within a web browser, so it can’t exert the kind of computing power that standard Excel can. Yes, Google Spreadsheets work the same way, however from my own experience, I would say that Google Spreadsheets are smoother. Not being able to access your spreadsheets without the internet is another downside to Microsoft Excel Online. Still, it is a great application if you don’t expect too much from it.
Apple Numbers is a great app if you’re a fan of Apple while despising its main competitors – Microsoft and Google. I have to admit that I personally don’t have much experience with Apple Numbers, and have rather used it when I had a MacBook handy and needed to create a few formulas in a hurry. If you’re used to Excel or Google Spreadsheets, then Apple Numbers will take a little longer to get used to – the functionality and syntax is, after all, a little different from what you might normally encounter. Still, it’s a great app that runs briskly even with Apple hardware. What I like most about Apple Numbers, though, is that a spreadsheet created on one device is instantly at my fingertips on other Apple devices, without having to forward the creation or do complicated tracking on different repositories.
Microsoft Excel: Terminology
If you want to work in Excel, you should first familiarize yourself with the terminology that Excel uses. It’s not complicated at all, and I’m sure you’ve come across it before. But as they say, repetition is the mother of wisdom, so I’d rather recap the basic terminology.
Microsoft Excel: Shortcuts You Need to Know
The English alphabet has 26 letters. This is probably nothing new. However, 26 letters – and therefore 26 keys on the keyboard – can help us with a lot in Excel. Let’s go through a shortcut alphabet that will simplify your work in Excel countless times.
I’m sure you’ll agree with me when I say that using shortcuts can make working on the computer a lot easier. If you learn them, you’ll work much more efficiently – and that’s what we’re all about. If you want to learn more about keyboard shortcuts, then I definitely recommend our series of articles to learn more about keyboard shortcuts. I guarantee you’ll find at least a few you didn’t know about before!
Microsoft Excel: Most Useful Functions and Examples
The real power of Microsoft Excel comes from the functions you can use. From basic calculations to the most complex formulas, it offers features that can be used by both the beginner and the master of their craft.
However, it is important to mention at the outset that not all of these functions are named the same in all language sets. This article is written for the English version of Microsoft Excel. If you want to switch your Excel to English as well, continue reading.
Switching Microsoft Excel to Another Language
If your Excel is in your native language, it is definitely easier to work with. Excel itself offers you which arguments you need to enter in the function. But this has its problems too – not all functions are named the same across languages. I’ll show you how to change your language – but don’t worry, this is not a permanent solution. You can switch between languages at will. I’ll show you that too.
- Open Excel, go to the File tab, choose Options, and then click on Language.
- Click on Add a Language. This opens up a window with all available languages. Select the one you want – personally, I prefer English (United Kingdom), but I bet there are lots of you who prefer another English variant.
- After selecting the language, click on Install. The installation process now begins. Note that the language will only be installed after you close Excel entirely, so be sure to save all your work before!
- Now, upon restarting Excel, your Excel will be in your desired language.
- To change Excel to another language that you have installed already, just navigate back to the Language window, select the language you want, and click on Move Up until it is back on top. Hit OK and you’re done.
That wasn’t that hard now, was it? Of course, you don’t have to do it. If you go along with me and find that some function doesn’t trigger even though you’re writing it correctly, just check out this article – we have covered the most common Excel languages and the differences between languages, so you can compare English syntax with the syntax of the language of your choice.
Basic Excel Formulas
Before we dive into the more complex calculation formulas that Excel offers, it’s worth taking a look at the very basic ones. These formulas will give you a better understanding of how formulas work in Excel.
At the beginning it is worth mentioning how the arguments of formulas (i.e. the cells to be worked with) can be written. We’ll show the first few functions with both notations to make it more understandable.
To illustrate some of the basic functions, we will use the following table. The cast of Drive (2011) will serve as a model.
SUM is a basic formula in Excel. Using it, we get the sum of the selected cells or the selected range. It should be noted that SUM can only add numbers.
Now let’s look at the table above.
COUNT is another basic formula in Excel. Using it we can get the number of selected cells or selected range. It should be noted that COUNT can only add numbers.
Now let’s look at the table above.
COUNTA is very similar to the COUNT formula. However, with the COUNTA formula, we can get the number of cells, regardless of whether they contain a number or a character.
Now let’s look at the table above.
COUNTBLANK works similarly to COUNTA, but with the difference that it returns the number of cells in which nothing is written. Again, this can be written either by selecting individual cells (the arguments in the formula will be separated by a comma or semicolon) or by selecting a range (by selecting the highest cell and the lowest cell and writing a colon between them). As we can see, the actress Tina has no height entered – so we can try the COUNTBLANK formula in the height column.
Formula used: =COUNTBLANK(C2:C10) → result is 1.
The AVERAGE formula returns the arithmetic average of the specified cells.
Formula used: =AVERAGE(B2:B10) → result is 43.22.
The MEDIAN formula returns us the numeric cell that is right in the artihmetical middle of specified range.
Formula used: =MEDIAN(B2:B10) → result is 47.
The MODE formula returns us the most common number in the specified range.
Formula used: =MODE(B2:B10) → result is 42.
The MIN formula returns the smallest number in the specified range.
Formula used: =MIN(B2:B10) → result is 37.
The MAX formula returns the highest number in the specified range.
Formula used: =MAX(B2:B10) → result is 75.
The formula LEN calculates the number of characters in a given cell. As an example, let’s take the name of the actress Christina.
Furmula used: =LEN(A6) → result is 9.
The IF formula checks whether the selected cell matches our request. The notation looks like this: =IF(cell and its condition, result if the condition is met, result if the condition is not met).
Since this is a bit more complicated, let’s give a trivial example. Let’s say we want to find out if the actor Ryan is under 50 years old. If so, we want “YES, HE IS” to appear in the cell. If not, we want “NO, HE ISN’T” to appear in the cell. Then we try to apply the same situation to the actor Ron.
Formula used on Ryan: =IF(B2>50,”YES, HE IS”,”NO, HE ISN’T”) → result is NO, HE ISN’T.
Formula used on Ron: =IF(B8>50,”YES, HE IS”,”NO, HE ISN’T”) → result is YES, HE IS.
Advanced Excel Formulas
Now that we’ve covered the really basic Excel functions, we can move on to the more complex ones. All the formulas are described in detail so you can see what activities you can use them for. And if something isn’t clear or formula doesn’t happen to work for you, let me know in the comments!
But for now, here we go.
SUMIFS is one of the most useful functions in Excel, and for me personally, it is the most popular and most used function. Thanks to the SUMIFS formula, I can easily check which member of my team has reported how much work in a given month, for example – however, there are many uses.
Simply put – SUMIFS will return the sum of the values that meet the given criteria. There is also a SUMIF function, however, it can only test one condition, whereas SUMIFS can test several. Therefore, I personally recommend using SUMIFS instead.
What does the SUMIFS formula syntax look like? =SUMIFS(sum range, criteria 1 range, criteria 1, …)
Let’s use a real example. Let’s look at the table with the actors in the movie Drive. Let’s say we want to add up the ages of all the men (the letter M in the Gender column).
Formula used: =SUMIFS(B2:B10,D2:D10,”M”) → result is 354.
So what’s going on here? In the first argument, we select the values in the Age column. In the second argument, we select the values in the Gender column. The third argument is a condition – if there is a letter M in the Gender column (we can tell that we want a letter by putting it in quotes), then the value in the Age column on the same row is counted. Sorry, Carey, Tina and Christina, we’re not counting you this time!
Remember the difference between COUNT and SUM? If you do, then you probably understand what the COUNTIFS function means. It’s basically the same as SUMIFS, except the values are not added, but counted.
The important note is that the test criterion must be specified either as a cell reference or as text (i.e. in quotes).
Now, let’s say we want to count how many women there are in the movie Drive.
We will use this formula: =COUNTIFS(D2:D10,”F”) → result is 3.
CONCATENATE is a feature that is actively used by every professional working in Excel. CONCATENATE will join multiple cells together. This is fantastic when creating keywords for ads, for example, or when creating campaign URLs. How to do it?
Again, let’s take an example. Let’s say we want to display a name along with an age, and the age is in parentheses. Let’s try this feature on Ryan.
The formula will look like this: =CONCATENATE(A2,” (“,B2,”)”) → and result being: Ryan (42).
But let’s say we want to make a complete sentence out of known data. For example, we want to mention the age, and at the same time break down the gender in words. Because we can combine patterns together. How do we do that?
We will use this formula: =CONCATENATE(A2,” who is “,B2,” years old is “,IF(D2=”M”,”a man.”,”a woman.”)) → the result will be: Ryan who is 42 years old is a man.
Take a closer look at the formula. It does make sense, doesn’t it? Excel isn’t really all that hard, and soon, you will be an expert yourself!
LEFT/RIGHT is the exact opposite of CONCATENATE. The LEFT function extracts the specified number of characters from the cell on the left, while the RIGHT function extracts the specified number of characters from the cell on the right.
So let’s try to create a three-character abbreviation of a first name. Let’s use Ron as an example. This formula =LEFT(A8,3) will, as a result, give us… yeah, Ron. Alright, let’s go for some other example – Christina. Formula =LEFT(A6,3) will give us Chr.
Frequently Asked Questions about Microsoft Excel
Was this article helpful?
Support us to keep up the good work and to provide you even better content. Your donations will be used to help students get access to quality content for free and pay our contributors’ salaries, who work hard to create this website content! Thank you for all your support!