## 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.

**Word**. For creating documents, flyers, publications and PDFs.

**PowerPoint**. For creating eye-catching presentations.

**OneDrive**. For online data storage.

**OneNote**. For collecting notes – both handwritten and typed, as well as drawings, screen captures, audio clips, videos and more.

**Publisher**. For creating professional publications, posters, flyers and menus.

**Outlook**. For managing email, calendars, to-do lists and contacts. Essential for everyone, who means it seriously with their business.

**Access**. For creating databases and compiling and organising large amounts of data.

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.

### Google Spreadsheets

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

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.

**Cell**. A cell is the intersection of a column and a row into which the user enters data.

**Cell reference**. This is a cell reference that is controlled by a column (denoted by letters starting with A) and a row (denoted by numbers starting with 1).

**Active cell**. The cell that is currently selected is active. You can easily recognize it by the green outline.

**Copied cell**. Excel will show you which of the cells you last copied is still in memory. Such a cell is highlighted with a dashed green outline.

**Workbook**. Workbook refers to an entire document in which you have multiple sheets.

**Worksheet**. Within the Workbook, you have several sheets, called worksheets.

**Worksheet tab**. A worksheet tab refers to a specific sheet on which you have your data.

**Column and row headings**. Columns are labelled in writing, rows are labelled numerically. This makes it easy to know which cell contains what data.

**Formula**. Formulas are mathematical calculations, and references to other cells or functions. All formulas begin with the = sign. If you want your cell to start with an = sign but not be a formula, then start the cell with an apostrophe, for example: ‘=5 will give you the text “=5” in the result.

**Formula bar**. A longer command bar is displayed above the table in case you have a more complex formula and the display directly in the cell becomes cluttered.

**Address bar**. The coordinates to the cell appear next to the active cell. This is much clearer than tracking it down in Column and Row headings, even though you have the active row and column highlighted there as well.

**Filter**. Filter is a feature that makes working with Excel much easier because it allows you to sort data and set different conditions for sorting or displaying it.

**AutoFill**. AutoFill is a great feature to make your job easier if you want to create a row or column with predictable data. You don’t have to type numbers from 1 to 1000 yourself – you can type the first two, select those cells, and then drag to expand the list. Excel will fill in the remaining data itself. And it doesn’t just apply to a list like this – for example, you tell Excel that you want only odd numbers by the numbers 1 and 3, and it will continue to do so. Excel can also do this with dates and months. And much more.

**AutoSum**. AutoSum is a feature that many people may not know about – and that’s a big mistake. Just select the cells you want to add, and then press Alt and = and the cells will add themselves.

**PivotTable**. Below the list of worksheets in the Workbook, a line is displayed here about the current worksheet.

**PivotChart**. This is a graphical representation of the PivotTable.

## 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.

**CTRL + A**: Select All

**CTRL + B**: Convert font to bold

**CTRL + C**: Copy

**CTRL + D**: Fill down

**CTRL + E**: Flash fill

**CTRL + F**: Find

**CTRL + G**: Go to

**CTRL + H**: Find and replace

**CTRL + I**: Convert font to italic

**CTRL + J**: Input line break (in find and replace)

**CTRL + K**: Insert hyperlink

**CTRL + L**: Insert Excel table

**CTRL + M**: Not assigned

**CTRL + N**: New workbook

**CTRL + O**: Open

**CTRL + P**: Print

**CTRL + Q**: Quick analysis

**CTRL + R**: Fill right

**CTRL + S**: Save

**CTRL + T**: Insert Excel table

**CTRL + U**: Convert font to underline

**CTRL + V**: Paste (only when something is cut/copied)

**CTRL + W**: Close the current workbook

**CTRL + X**: Cut

**CTRL + Y**: Redo (repeat last action)

**CTRL + Z**: Undo

**F4**: Lock the selected cell in a formula

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

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.

**=SUM(B3,B6,B10)**. The sum of these numbers is 125.

**=SUM(B2:B10)**. The sum of these numbers is 480.

#### COUNT

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.

**=COUNT(B3,B6,B10)**. The resulting number is 3.

**=COUNT(B2:B10)**. The resulting number is 9.

#### COUNTA

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.

**=COUNTA(D3,D6,D10)**. The resulting count is 3.

**=COUNTA(D2:D10)**. The resulting count is 9.

#### COUNTBLANK

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.

#### AVERAGE

The AVERAGE formula returns the arithmetic average of the specified cells.

Formula used: **=AVERAGE(B2:B10) **→ result is 43.22.

#### MEDIAN

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.

#### MODE

The MODE formula returns us the most common number in the specified range.

Formula used: **=MODE(B2:B10)** → result is 42.

#### MIN

The MIN formula returns the smallest number in the specified range.

Formula used: **=MIN(B2:B10)** → result is 37.

#### MAX

The MAX formula returns the highest number in the specified range.

Formula used: **=MAX(B2:B10)** → result is 75.

#### LEN

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.

#### IF

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

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!

#### COUNTIFS

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

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

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

**What does Microsoft Excel do?**Microsoft Excel is a spreadsheet editor that allows you to sort data and apply advanced mathematical formulas to it. You can also create professional graphs with it thanks to advanced visualisation techniques.

**How much does Microsoft Excel cost?**Excel is part of the Microsoft Office suite. A personal annual license costs $69.99, and a group license (for up to six people) costs $99.99. A one-person monthly license costs $6.99, and a monthly license for a group of up to six costs $9.99.

**Are there free alternatives for Microsoft Excel?**Yes. Google Spreadsheets, Microsoft Excel Online, Apple Numbers or LibreOffice Calc, for example.

**What operating systems can I run Microsoft Excel on?**You can run Microsoft Excel on Windows, macOS, Android, and iOS devices. Excel is not officially supported on Linux operating system.

**How to learn Excel?**To get started, it’s a good idea to learn the terminology, keyboard shortcuts and most useful functions.

## 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!

## Reaction to comment: Cancel reply