Microsoft Excel Professional 2021 Guide: A Complete Excel Reference, Loads of Formulas and Functions, Shortcuts, and Numerous Screenshots to Become an Excel Expert (English Edition)

By CA Manmeet Singh Mehta

Do you struggle with handling Excel data?

Do you waste a lot of time figuring out which formulas and functions to use for your day-to-day office work?

Even so, do you still want to become a master of Excel's most advanced features and techniques?

This book will teach you the fundamentals and most beneficial uses of Microsoft Excel through various examples, practical illustrations, and good shortcuts to make your life easier with it. To begin, we'll look at the numerous features every professional needs to manage enormous volumes of data. The reader will discover how to import data from various sources and cleanse and arrange it.

You'll learn how to use Excel's formulas and functions, as well as how to analyse data in a variety of ways. This book will demonstrate how Excel's new capabilities combine AI and ML to improve and simplify data visualisation straightforwardly. This book will teach you how to solve business problems using Excel's built-in functions.

In this book, you will learn:
● Start with the essential Excel skills and gradually learn the sophisticated formulas.
● Discover how to quickly and easily import data from various external sources and formats.
● Eliminate complex data structures, adjust formats, and enhance data visibility.
● Discover hundreds of shortcuts, do complex computations, and study intelligent visuals.
● Learn how to use PivotTables, What-If Analysis and Array functions to generate valuable reports and dashboards.

What makes this book your go-to Excel skill guide?
● Excel techniques and formulas.
● Several practical examples that make day-to-day job activities easier in minutes.
● With only a few clicks, you can learn to filter, manage, and analyse your business data.
● High-quality images and screenshots help you choose the correct tool in seconds.

And there are many more to explore in this book.

• Language: English
• Publisher: BPB Online LLP
• Release date: Sep 13, 2022
• ISBN: 9789355510464

Book preview

CHAPTER 1

What is New in Excel 2021

Introduction

In this chapter, we will discuss Excel, which is the world’s most utilized spreadsheet program and part of the Microsoft Office suite. Excel is ever evolving, and Excel 2021 offers all of its earlier features and more, as the software allows users the opportunity to complete countless different tasks relating to spreadsheets. In this chapter, we will discuss the new features in Excel 2021 that facilitate collaboratively working with others and analyzing data easily with new Excel capabilities like co-authoring, dynamic arrays, XLOOKUP, XMATCH and LET functions apart from increasing productivity and speed.

Structure

In this chapter, we will discuss the following topics:

Evolution of Excel

What’s new in Excel 2021

Co-authoring

Collaborations through comments

Visual update

Sheet views

Accessibility ribbon

Unhide many sheets

Microsoft search

XLOOKUP

LET function

XMATCH function

Dynamic arrays - FILTER, UNIQUE, SEQUENCE, SORT, SORTBY, RANDARRAY

Objectives

After reading this chapter, you should be able to understand the evolution of Excel and how Excel in its latest version, Excel 2021, has many new features. You should also learn about new features like co-authoring and collaboration through comments, along with new functions like XLOOKUP and dynamic array functions, improved sheet views and navigation features.

Introduction

Microsoft Excel is the most familiar, flexible, and widely used business application in the world due to its capability to adapt to almost any business process.

- The History of Microsoft Excel

In addition to being the world’s most widely used spreadsheet tool, Excel is also a part of the Microsoft Office suite. While other spreadsheet tools like Google Sheets, and OpenOffice Calc can be used to create spreadsheets, Excel has been the worldwide standard for many years. Our daily professional job will almost certainly require the use of Excel, no matter what industry we are working in. Excel is a user-friendly tool; the top has a toolbar with tabs, while the bottom has a grid-like structure.

From a purely functional standpoint, Excel is a program that allows us to enter numerical data into the electronic spreadsheet to do one or more mathematical calculations. In the end, these calculations transform the quantitative information into data. When working with Excel, we can analyze and present massive amounts of data in a clear and concise manner.

Excel’s appeal stems from the fact that it is so adaptable and customizable and can be used for various things:

Calculations: What-if analysis, calculating financials, and a wide range of other financial analysis may all be done with the help of financial modelling tools in Excel.

Clean data: Clean data, that is in the form of text.

Use Tables: Use the row-and-column layout to efficiently tabulate data.

Graphical presentations: Produce a wide range of charts with a great degree of customization.

Accessing source data: Linking or importing data from a wide range of sources is one way to gain access to a variety of data.

Creating visualisations: Create a dashboard to display business data.

Diagrams: Using Shapes and SmartArt, we can design visually appealing diagrams.

Macros: Many monotonous jobs can be automated with Macros.

Everything about Excel will be covered in this book, including the new features added to Excel 2021.

Evolution of Excel

Visible calculator (VisiCalc) was the first spreadsheet application built in 1979 by Dan Bricklin, who is often called as the "father of the spreadsheet" alongside Bob Frankston.

Lotus 1-2-3, developed by Lotus Corporation, was the next widely used spreadsheet application.

Excel’s history dates to Excel 1, which was launched in 1985 for Macintosh and Excel 2, which was released in 1987 for Windows.

From 1985 till date, there have been several Excel versions to pick from. But what makes this achievement even more remarkable is that we’re discussing a software that has been around for more than three decades now. And despite the multiple revisions and introduction of more than 400 functions, it is leading, with Excel 2021 as the newest avatar.

Software as a Service (SaaS) a strategy where software is rented rather than sold and has been adopted by Microsoft. However, some customers still favour the "buy once, use forever" concept, especially those who are regulated enterprises, even government offices whose processes and programmes cannot be modified regularly. Microsoft recognises this and has released the perpetual licence version Excel 2021, which includes many new cutting-edge capabilities that we will examine in this chapter and throughout the book. Excel 2021 for consumers and Excel LTSC for businesses are the two versions of Excel 2021 developed by Microsoft.

It goes without saying that Microsoft 365 is always the latest and most powerful version of Excel that we can use, and it is accessible on a monthly subscription model rather than permanent licences.

New in Excel 2021

Several new features in Excel 2021 make it an excellent choice for those just getting started with spreadsheets. Excel 2021 has a lot of new features, and we’ll explore them all in this chapter, like new User Interface (UI), new Accessibility features, new Search box, new evolved Functions, and Dynamic Arrays. In Excel 2021, most old functions can be utilised in whole new ways, and several old functions have also been made compatible with arrays. Excel 2021 has also added a few new and evolved functions.

Co-authoring

In Excel, multiple users can now edit a document stored on OneDrive or SharePoint at the same time, and we’ll see icons for each person who is on the file with their photo or initials. Co-authoring is where we and our team may now open and work on the same Excel spreadsheet at the same time. When one person makes a modification, the other can see it instantly, and the other person’s working will appear in a different color.

To get started, the Workbook must be saved on OneDrive, or a SharePoint Online library if co-authoring is desired. We can quickly upload it using a web browser.

In the upper-right corner, select Share, enter Email addresses; if entering multiple email addresses, separate them with a semicolon in the address box. Add a message and click on Send, as shown in Figure 1.1:

Figure 1.1: Share with co-author through a link on email

Pro-Tip: We can also copy the link by selecting copy link at the bottom of the pane in the toolbar and send it to anyone.

By default, the workbook can be edited by anyone who has access to it, but we may restrict this by selecting the edit option.

The receiver will be sent an email with a link to open the file. They can open the workbook by clicking on the link. Excel for the web will open in a web browser, and the workbook will appear. They can pick Edit in Desktop Software if they prefer to use the Excel desktop app to co-author.

File should be opened in AutoSave mode on. Once the file is opened by others, we’ll all be co-authors of the document together. In the upper-right corner of the Excel window, we can see images of persons or their initials, or Guest Contributor (GC), as shown in Figure 1.2:

Figure 1.2: Co-Authoring with GC (Guest Contributor)

Co-authoring in Excel spreadsheets is a fantastic new feature to work as a team and or complete a project.

Comments

The way comments are handled in Excel 2021 has changed; they are different from notes called comments in earlier Excel versions. With comments that are threaded now it is possible to conduct a discussion about the data with others, accordingly, reply boxes appear in comments. When others respond, we can see a virtual conversation taking place in the workbook as multiple comments are linked together. We can use a comment to discuss data with others.

Right-click on a cell and select New Comment from the context menu that appears, and write the comments in the box provided.

Comments disappear when we move the mouse away from a cell, but the purple comment indicator remains visible. The comment can be viewed again by hovering over the cell, as shown in Figure 1.3:

Figure 1.3: Right Click to type a comment and others reply

It is also possible to manage comments via the Review tab | Comments group.

We choose to display the comments by selecting the Show Comments option. On the right, a new Comments task pane will open, showing all our comments in chronological order.

A comment can be deleted from a thread by clicking on the More actions button (…) in the comment’s top right-hand corner and selecting Delete thread; in addition, a thread can be resolved using this feature. Refer to Figure 1.4:

Figure 1.4: Comments Group under Review Tab

Visual update

As a result of a recent visual update, Excel 2021 now features rounded window corners and the choice of a dark theme. If we see a ‘megaphone’ icon in the upper-right corner of our Excel window, we know we’ve received the visual upgrade, as shown in Figure 1.5. The Visual Update is enabled via a Coming Soon feature in this update. Take a look at the following screenshot:

Figure 1.5: Visual Update with rounded corners

Sheet views

We often collaborate on spreadsheets with our teammates and work on them at the same time. The ease of working together in Excel is sometimes matched by the potential for distraction. When we’re attempting to focus on anything, it’s easy to get distracted by the other person’s constant changes to the content of a worksheet.

Excel’s Temporary View option might assist avoid confusion when working with others. In this approach, we can focus on the data that we want to analyze while working together.

We may now construct our own Views in Microsoft Excel and not be bothered by anyone else doing the same. To differentiate these views, they are referred to as Sheet View. For instance, we could construct a filter that, when applied to a worksheet, would only show the data that is relevant to us and would not be affected by the actions of other users working with the same document. Multiple sheet views can also be put up on a single sheet in the Worksheet. We may store these temporary views by selecting the View Name from the Sheet View drop-down menu and typing a name.

Here is an example of a situation where we want to filter the list, but we do not want any of our co-authors to be distracted.

Once we’ve opened a shared Excel file, we’ll select the worksheet where we want to create a temporary view.

We will navigate to View tab and on the left side of the ribbon, go to Sheet View group. In order to create a new Sheet View, we click on the New button, as seen in the following figure. The filter we need can then be applied. Excel will name our new view Temporary View by default and will appear in the Sheet View dropdown menu as soon as we press the button. We’ll see this change immediately.

Our worksheet’s headers will turn black when we view it in a temporary view. It’s worth noting that when we create a New Sheet View in Excel, Excel keeps our initial view and sets it to default.

The Temporary View mode allows us to work on the sheet uninterrupted by other users and enables us to make modifications and analysis as if we were the only person working on the page at the time.

If someone tries to update the filter on a worksheet that we’ve shared with them, they’ll be prompted to use a sheet view instead. Refer to Figure 1.6:

Figure 1.6: Creating a Temporary Sheet View

When in Sheet View, there will be an eye symbol next to Worksheet Name at the bottom of the screen. The current Sheet View name will be displayed if we hover over it.

Figure 1.7: A Sheet view indicated by eye symbol next to Sheet name

To save the view we can navigate to View | Sheet View | Keep. The first sheet’s view will be saved as View1; click on Exit. Once we create a Sheet View, we can then click on the command Options to the left corner of the Sheet View group. This will open an Options Dialog box, through which we can Rename the Sheet View, Duplicate the Sheet View or even Delete the Sheet View. To move to a different sheet view, we simply click on the Switch To button at the bottom of the dialogue box. Take a look at the following screenshot:

Figure 1.8: Saving a sheet view

Any Sheet View that we desire can be saved and used by anyone who has access to it. Others sharing the sheet can also develop their own views, which we can then access and utilize as well. The views we’ve developed can be found in the Sheet Views group of the View tab.

Sheet Views is a splendid feature with many benefits:

As many as 256 temporary sheets views can be created.

Everyone can see any changes made to a single cell in the Sheet View as soon as they are made.

The Sheet View’s drop-down menu only displays sheet views that are currently open on the current sheet.

Even when we’re not collaborating with others, we can use this functionality to create our own custom views.

Once we’ve saved the custom view data, switching between Sheet Views is easy.

Using Sheet Views, we may adjust our views on a worksheet while working with others without having to worry about other users interfering with our work.

Using Sheet View, we may remove unnecessary data and present only the relevant information. Without affecting each other’s sheet operations, others can also do the same.

Accessibility ribbon

The Accessibility ribbon contains all the tools we need to create accessible Excel worksheets so that everyone, including those with impairments, can access our content.

We can select Check Accessibility from the Review tab, as shown in Figure 1.9:

Figure 1.9: Check accessibility under Review Tab

This will bring up the Accessibility ribbon as shown in the following figure 1.10:

Figure 1.10: Accessibility Ribbon

Before distributing or saving a document to a public site, we can ensure that it is accessible.

Look for possible accessibility issues in our material using the Accessibility ribbon.

Alt Text descriptions or easy to understand names of images and charts should be added.

To make our data more understandable, unmerge the cells.

Correct issues with color contrast.

Make use of pre-built styles (for example, tables) with high contrast.

An Accessibility Checker is also located in Status bar, which tracks the accessibility issues alerts us when it detects something we should take a closer look at as shown in Figure 1.11:

Figure 1.11: Accessibility Checker in Status bar

Unhide multiple sheets at once

In Excel, the option to hide or unhide a worksheet is a useful tool. Unhiding two or more sheets at once was not an option in earlier versions of Excel.

In Excel 2021, we’ll be able to simultaneously unhide multiple sheets. Select Unhide by right-clicking on the Sheet tab in the bottom menu bar.

Press the Ctrl key and select the sheets we want to display from the Unhide dialogue box.

To choose several (or all) worksheets, press the Shift + Up/Down Arrow keys, and click on OK, as shown in Figure 1.12:

Figure 1.12: Unhide Multiple Sheets at once

Premium creative content collection

In Excel 2021, there are thousands of royalty-free photos, icons, graphics, and more that we may utilise in our worksheets.

Simply select Pictures and then Stock Images from the Insert tab.

We can choose from the categories: Images, Icons, Cut-outs, Stickers, Illustrations, and Cartoon People, as shown in Figure 1.13:

Figure 1.13: Premium Creative Content collection in Stock Images

New Microsoft Search box

More powerful than we can imagine is Excel 2021’s brand-new Microsoft Search box. Searching for information, files, tips, photos, or people is a breeze now that we have this handy search box.

If we click inside the Search box or press Alt+Q, a dropdown menu will show us recent searches and Suggested Actions based on what we seem to be doing and the People we often collaborate with, as shown in Figure 1.14:

Figure 1.14: Recent Suggested actions & People

When we start typing one letter at a time, the suggestions will start repopulating. We can enter into the search box a single word or phrase, for example pivot; we’ll get relevant suggestions, as shown in Figure 1.15:

Figure 1.15: Various suggestion based on a word or phrase

Just think about how much time we waste when trying to locate a file or piece of data. With the use of Search, this task becomes a piece of cake.

There are several standard features in the search box that are shared by all of Microsoft Office applications.

Phrases or sentences: We can use the search box to enter a single word or a phrase and get several results that are specific to the context, like "Pivot" in the preceding example.

Machine Learning: The Search box will learn our behavior and preferences and provide us with more relevant results based on what we search for.

Tips: To find out how to do something in an application, just key in a word linked with the action we’re looking for. We’ll be able to get some help in the form of a document or a video.

Search Internet: Enter a search term into the Excel’s search field and it will populate with relevant information as well as Bing search results that will open in a browser if we click on them.

Definitions: When we type a word, we get the meaning as a response.

Access files: We can obtain data from various sources. We can find a file if we provide its name. Inserting the file or copying information from it is as simple as dragging and dropping it into our existing workspace.

Sharing: We can share a link to our current file with colleagues by typing Sharing into the Search box.

Suggested Actions: We can use these context-specific shortcuts to help us complete recommended actions more quickly and efficiently.

Access Settings Quickly: We don’t need to spend a lot of time searching for a setting or tool if we just type in a letter in the search box.

Use the Microsoft Search Box to work faster and increase productivity.

AutoSave

To ensure that all our changes are automatically saved in Excel 2021, we can activate AutoSave and we’ll be able to upload the files to OneDrive and SharePoint Online.

Draw tab

In Excel 2021, under the Draw tab, all the inking tools may be quickly accessed in one spot. The new Draw tab additions are Pens, Action pens, Point Eraser, Ruler, and Lasso, as shown in Figure 1.16. The drawing experience has become much better.

Figure 1.16: New Drawing Tools under the Draw tab

Workbook Statistics

Excel 2021 offers us with Workbook Statistics that is data about our spreadsheet, including the number of sheets, tables, formulas, charts and PivotTables. Select Workbook Statistics from the Proofing group of the Review tab.

The Workbook Statistics dialogue box appears and provides current Worksheet and Workbook Counts and other statistics. Take a look at the following screenshot:

Figure 1.17: Workbook Statistics

XLOOKUP function

In Excel 2021, the XLOOKUP tool is quite versatile. XLOOKUP has resolved the issues with VLOOKUP and HLOOKUP.

XLOOKUP can search vertically and horizontally, to the left and above, and even return an entire column or row of data rather than just one value.

The XLOOKUP function is better than VLOOKUP, and it is an alternative to many of the workarounds we had to use to get VLOOKUP to work in certain situations (INDEX, MATCH, IFERROR, CHOOSE, and so on.)

There are many advantages to using XLOOKUP instead of the VLOOKUP because of the way the arrays are separated. It is now possible to place the lookup array either to the left or right of the return array. Since XLOOKUP lets us search from bottom to top, it’s easy to find the last value in a list and get the value from a column that matches it.

For detailed discussion on XLOOKUP, refer to Chapter 10, Logical and Lookup Functions.

LET function

By using Excel 2021’s new LET function, we can make our formulas more readable while also speeding up their execution. If the same expression (e.g., a formula or value) is used more than once in a formula, this function is useful.

The LET function assigns name to the results of calculations. We can now store values, names, and intermediate calculations inside of a formula. However, apart from the scope within LET function, these names are meaningless. Like variables in programming, Excel’s native formula syntax is used to implement LET.

For a detailed discussion on LET, refer to Chapter 10, Logical and Lookup Functions.

XMATCH function

The XMATCH function is like MATCH, but it is much more versatile. It can search in both vertical and horizontal arrays, search first-to-last or last-to-first, find exact, approximate, and partial matches, and employ a faster binary search algorithm. We can also use XMATCH to return a value in an array.

For a detailed discussion on XMATCH, refer to Chapter 10, Logical and Lookup Functions.

Dynamic arrays

Even though Excel has been evolving, but One cell, One formula has always been at the heart of Excel. Traditional array formulas and Control Shift Enter (CSE) formulas both required us to select a range and, in a sense, enter a formula into each cell where we wanted a result to appear.

Since the advent of Dynamic Arrays in Excel 2021, this rule has been rendered obsolete entirely. The term "Spilling" refers to the automatic spilling of values into neighboring cells that occurs whenever a formula returns an array of values.

This long-awaited and much-welcomed change to Dynamic Arrays is well worth the wait.

Using Dynamic Array formulas is something that every Excel user can understand easily and enjoy because of their ability to handle multiple values in a straightforward manner.

We can now create a Dynamic Array of values from a single function. Six new functions have been added to Excel 2021 that work with arrays natively and output data to a range of cells as part of the new functionality. The output is always dynamic, and it updates automatically whenever the source data changes. The group’s name reflects this Dynamic Array functions. These functions are FILTER, UNIQUE, SEQUENCE, SORT, SORTBY, RANDARRAY.

For a detailed discussion on dynamic arrays, refer to Chapter 11, Array Formula and Functions.

Conclusion

This chapter discussed Excel, the most popular spreadsheet application. Excel is constantly evolving, and Excel 2021 offers several new features. In this chapter, we learned about new collaboration tools such as Co-Authoring, which can be used to share an Excel file with co-authors. We also explored comments, which aid in virtual conversations with workbook users. Sheet Views is a fascinating feature that allows us to keep our preferences even when sharing a workbook. The Accessibility Checker and related features aid in the detection of issues that users may have when sharing files. We were also introduced to Dynamic Arrays, XLOOKUP, XMATCH, and LET Functions, all of which will be covered in further detail in the subsequent chapters.

In the next chapter, we’ll cover the basics of Excel. This will introduce Excel and provide a demonstration of how to work with ribbons, including the Quick Access Toolbar, which provides quick access to its commands. Excel is incredibly adaptable. We’ll go over the Copy and Paste and Paste Special options.

Tips

When we collaborate as co-authors, other people’s selections will be displayed in various shades of blue. We will, however, always have green selection. On the displays of other people, their selections will be green. If we lose track of who they are, we can see their name by hovering the cursor over the selection. Select the person’s photo or initials, and then click on the Go To option if we want to go directly where they’re working.

CHAPTER 2

Excel Basics

Introduction

In the previous chapter, we learned that Excel is an amazing tool. Excel is ever-evolving, with many new features being added in new versions.

Masters learn the fundamentals first, and in this chapter, we will learn the fundamentals of Excel. This chapter provides an overview of Excel and demonstrates how to use Ribbons. This includes our toolbar for quick access. Excel is extremely customizable. We’ll go over the options for copy and paste, and paste specials.

Structure

In this chapter, we will cover the following topics:

Basic screen overview

Data types, including new rich data types

Ribbon and customize Quick Access tool bar

Copy, paste, and formatting

Find and select-Go to Special

Managing worksheets, rows and columns

Objectives

In this chapter, we will learn the fundamentals of Excel as well as a basic overview of the screen, including how to use Ribbons. We will also learn how to use the Quick Access Toolbar to boost productivity.

We will go over different data kinds as well as new rich data types like stocks and geography. Excel comes with a plethora of customization choices. Throughout the process, we will learn about copy and paste, and the numerous options available in paste special.

Basic screen overview

Let’s get acquainted with the Excel environment and some Excel fundamentals before diving into sophisticated formulae and functions.

Whatever work we complete in Excel is saved as a workbook file. A workbook can be thought of as a book holding many sheets of paper, and just like a book, a workbook might have multiple worksheets. In that book, a worksheet is like a single sheet of paper.

Depending on our needs, we can open many workbooks, each of which shows in its own window. By default, Excel workbooks have the .xlsx file extension; however, additional extensions such as .xlsm (Macro enabled workbooks) or .xlsb (Excel binary workbook) may exist.

A workbook may contain multiple worksheets; a worksheet is a name given to a grid on which we can perform calculations. Each worksheet contains 16,384 vertical groupings identified by alphabets (A - XFD) and 1,048,576 horizontal groupings. As a result, each worksheet has 17,17,98,69,184 individual cells. This is predetermined and cannot be changed. The exact number of cells displayed at any given time is determined by a variety of factors, such as screen size, screen resolution, and video driver. A cell is the intersection of a row and a column and is identified by its unique address, which is composed of the first column letter followed by the row number, for example, A1. Each cell can have a value, a formula, or text in it.

The worksheet can also contain illustrations such as images, shapes, SmartArt, and charts. By clicking on the tab at the bottom of the workbook window, we can open the worksheet in the workbook.

Excel is a large program with hundreds of menu options and functions, and it’s easy to become lost in it. The first thing we’d like to learn is how to browse Excel and use its many functions.

Parts of the Excel screen

Figure 2.1 shows some important parts of the screen:

Figure 2.1: Parts of an Excel screen

Primarily, we can categorize it into five parts:

Quick Access Toolbar: Where all the important commands that we quickly require can be placed.

Ribbon: It is an expanded menu.

Formula bar: It is where any calculations or formulas we write will appear.

Spreadsheet Grid: It is where all our numbers, data, illustrations, and charts and drawings are placed.

Status bar: It informs us of what is happening in Excel at any time.

Title bar

The title bar displays the name of the program as well as the current workbook. It also contains the Quick Access toolbar (on the left) and a few control buttons for customizing the window’s layout