OceanofPDF.com

Excel 2021

A Step-By-Step Guide for Beginners to Learn

Valuable Excel Skills, Improving Their Skillset and

Work-Efficiency with Excel 2021’s New Features

ROBERT J. GREEN

OceanofPDF.com

© Copyright 2021 by (ROBERT J. GREEN) - All rights reserved.

This document is geared towards providing exact and reliable information regarding the topic and issue covered. The publication is sold with the idea that the publisher is not required to render accounting, officially permitted, or otherwise, qualified services. If advice is necessary, legal, or professional, a practiced individual in the profession should be ordered.

- From a Declaration of Principles which was accepted and approved equally by a Committee of the American Bar Association and a Committee of Publishers and Associations.

In no way is it legal to reproduce, duplicate, or transmit any part of this document in either electronic means or in printed format. Recording of this publication is strictly prohibited and any storage of this document is not allowed unless with written permission from the publisher. All rights reserved.

The information provided herein is stated to be truthful and consistent, in that any liability, in terms of inattention or otherwise, by any usage or abuse of any policies, processes, or directions contained within is the solitary and utter responsibility of the recipient reader. Under no circumstances will any legal responsibility or blame be held against the publisher for any reparation, damages, or monetary loss due to the information herein, either directly or indirectly.

Respective authors own all copyrights not held by the publisher.

The information herein is offered for informational purposes solely and is universal as so. The presentation of the information is without contract or any type of guaranteed assurance.

The trademarks that are used are without any consent, and the publication of the trademark is without permission or backing by the trademark owner. All trademarks and brands within this book are for clarifying purposes only and are the owned by the owners themselves, not affiliated with this document.

OceanofPDF.com

Table of Contents

Introduction

Chapter 1: What is Microsoft Excel?

1.1 Identifying What Excel is Good For?

1.2 Basic Functions of Microsoft Excel

1.3 History of Microsoft Excel

1.4 Features of MS Excel

1.5 Why Should You Learn Excel?

1.6 Where Can You Get Excel?

1.7 How Do You Open Excel?

Chapter 2: Understanding and Getting Started with MS Excel

2.1 Best way to use Microsoft excel

2.2 Create, Open and Save Workbooks

2.3 Enter, Edit and Delete Data

2.4 Customization of the Ribbon

2.5 Proofing setting

2.6 Excel’s Proofing Options

Chapter 3: Formulas and Features of MS Excel

3.1 Basic Terms in Excel

3.2 How to use Features and Formulas?

3.3 Time-saving Techniques to Enter Data into Excel

3.4 Fundamental Excel Formulas for Workflow

Chapter 4: MS Excel’s Text Function

4.1 CONCATENATE

4.2 PROPER

4.3 Trim

4.4 Len

4.5 Find ()

4.6 Search Function

4.7 Textjoin Function

4.8 Lower Function

4.9 Upper Function

4.10 Substitute

Chapter 5: MS Excel’s Counting and Logic’s Features

5.1 IF

5.2 MAXIMUM & MINIMUM

5.3 EVEN & =ODD

5.4 Not Feature

5.5 OR

5.6 AND

5.7 IFS FEATURE

5.8 Contact

5.9 Count

5.10 Countblank

Chapter 6: Conditional Features in MS Excel

6.1 AVERAGE IF

6.2 AVERRAGEIFS

6.3 SUMIFS FEATURE

6.4 MINIFY

6.5 COUNTIES

6.6 AVERAGE

Chapter 7: Functions of Date and Time, Entering and Managing Data

7.1 Weekday Function

7.2 Date Function

7.3 Edate Function

7.4 Datedif

7.5 End of Month

7.6 Today & Now

7.7 Network Day

7.8 Work Day Function

7.9 Entering Data

7.10 Editing Data

7.11 Auto Fill

7.12 Moving Data

7.13 Adjusting Columns and Rows

7.14 Hiding Columns and Rows

7.15 Inserting Columns and Rows

Chapter 8: Excel Problems and their Solutions with Tips and Shortcuts

8.1 Excel Security

8.2 Hashes in Cell

8.3 Solution

8.4 Copy Formula

8.5 Fill in the Cells Rapidly

8.6 Choose to Remove Duplicate Key

8.7 Fast Aggregation

8.8 Formula View

8.9 IF Error

8.10 IFNA

8.11 Importing Tables from the Internet

8.12 Excel Shortcuts

8.13 Tips and Techniques for Excel 2021

Chapter 9: Charts, Graphs and Pivot Table in MS Excel

9.1 Creating Pivot Table

9.2 Filtering

9.3 Change Summary Calculation

9.4 Sorting Data by Specific Trait

9.5 Fine Tune the Calculator

9.6 Macros

9.7 Importance of Pivot Table

9.8 Chart and Their Types

9.9 The Pie Chart

9.10 The Line Chart

9.11 The Column Chart

9.12 The Bar Chart

9.13 The Scatter Chart

9.14 The Area Chart

9.15 Excel Graphs

9.16 Types of Excel Graphs

9.17 Creating Tables

Chapter 10: Named Ranges

10.1 Creating Named Range

10.2 Creating a name for a constant in Excel

10.3 Defining any Name for a Formula

10.4 Naming Rules

10.5 Advantages of utilizing Name

10.6 Tables

10.7 Creating Tables

10. 8 Adding Data Tables

10.9 Formatting Tables

10.10 Finding Data Tables

Chapter 11: Benefits, Applications and features of MS Excel 2021

11.1 Benefits of Using Excel

11.2 Business Uses

11.3 Features of Excel 2021

11.4 Worksheet Navigation

11.5 Zoom Out/IN

11.6 Excel by Default Keyboard Shortcut

Conclusion

OceanofPDF.com

Introduction

Microsoft Excel is a spreadsheet application developed by Microsoft that enables people to manage, arrange, and compute data using formulae. This program is part of the Microsoft Office suite but also is interoperable with other Office apps. Like many Microsoft Office applications, Microsoft Excel is now available as a cloud-based subscription via Office 365. MS Excel is a professional spreadsheet program developed by Microsoft that was released for the Microsoft Windows and Mac OS operating systems. It includes, among other things, the capacity to do simple arithmetic, utilize graphing tools, build pivot tables, or construct macros.

To organize and manage data, spreadsheet programs like MS Excel utilize a set of cells organized into rows and columns. They can also use charts, histograms, and line graphs to show data. MS Excel allows users to organize data in order to see various aspects from multiple angles. Microsoft Visual Basic is a programming language that may be used to build a range of sophisticated numerical techniques in Excel. Developers have the option of creating code directly in the Visual Basic Editor, which includes Windows for troubleshooting and organizing code modules. History and Future of MS Excel

For data analysis and documentation, Microsoft Excel is a useful and capable tool. It's a spreadsheet software with several columns and rows, with each intersection of a column or a row being referred to as a "cell." Each cell carries a single piece of data or information. You may make information simpler to locate and automatically pull information from moving information by arranging the data in this manner. Microsoft Excel played a critical role in accounting and record-keeping for company operations in the initial periods of accessible PC business computing. A table with an autosum format is one of the finest examples of an MS Excel use case. Entering a column of data and clicking into a cell at the end of the spreadsheet, as well as using the "autosum" option to enable that column to add up all of the values input above, is extremely simple with Microsoft Excel. This replaces manual ledger counts, which were a time-consuming aspect of business prior to the development of the contemporary spreadsheet.

MS Excel has become a must-have for different types of corporate computing, like looking at daily, weekly, or monthly figures, tabulating payroll or taxes, and other comparable business operations, thanks to the autosum and other improvements. Microsoft Excel has become a major end-user technology, helpful in training and professional development, thanks to a variety of easy application cases. MS Excel has been included in basic business diploma courses on business computers for a number of years, and temporary employment agencies may evaluate people for a variety of clerical tasks based on their abilities with Microsoft Word and Microsoft Excel.

Microsoft Excel, on the other hand, has become entirely outdated in certain respects as business technology has progressed. This is due to a notion is known as "visual dashboard" technology, sometimes known as "data visualization." In general, businesses and suppliers have devised innovative new methods to graphically display data that do not need end-users to examine a conventional spreadsheet with columns of numbers and IDs. Instead, they use graphs, charts, and other complex displays to better grasp and comprehend the data. People have learned that "reading" a visual presentation is much simpler.

The application cases for Microsoft Excel have changed as a result of the data visualization concept. Whereas in the past, companies may have used Microsoft Excel to manage hundreds of entries, today's commercial use cases often include spreadsheets that handle just a few dozen variables for each project. If the spreadsheet has more than a few dozen rows, the information will be more effective shown on a visual dashboard than in a conventional spreadsheet style.

Spreadsheets, second only to word processors, have become one of the most common types of computer software. Data, mathematical formulae, text, and images may all be combined in a single report and workbook using spreadsheet software. As a result, spreadsheets have become essential commercial tools, as well as being widely used in scientific research. Excel, in particular, has received widespread praise for its simplicity of use and capability. As spreadsheets' power and simplicity of use have grown, there has been a surge in interest in utilizing them in the classroom. Due to the widespread availability of spreadsheet software at colleges and institutions, a statistics teacher may teach a course without asking students to buy extra software. It would be dishonest not to add a few cautions now that we've highlighted Excel's benefits for teaching fundamental statistics. Spreadsheets are not statistics programs. Therefore, their ability to replace a full-featured statistics program is limited. Equitable two-way analysis of variance is simple in Excel, while unequal two-way analysis of variance is difficult. Spreadsheets also have limitations when it comes to processing data with null values.

Who is this book for?

This book is intended for home and corporate users of Microsoft Office applications who wish to utilize Excel to organize their data, produce meaningful studies and visualizations, as well as uncover information into their processes utilizing Excel's extensive business intelligence analytical capabilities. The book's material is intended for individuals who already have already utilized older versions of Excel as well as those who are learning Excel for the very first time.

OceanofPDF.com

Chapter 1: What is Microsoft Excel?

Microsoft Excel is spreadsheet software that is part of the Microsoft Office suite. You may download the program to your hard disc and also use the online version with Office 365. The online version allows you to share and work on your files with others instantaneously. Spreadsheets are rows and columns of data that may be modified numerically using both simple and sophisticated mathematical operations and functions. The software is compatible with a variety of operating systems, including Windows, macOS, smartphones, as well as tablets.

What may Microsoft Excel be used for?

Data in Excel,.csv,.txt and .ods formats may be imported, exported, and converted.

Develop your own style by using formatting and modifying formulas to conduct computations on your data.

While using the online version, chat in real-time with others who are updating your spreadsheet.

Use your data to make graphs.

Add a spreadsheet and single spreadsheet sheets; to your website or blog.

1.1 Identifying What Excel is Good For?

Microsoft Excel is extensively used these days by everyone since it is extremely useful and saves time. It has been in use for several years and is updated with new capabilities every year. Microsoft Excel's most remarkable feature is this software could be utilized anywhere and for any type of task. It's utilized for things like billing, database administration, inventory, analysis, finance, business activities, and complicated computations, among other things. It may also be used to do mathematical computations and to store significant data in the shape of worksheets and graphs.

Microsoft Excel protects your files, ensuring that nobody else may access or corrupt them. You may password-protect your files with the assistance of Microsoft Excel. Microsoft Excel may be approached from any location and at any time. If you don't have access to a laptop, you may use your phone to complete your task on Microsoft Excel. Microsoft Excel has so many advantages. This software is an unavoidable part of thousands of people's lives. Microsoft Excel offers a variety of functions and attributes that make work easier and save time. To get the most out of Microsoft Excel, you must first understand its perks and advantages. The most effective applications of Microsoft Excel are as follows:

Data analysis and storage

The most useful features of Microsoft Excel are the ability to analyze huge quantities of data in order to spot patterns. You may summarize data and save it in an orderly manner with the assistance of charts and spreadsheets so that you can quickly access it whenever you need it. This software becomes simpler to save data, and you will save time as a result. Data may be utilized for a variety of reasons after it has been saved in an organized manner. Microsoft Excel constructs it simpler to do different operations on data by providing a variety of tools.

Microsoft Excel functions make tasks easier.

Microsoft Excel has a plethora of features that make the job a lot easier and save your time. There are fantastic tools for filtering, sorting, and searching that make your job even easier. You can complete your job in much less time if you mix these features with pivot tables, tables, and other functions. Multiple components may be readily found in huge quantities of content to assist in the resolution of a variety of issues and queries.

Spreadsheets and recovery of data

One of the advantageous features of Microsoft Excel is that whenever your content is lost, you may easily restore it. If a businessman has vital data saved in Microsoft Excel, if it is lost or the file is destroyed, he need not worry since the new Microsoft Excel XML format may be used to recover the deleted or destroyed file data. The second essential purpose is that Microsoft Excel spreadsheets make your job easier, and you may decrease the length of the worksheet or make things simple and easy while using the new Microsoft Excel XML format.

Microsoft Excel math formulas make things simple.

Another greatest usage of Microsoft Excel is that it allows you to tackle difficult mathematical issues in an easier and less time-consuming manner. There are numerous formulae in Microsoft Excel; while utilizing them, you can do a variety of operations on a big quantity of data at once, such as calculating the sum, average, and so on. As a result, Microsoft Excel is used anytime individuals need to solve complicated mathematical issues or apply basic mathematical functions to tables with a lot of data.

Security

Microsoft Excel secures Excel files, allowing users to keep their data safe. By basic visual programming and directly inside the excel document, all Microsoft Excel files may be password-protected. People save their essential data in Microsoft Excel because they want to keep it structured and save time. Almost everyone wants their documents to be passcode secured so that nobody can access them or damage them, and Microsoft Excel is an excellent solution to this issue.

Data presentations with sophistication

The next benefit is that Microsoft Excel allows you to add more complexity to your forecasting, which implies you can enhance the data charts, emphasize any particular things you need to emphasize, and simply turn your content better appealing.

If you have data saved in Microsoft Excel, but you wish to emphasize something essential, you may do it using the different data visualization tools provided in Microsoft Excel. Moreover, you can even create the spreadsheets on which you've put data more appealing.

OceanofPDF.com

Online availability

The advantage is that Microsoft Excel can be used anywhere across and at any time, allowing you to utilize it via any machine and place. It allows you to work more conveniently, which indicates that whenever you don't have a laptop, you may use your phone to do your tasks quickly and simply. As a result of the extensive adaptability that Microsoft Excel offers, individuals choose to complete their job. In this way, they may focus on their job without being distracted by their device and location.

Stores data in one place

Microsoft Excel allows you to store all of your data in one place. This will assist you in preventing the loss of your data. It will store all of your content in the same location, so you won't have to spend time looking for files. As a result, you will save time and will be able to quickly search up the classified or organized data whenever you need it.

Help entrepreneurs develop a future strategy.

Data may be represented in the shape of graphs or charts to aid in the identification of various patterns. Trend lines may be stretched outside the graph with the assistance of Microsoft Excel, making it simpler to analyze patterns and trends. In order to optimize sales, it is essential to evaluate the approval of products and selling designs that everyone admires. Microsoft Excel makes this job easier for company owners, allowing them to develop and optimize earnings.

Control expenses

Microsoft Excel is useful for budgeting. For example, if a physician earns $50,000 every month, he will incur certain expenditures; whenever he wants to take notice precisely about his spending each month, he may simply do so using Microsoft Excel. He may enter his monthly earning and expenditures into excel tables, which will allow him to see his pay and, as a result, limit his spending.

There are many advantages to utilizing Microsoft Excel; this is utilized by people all over the globe for a variety of activities. Not only does it save time, but it also makes the job simpler. It is nearly capable of completing any job. For instance, one may do mathematical computations and also create charts and graphs to save data. It is simple for a businessperson to compute and save content inside it.

Microsoft Excel has the ability to store and analyze huge amounts of data. It helps to store all of the data in a single place so that nothing is lost and no time is wasted looking for specific information. It is a famous program as a result of these reasons. Hence, everyone has grown used to using it.

1.2 Basic Functions of Microsoft Excel

We may maintain the header or footer in our spreadsheet file using Microsoft Excel.

Find and Replace Command

Microsoft Excel enables us to locate required data (text or figures) inside a worksheet as well as to replace old data with new information.

Password Protection

It enables the user to secure their workbooks from unwanted access by encrypting them with a password.

Data Filtering

Filtering is a fast and simple method of locating and manipulating a subset of data in a range. Only the rows that match the criteria you set for a column appear in a filtered range. For filtering ranges in Microsoft Excel, there are two commands:

AutoFilter, which provides a selection-based filter for basic criteria.

Advanced Filter; for criteria that are more difficult to define.

Data Sorting

The process of organizing data in a logical order is known as data sorting. We may sort data in ascending and descending order in Microsoft Excel.

Built-in formulae Microsoft Excel includes numerous built-in formulas for sums, averages, and minimums, among other things. We may utilize such formulas according to our needs.

Create different charts (Pivot Table Report)

Microsoft Excel enables us to make a variety of graphs, including bar graphs, pie charts, line graphs, and more. This allows us to quickly examine as well as compare data.

Automatically edits the result.

If any modifications are made in any of the cells, Microsoft Excel immediately updates the result.

Formula Auditing

With formula auditing, we may use blue arrows to visually show or trace the connections between cells and formulae. The antecedents (cells that give data to a particular cell) and dependents (cells that are reliant on the result in a certain cell) may be tracked.

1.3 History of Microsoft Excel

For data analysis and documentation, Microsoft Excel is a functional and competent tool. This is a worksheet software with many rows and columns, with every crossing of a row and a column being referred to as a "unit" Every unit holds one portion of information and data. You may make information simpler to locate and automatically pull information from moving information by arranging the data in this manner. Microsoft Excel played a critical role in accounting and record-keeping for company operations in the initial periods of accessible PC business computing. A table with an autism format is one of the finest examples of a Microsoft Excel use case. Entering a column of data and clicking inside a unit at the end of the spreadsheet, as well as using the "autosum" option to enable that column to add up all of the values input above, is extremely simple with Microsoft Excel. This replaces manual ledger counts, which were a time-consuming aspect of business prior to the development of the contemporary spreadsheet.

Microsoft Excel has become a must-have for different types of corporate computing, like looking at daily, weekly, or monthly figures, tabulating payroll or taxes, and other comparable business operations, thanks to the autosum and other improvements. Microsoft Excel has become a major end-user technology, helpful in training and professional development, thanks to a variety of easy application cases. Microsoft Excel has been included in basic business diploma courses on business computers for a number of years, and temporary employment agencies may evaluate people for a variety of clerical tasks based on their abilities with Microsoft Word and Microsoft Excel.

Microsoft Excel, on the other hand, has become entirely outdated in certain respects as business technology has progressed. This is due to a notion is known as "visual dashboard" technology, sometimes known as "data visualization." In general, businesses and suppliers have devised innovative new methods to graphically display data that do not need end-users to examine a conventional spreadsheet with columns of numbers and IDs. Instead, they use graphs, charts, and other complex displays to better grasp and comprehend the data. People have learned that "reading" a visual presentation is much simpler.

The application cases for Microsoft Excel have changed as a result of the data visualization concept. Whereas in the past, companies may have used Microsoft Excel to manage hundreds of entries, today's commercial use cases often include spreadsheets that handle just a few dozen variables for each project. If the spreadsheet has more than a few dozen rows, the information will be more effective shown on a visual dashboard than in a conventional spreadsheet style.

Spreadsheets, second only to word processors, have become one of the most common types of computer software. Data, mathematical formulae, text, and images may all be combined in a single report and workbook using spreadsheet software. As a result, spreadsheets have become essential commercial tools, as well as being widely used in scientific research. Excel, in particular, has received widespread praise for its simplicity of use and capability. As spreadsheets' power and simplicity of use have grown, there has been a surge in interest in utilizing them in the classroom. Due to the widespread availability of spreadsheet software at colleges and institutions, a statistics teacher may teach a course without asking students to buy extra software. It would be dishonest not to add a few cautions now that we've highlighted Excel's benefits for teaching fundamental statistics. Spreadsheets are not statistics programs. Therefore, their ability to replace a full-featured statistics program is limited. Equitable two-way analysis of variance is simple in Excel, while unequal two-way analysis of variance is difficult. Spreadsheets also have limitations when it comes to processing data with null values.

1.4 Features of MS Excel

Microsoft Excel XP is a spreadsheet program that comes as part of the Microsoft Office package. A spreadsheet is computer-based accounting software. The primary purpose of a spreadsheet is to deal with numbers as well as text. Spreadsheets may be used to organize data, like rearranging a list of the names and sorting data, as well as to compute and analyze data utilizing mathematical formulae.

The Excel Windows

Most of the objects that Excel XP displays are common in other Microsoft software applications, such as PowerPoint, Word, including earlier versions of Excel, but others are exclusive to Excel XP.

Workbook

The workbook, often known as a spreadsheet, is a unique file produced by Excel XP.

Title Bar

The application's and spreadsheet's names are both shown in the title bar.

Menu Bar

The menu bar in Excel XP shows all of the options accessible to you. By left-clicking the menu name, you may see the contents of any menu.

Toolbar

There are images or symbols connected with certain menu commands. These images may also show on the toolbar as shortcuts.

Column headings

There are 256 columns in each Excel spreadsheet. A letter or a combination of letters is used to name each column.

Row headings

There are 65,536 rows in each spreadsheet. A number is assigned to each row.

Name box

This shows the average selection's or active cell's location.

Formula bar

The formula bar shows data that has been entered or is being input in the current and active cell. The formula bar may also be used to modify the content of a cell.

Cell

A cell is a point where a column and a row meet. Each cell has its own unique address. The cell address of the chosen cell in the image above is B3. The cell pointer is the thick border that surrounds the chosen cell.

OceanofPDF.com

Navigation buttons and sheet tabs

You may go to another worksheet in an Excel workbook using navigation buttons. They're used to show the workbook's first, previous, next, and final worksheets.

Worksheet tabs divide a workbook into individual worksheets. There are three worksheets by default in a workbook. There must be at least one worksheet in a workbook.

Workbooks and worksheets

When you launch Microsoft Excel, a workbook appears on the workspace by default. There are three worksheets in each workbook. A worksheet is a cell grid that has 65,536 rows and 256 columns. Text, numbers, and mathematical formulae are input into various cells of a spreadsheet.

The grey bars that run across the Excel screen, starting with column A and finishing with column IV, contain alphabetic characters that correspond to column titles.

Rows are identified by numbers that display on the left side of the Excel screen and then go down the screen. The first row is referred to as row 1, while the final row is referred to as row 65536.

Sheet1, Sheet2, and Sheet3 are the names of the three worksheets that make up a workbook.

Columns and rows make up each Excel spreadsheet.

To go to a worksheet, go to the Sheet# tab and click it.

The cell

Columns and rows make up an Excel spreadsheet. The intersection of these columns and rows results in the formation of cells, which are little boxes. A black border surrounds the active cell or the cell that may be acted upon. The rest of the cells have a light grey border. Each cell is given a name. It has two components to its name: the column letter and the row number.

The active cell is the cell that receives the data and instructions you give it.

And it has a unique cell address made up of the cell's column and row.

It's identified by a darker border known as the cell pointer.

Moving around the worksheet

You have many options for moving about the spreadsheet.

To shift the cell pointer, do the following:

To engage any cell, use the mouse to point to it and click.

Use the keyboard arrow keys to move the cursor one cell to the left, right, up, and down.

To scroll through the worksheet.

To navigate up or down the spreadsheet, utilize the vertical scroll bar on the right side of the screen. To navigate left or right across the spreadsheet, utilize the horizontal scroll bar at the bottom of the screen.

The keyboard buttons PageUp and PageDown are used to move the pointer up and down one page at a time. Home, which moves the pointer to the first column on the current row, or Ctrl+Home, which sends the pointer to the top-left corner of the spreadsheet, and cell A1, are two more keys that shift the active cell.

To move between worksheets.

Each workbook comes with three worksheets by default. Sheet1, Sheet2, and Sheet3 are the tabs that show at the bottom of the Excel window to indicate these workbooks.

1.5 Why Should You Learn Excel?

It is used by teachers when they need to compare a pupil to their classmates. When it comes to choosing which offering(s) to retain and which to retire, businesses have the same dilemma. Scientists that wish to determine the homogeneity of a dataset have the same problem.

What exactly are we discussing? Of course, Microsoft Excel is used. According to a survey performed by Burning Glass, the three occupations are among the 80 percent of employment opportunities that need spreadsheet and word-processing software abilities. Despite this, many individuals never give Excel a try because of its daunting reputation.

The sad fact is that "technical ignorance, let alone technophobia," is no longer a choice for contemporary workers. Entire sectors of the US economy are effectively off-limits to individuals who lack fundamental computer skills.” What's the good news? Actually, there's a lot of positive news. Continue reading to discover why you should include Excel on your resume.

Excel is not just for making tables

Maybe you used Excel in school to put in a few number tables and add two columns jointly. Excel, on the other hand, is much more complicated. Did you know, for example, that the software can do all of the following tasks:

Arrange the data in a user-friendly manner.

Perform simple and complicated mathematical operations for you, so you don't have to.

Convert large amounts of data into visually appealing graphs and charts.

Perform data analysis and predicting forecasts. Pixelate pictures are created, built, and edited.

Is it possible to make a long tale short? There's a lot more to the software than you probably thought, and you may use it in any situation. It's less about rows of data and more about problem-solving in an orderly way, and this change in viewpoint will enable you to think more critically about how Excel might assist you.

Excel helps you get stuff done.

No professional has ever stated, "I don't want to go through my job more efficiently." Excel's many applications and features are designed to save you time in addition to organizing data. Instead of manually adding up 127 columns of monthly expenditures, Excel can do it for you, and you'll know it's right. You'll save a lot of time at work and/or in your personal life by utilizing Excel, and it'll always be more accurate than anything you could accomplish by hand. What's not to like about that?

It will increase your salary

Did you know that having an understanding of Excel may immediately improve your employment chances and your beginning salary? Any hiring manager recognizes the importance of Excel as a transferable talent. The benefit of understanding such universal computer software is that it provides you with choices. But, more significantly, let us discuss the huge dollars.

According to research, middle-skill job candidates who know Microsoft Excel earn $22.66 per hour on average, compared to $20.14 per hour for those who don't. Simply understanding how to operate a single computer application is worth an additional $20 every eight-hour workday and $100 per workweek. Furthermore, full-time workers in some sectors may expect a pay increase of $1,000 to $7,000 per year as a result of their Excel abilities. That isn't a small sum to dismiss.

Excel will make you better at your job (no matter what that is)

Excel isn't only used by investment bankers and accountants; it's also used by scientists, professors, company owners, graphic designers, and a slew of other professionals. Whatever you do at work, there's a good chance Excel can help you do it better; it's simply a question of finding out how.

Is there a more efficient method to organize your data, for example? Could visuals help you convey your thoughts more effectively? Do you just need a location to put all of your brainstorming ideas? Instead of those odd pieces of paper packed with your chicken scratch, use Excel next time.

Even better, excel prowess may make you the office's go-to person; you never realize when your employer or a coworker will require someone with Excel prowess to work their magic, and that individual might be you. Who wouldn't want to win a gold star? And it is for this reason that you must learn Excel. Excel may appear daunting at first, however by just using it for simple activities in your life, and you'll get a greater understanding of how it works and how it might make life easier.

1.6 Where Can You Get Excel?

Excel, developed by Microsoft, has long been the industry standard in spreadsheet software. You do not, however, have to pay to get access. For more than 30 years, Microsoft's Excel has been the industry standard in spreadsheet software.

It has become a very important tool for companies all over the globe as a major component of the enormously popular Microsoft Office productivity suite. While a Microsoft 365 subscription makes sense in the business sector, it may not be appropriate for you. We'll teach you how to use Microsoft Excel fully legally and for free in this post.

Web Version

All you need is a Microsoft account to use Microsoft Excel and other essential Office applications over the web. To get started, go to Office.com and establish an account, and log in if you already have one. You'll be greeted with this page after you've signed in to your Microsoft account on office.com: You may choose the App you want to use at the top of the page, which includes anything from Excel, Word, and PowerPoint to Outlook or Skype. These are all referred to as 'Web Apps,' which implies they are programs that you use online rather than on your computer. When you work on a document here, it will be uploaded to Microsoft's OneDrive cloud storage service.

Indeed, Google is likely to blame for this program's availability as a web app since its free Docs or Sheets software and connection with Google Drive made it impossible for Microsoft to continue charging customers. If price isn't a determining issue, have a look at our list of the top cloud storage options. Because the online version of Excel is a more stripped-down version of the software than the full desktop version, the comparison to Google Docs seems to be fairer. Nonetheless, both systems must be able to offer all a typical consumer's needs. Make sure to check out the finest free Windows applications right here.

Use on mobile Microsoft's Office mobile apps are totally free and accessible on all contemporary mobile devices, maybe in reaction to Google. The Microsoft Excel app is available for Android and iOS. While Microsoft sensibly saves certain premium capabilities for its Office 365 subscription, the mobile applications are nonetheless very powerful and the ideal choice for on-the-go work.

Get the full version.

Of course, if you just need the full edition of Excel, you'll need to sign up for Microsoft 365. Microsoft, like Adobe, prefers that you pay a monthly subscription to use their software rather than purchasing it altogether. On the positive side, you'll receive frequent upgrades, which should make it safer than waiting for a major yearly release.

Subscriptions are available for £5.99 per month or £59.99 per year. For £119.99, you can receive Excel, Microsoft Word, and PowerPoint as a one-time purchase, but you won't get OneDrive and Skype, which are normally included as normal.

1.7 How Do You Open Excel?

Excel works in the same manner as any other Windows program. Use Windows with a graphical user interface to carry out these steps (Windows 8.1 0r 10).

From the drop-down option, choose start.

All programs should be visible to the public.

Navigate to Microsoft Excel using the cursor.

Select Microsoft Excel from the drop-down menu.

However, if it's been added to your start menu, you may access it from there. You can utilize a desktop shortcut to get to the application if you've made one. Right-click on the Windows Power button and search for "Excel" in the search box. Choose "Excel" from the search results. There are a variety of spreadsheet programs available, but Excel is perhaps the most popular. Over time, people have been upgraded with ever-improving capabilities.

Excel's biggest virtue is its flexibility; it can be used for statistics, economics, data management, planning reporting, product, billing monitoring, and business intelligence, among other things.

OceanofPDF.com

Chapter 2: Understanding and Getting Started

with MS Excel

Microsoft Excel is a spreadsheet program that is used by marketers, accountants, data scientists, as well as other experts to store, organize, and monitor large amounts of data. It's a component of the Microsoft Office suite. Google Sheets or Numbers are two alternatives. Excel is a spreadsheet program that may be used to organize, Filter, and visualize huge quantities of data. It's most often used in accounting, although it may be utilized by anybody who has to handle large, unmanageable information. Balance sheets, budgeting, editorial calendars, and data calculators are examples of Excel applications.

Because of its powerful computational capabilities, Excel is mainly used to create financial documents. Accounting offices and teams often utilize the program because it enables accountants to view sums, averages, and totals instantly. They can quickly make sense of their company's data using Excel. While Excel is best recognized as an accounting program, it can be used by experts in any area, particularly marketers, who can utilize its features and formulae to monitor any kind of data. It eliminates the need to count cells or copy and paste performance data for hours on end.

2.1 Best way to use Microsoft excel

Excel may seem to be too wonderful to be true at times. All you have to do is type in a formula, and almost everything you'd have to do manually can be done automatically. Do you need to combine two sheets that have comparable data? Excel is capable of completing the task. Perform you need to do some basic math? Excel is capable of completing the task. Do you need to integrate data from many cells? Excel is capable of completing the task. The only issue is that it may be tough for novices to utilize. While manually entering Data is simple, learning all of the formulae and shortcuts required to master the tool may be a headache. Plus, even if you follow the formulae exactly, there's a possibility you'll get an error notice. Simply stated, Excel is a difficult program to master.

Don't be concerned. We'll go through the greatest Excel tips, techniques, and shortcuts you can use right now to boost your productivity. There is no need for sophisticated Excel skills. Microsoft Excel is a spreadsheet program that is used by marketers, accountants, data scientists, as well as other experts to store, organize, and monitor large amounts of data. It's a component of the Microsoft Office suite. Google Sheets or Numbers are two alternatives. Excel is a spreadsheet program that may be used to organize, Filter, and visualize huge quantities of data. It's most often used in accounting, although it may be utilized by anybody who has to handle large, unmanageable information. Balance sheets, budgeting, editorial calendars, and data calculators are examples of Excel applications.

Because of its powerful computational capabilities, Excel is mainly used to create financial documents. Accounting offices and teams often utilize the program because it enables accountants to view sums, averages, and totals instantly. They can quickly make sense of their company's data using Excel. While Excel is best recognized as an accounting program, it can be used by experts in any area, particularly marketers, who can utilize its features and formulae to monitor any kind of data. It eliminates the need to count cells or copy and paste performance data for hours on end. Excel usually offers a shortcut or quick workaround to make the process go faster. Are you unsure how to effectively utilize Excel in your team? The following is a list of papers that you can make:

Income Statements

An Excel spreadsheet may be used to keep track of a company's financial and sales health.

Balance Sheets

Balance sheets are one of the most frequent sorts of materials that Excel can generate. It enables you to obtain a comprehensive picture of a company's financial position.

Calendar

To keep track of events or other date-sensitive information, you may simply build a monthly spreadsheet calendar.

2.2 Create, Open and Save Workbooks Getting to Know File Terminology.

All of the procedures we'll be covering are found in the File menu: New, Open, Close, Save and Save As are all options.

New

This is what you'll need to establish a new workbook.

Open

This is used to retrieve a file from your computer's floppy disc or hard drive.

Close

This Command is often used to end a spreadsheet.

Save As

This is utilized when storing a new document for the very first time and changing the title of an existing file.

Save

This is used to save a file after it has been modified. Any changes you make will be lost if you close the workbook without saving them.

Creating a Workbook

When you initially start Microsoft Excel XP, you'll see a blank workbook. This blank workbook may be used to input content or create a layout. To make an Excel XP workbook, follow these steps:

From the menu bar, choose FileNet.

On the right side of the screen, the New Workbook task window appears.

Under the New category header, choose Blank Workbook.

In the Excel window, a blank workbook appears. The task window for New Workbook has been closed.

Saving a Workbook

To differentiate it from other workbooks, each Excel workbook must be saved and given a name. When you save a workbook for the first time, Excel will ask you to name it using the Save As Command. Any further modifications to the text, numbers, or formulae must be saved using the Save procedure after the File has been given a name.

To save a new workbook.

From the menu bar, choose File Save As.

The dialogue box "Save As" appears.

Select a location for the File to be stored from the Save In drop-down menu. To save the file to a floppy disc, choose 3 1/2 Floppy (A:) or Local Disk (C:) on your computer.

In the File Name: box, give your File a name.

Select the Save option.

Select File ->Save from the menu bar to save changes to an existing worksheet.

On the Standard toolbar, click the Save button with the "floppy disc symbol."

Select "correct-sign" from the drop-down menu. If you don't provide a file name when saving the File for the first time, Microsoft Excel will do it for you. When working with a spreadsheet, it's a good idea to save regularly. It's never pleasant to lose knowledge. Using the shortcut key combination Ctrl+S, you may rapidly save your spreadsheet.

Taking out a workbook .

Any workbook that has been saved as well as given a name may be opened.

Select File Open from the menu bar to open an existing Excel XP worksheet.

The dialogue box "Open" appears.

Select the disc, folder, and Internet location that contains the file you wish to open from the Search in the list.

Select the folder that holds the File from the folder list. Click on the File you wish to open after it has been shown.

Select the Open option.

Putting an end to a workbook.

Select File Close from the menu bar to close an existing Excel XP worksheet. The Excel window's worksheet has been closed.

If you have written anything between the last save and the time you shut the File, Excel will ask you to save it.

2.3 Enter, Edit and Delete Data

Adding text to a cell

Text, numbers, and formulae are the three kinds of data that may be entered into a cell. Any input that isn't a number or a formula is referred to as text. Numbers are numerical quantities that are utilized in computations. The term "formula" refers to a set of mathematical computations.

To input data into a cell, do the following:

Click the cell where you wish to type data.

Fill up the blanks with data. As the Data is typed, an insertion point appears in the cell.

The information may be entered into the cell or the formula bar.

The active cell and the formula bar both show the Data that is being entered.

In the formula bar, you'll see the Cancel and Enter buttons.

To finish the input and switch off the formula bar buttons, use the Enter key.

Excel's AutoComplete function maintains track of text that has already been input. Microsoft Excel fills in the remaining characters for you if the first few characters you enter in a cell match an existing item in that column.

Changing the contents of a cell

A spreadsheet's Data is likely to alter over time. There are two methods to alter information. The fast and simple approach is as follows:

Select the cell that contains the Data you wish to modify.

Make a new entry. The previous entry is deprecated in favor of the new one.

You may immediately modify the information in the cell if the original item is lengthy and just needs minor changes (for example, in Spelling). To change the contents of a cell, use the following commands:

Direct cell editing

Double-click the cell that contains the Data you wish to modify.

The cell is accessed for editing directly.

Make the required changes.

To finish the input, press Enter and press the Enter button on the formula bar.

Formula bar editing

Select the cell that contains the Data you wish to modify.

Make changes to the formula bar entry.

Information in a cell is deleted.

To remove data from a cell that already exists, click the cell holding the information you want to remove.

Select the data in the formula bar by clicking it.

To remove an item and switch off the formula buttons, click the Cancel button.

Select the cell holding the information you wish to remove by clicking it.

Right-click and select Clear Contents from the shortcut menu, OR

Press the Delete key.

Cancel an input by hitting the Escape key to remove data that has not yet been entered into the cell.

Using the Undo/Redo functions.

You may make an unintended change to a spreadsheet, such as typing the incorrect number into a cell. You may reverse an operation in Excel XP. To undo a mistake, use the Undo button on the Standard toolbar. The most recent action is reversible.

To reverse recent acts one by one, follow these steps:

Select the Undo option.

To reverse a series of recent activities all at once, type:

Select the Undo button by clicking the arrow next to it.

From the list, choose the required Undo operation(s).

The chosen action, as well as any activities in the list above it, are reversed in Microsoft Excel.

A Redo operation may be used to cancel an Undo operation. This is helpful if you accidentally applied an Undo action. Remember that a redo is only feasible if you haven't made any changes to an Excel spreadsheet since the previous Undo operation:

Press the Redo button to undo an Undo action.

To undo multiple recent Undo operations at once, use the following command:

Next to the Redo button, click the arrow.

From the list, choose the Redo operation you want.

The Undo function in Microsoft Excel is reversible.

Multiple cells may be selected.

The active cell in Excel XP refers to the cell that is presently chosen. You may also choose a cell range or a group of neighboring cells. Moving, copying, deleting, and formatting are just a few of the actions that may be performed on a cell range. A cell range may be specified in a variety of ways, including selecting a particular set of cells, several columns or rows, or the whole worksheet.

Go to the first cell in the range to pick a range of cells.

The mouse cursor changes to a big cross.

Hold down the left mouse button as well as drag to the last cell you wish to choose, either left or right, up or down.

Let go of the mouse button.

The cells you've chosen have been colored.

To select all cells in a column or row, use the following commands:

To select the whole column, click the grey column header. To choose additional column headers, click and drag the mouse across them.

To select the whole row, click the grey row heading. Select those rows by dragging the mouse down through the row headers.

To select a whole worksheet, use the following commands:

To select the whole worksheet, click the grey rectangle in the upper-left corner.

Pick one of the ranges you wish to select, then hold down the Control key while choosing additional ranges if the cells and

columns you want to select are not immediately next to one another.

2.4 Customization of the Ribbon

What you can and can’t customize

Before starting our guide, let's take a look at what can be customized.

What you are able to modify and what you are unable to customize.

Let's have a look at what can be modified.

Capabilities

In Excel 2010, 2013, 2016, 2019, and Microsoft 365 editions, you may modify the ribbon.

Create a new tab using your own instructions

The show, hide and rename tabs

Rearrange tabs, groups, and custom commands in the order you want them to appear.

Export or import your customized ribbon

Add and delete groups from existing tabs

Limitations

Built-in commands cannot be changed or removed. You may, however, conceal a whole group.

The ribbon cannot be resized. The only visual option is to totally conceal (collapse) it.

Text size, font type, and color choices are available right away. You may also use Excel schemes to change the backdrop of the ribbon across all Office apps.

The ribbon is one element of Microsoft Excel that you'll like. You may customize the toolbar area in Excel in a variety of ways. It conceals part of the toolbar when you need more space or wants to be in distraction mode, for example. It is, on the other hand, simple to display the full version again. Now we'll teach you how to add additional features to the Excel ribbon, like the Developer tab and common commands.

With Office 2007, Microsoft introduced the ribbon. It did away with menus and toolbars in favor of a new interface that performs the same functions. Contextual components may be found in the toolbar:

Tabs are significant divisions that run along the top of the page. (For example, Data)

Groups inside a Tab, are related things. (For example, Get and Transform Data)

Commands: These are buttons that represent features inside a Group. (For example, Filter)

The components listed above are present in all tabs. Some tabs, such as Page Layout, include an additional tab called Dialog Box Launcher. When it's active, it's in the Group's bottom right corner. It will launch a new window similar to Page Setup when you click it. Excel comes with the following ribbon tabs when you first open it:

File

Home

Insert

Draw

Page Design

Formulas

Data

Review

Help

Ribbon Elements: Show and Hide

The ribbon appears beneath the Quick Access toolbar for most users. The Excel ribbon is often concealed, so you don't see it. It's not gone; rather, it's "collapsed." Some individuals choose to close it so they can work on the spreadsheet more comfortably.

How to Collapse the Excel Ribbon

The menu may be opened or collapsed in the same way. The main difference is that the option may include a checkbox to indicate your current status.

Hover your cursor over the name of any ribbon tab. (For example, Data)

Choose Collapse the Ribbon from the context menu by right-clicking.

Keyboard shortcut: Ctrl + F1

Show or Hide Using Ribbon Display Options

Anyone who has worked with Microsoft products understands that jobs may be completed in a variety of ways. If you check at the upper right corner of your Excel page, for example, you'll see a tiny symbol before the minimize button. It features the shape of a square with an upward arrow.

This is where you'll find the Ribbon Display Options. This button may be more convenient in certain situations. Selecting Auto-hide Ribbon will cover the screen with your spreadsheet.

How to Reopen a Collapsed Tab?

All you're doing when you collapse the ribbon is temporarily concealing the groups and instructions. The tab names are still shown in a menu-like fashion. Click the Tab to bring these things back. Your instructions and groups will return. When you're finished, click the Tab once again to collapse it.

Add Excel Developer Tab & Other Tabs

Excel does not activate all of the tabs and functions when you initially install it. Microsoft usually adds the most commonly used ones first, then enables the user to add the others. The Developer tab is one of the first that people prefer to reinstall. If you use Excel macros, this is very useful.

Hover your cursor over any of the tab names. (For example, Data)

Choose Customize the Ribbon from the context menu by right-clicking.

Excel's Options dialogue box will appear.

Select Developer or another Tab from the Customize the Ribbon: section on the right.

Change the drop-down option on the top of the right panel from Main Tabs to All Tabs if you don't see your Tab.

Click the OK button.

Ribbon Structure and Commands

It's easier to add commands if you recall the ribbon's structure. We're adding a command to a custom group inside a tab when we add it to the ribbon. Commands aren't self-contained, and you can't cram them into a pre-existing group. You'll receive a Ribbon Customization error notice if you attempt to add an unneeded command to an existing group:

Custom groups must be created before commands can be added. To create a group, choose a tab from the drop-down menu and then click New Group. Another Excel alternative is available. Commands that aren't shown by default must be placed in a "custom group." This custom group may be added to an existing tab, or a new tab can be created.

How to Add a Command?

Hover your cursor over any of the tab names. (For example, Data)

Choose Customize the Ribbon from the context menu by right-clicking.

Excel's Options dialogue box will appear.

Select Commands Not in the Ribbon from the Choose commands from the drop-down menu.

Scroll to the bottom of the list and choose the Command you want to add. (For example, Form...)

Choose whether the Command should be placed on an existing tab (for example, Formulas) or a new tab.

Since many of the default Tabs are filled, I prefer to make a new one.

On the right side, towards the bottom, click the New Tab button.

New Tab (Custom) and New Group should now appear in the Main Tabs area (Custom).

It's also a good idea to shade the object. The coloring indicates that the new Command will be accepted in this area.

In the middle, click the Add >> button.

Our New Tab (Custom) and New Group must now be renamed (Custom).

Select the New Tab (Custom) item by clicking on it.

Select the Rename... option.

10. Give your Tab a new name. (For example, Custom, My Stuff)

11. Select OK.

12. Rename New Group in the same way (Custom).

OceanofPDF.com

Save Excel Ribbon Configuration

You may export your ribbon settings if you've made a lot of changes to it or if you're worried that another user will alter it. This will create an exported UI file for Excel Customizations. The File may then be imported back into Excel. Your current settings will be overwritten. However, your changes will not be visible until you press the final OK button.

Designing the ribbon in Excel requires a little more time than freezing panes, but it's not something you do for every worksheet. Changes you make, including adding tabs or altering the layout, are permanent.

2.5 Proofing setting

Microsoft Excel is a worksheet; it has stolen the spell check function from the in-house Word processor. Excel employs a similar mechanism to detect spelling errors in spreadsheets. However, objects are not precisely the same in Excel as they are in a regular word processor. You must use the spell check function in Excel.

How do you check spellings in Excel?

In contrast to popular belief, there isn't a key in the ribbon within the Home Tab. To use the button to check Spelling, go to the view key or choose Spelling from the Proofing group's first button on the left.

Check for spelling mistakes in the whole worksheet

Step no. 1

Check to see whether you're in the editing process. One can check this by glancing at the status bar in Excel, which is located at the lower-left corner of the window. It is NOT in editing mode if it reads "Ready." Excel is in the editing process if it reads "Enter."

OceanofPDF.com

Step no. 2

Make sure the worksheet you wish to be checked is active. By selecting the appropriate Tab, you may activate the worksheet.

Step no. 3

There are two choices available to you:

Go to the view button, then choose the Spelling option.

Alternatively, on the keyboard, press F7.

Excel begins to check for spelling errors. From the operational cell point until the conclusion of the worksheet, it checks for Spelling.

Step no. 4

Whenever Microsoft Excel detects an error, it will show a spelling options box from which you may choose the best option:

Ignore just one time: Neglect the present spelling check error.

Ignore all: Disregard all errors, much as the present spell check errors.

Change: Replace the existing error with the chosen suggestion

Put into Dictionary: Include the term in the dictionary

Replace all errors with the chosen recommendation if they are the same as the existing ones.

Auto-Correct: Excel will do the work for you.

Click the Cancel button if you wish to halt the correction procedure right now.

Step no. 5

When the operational cell is not A1, Excel will urge the user to resume checking from the start of the worksheet once it has reached the end of the worksheet. The user has the option of selecting yes/not.

Check spellings for just one specific cell

Enter the editing mode

Select that unit, then hit the F2 key.

Alternatively, you may click twice on that unit. OR

After choosing the cell, click on the formula bar.

Click the spelling button and hit the F7 key to start spell checking.

If Excel detects any spelling errors, it will show a suggestions box; or, a box will emerge stating, "The spelling check for unit text has been done."

Spelling check portion of text

Enter the editing mode by using the F2 button and clicking twice, text-filled cell.

Choose the text you'd like to have tested for spelling errors. You may do it in the following ways:

Whether by pressing the left key button down and dragging the choice over the text or by moving the mouse to the beginning and end of the text. Hold down the shift button on your keyboard while pressing the relevant arrow keys, such as Cursor Up, Cursor Down, Cursor Left, and Cursor Right.

Launch spell check using the ribbon or a keyboard shortcut.

Complete the job by following the active screen directions.

Spellings check multiple units in a single spreadsheet

Double-check that no cells are in editing function. If you're in edit mode, use the Escape button or click anywhere in Excel to exit.

You may easily drag selection if the cells are connected without any splits or gaps (contiguous data). This may also be done using a

shortcut. To make a selection, press F8 once and then use the directional keys.

How F8 selection proceeds?

If the cells are far apart, hold down the Ctrl button on the keyboard and use your mouse to pick the cells, you wish to examine. You may also use the Shift+F8 shortcut. If you have a big number of cells to pick, you won't need to hold down the shift key.

Use the shortcut key F7 to start the spelling check procedure.

Follow the directions on the screen.

Once the procedure has been completed instantly, a box will be shown stating that the spell check for the chosen cells has been completed.

Spelling checker multiple units on different worksheets

Make sure the edit mode is turned off. If that's the case, hit the Escape key for one time.

Hold down the Ctrl key or use the F8 and Shift+F8 key combination to select the cells on one worksheet. Once that's done, go on to the next worksheet, where you'll need to pick more cells. Make sure you don't click any keyboard or mouse buttons before leaving the current worksheet. Simply choose the other worksheet's Tab.

Once the next worksheet is open, repeat the selection procedure using the method you choose, such as the Ctrl key, F8, or Shift+F8, as described before. If you need to pick data from several spreadsheets, repeat the process.

To begin the spelling check process, press F7.

Complete the job by following the active screen directions.

Spell check multiple worksheets at once

Hold down the Ctrl key while clicking the spreadsheet tabs you wish to have spell-checked.

Press the F7 button on the keyboard to start the spelling check.

Follow the directions on the screen.

Keep in mind that the spreadsheets you choose would be examined for spelling errors; many others would be disregarded. Continue reading if you want to check the whole workbook.

Spell check all worksheets / whole workbook

Select all sheets by right-clicking on any spreadsheet tab.

To begin the spelling checker procedure, click F7 and select the spelling check button on the ribbon beneath the Review tab.

Complete the job by following the active screen directions.

Spelling check text combined with concatenate formula

Using the concatenation function in Microsoft Excel, we may combine text strings from multiple cells into a single cell. Consider the following scenario:

“I watch cartoons all day” is written in cell A1.

“My favorite cartoons are Tom and jerry,” says cell A2.

“I am 35 years old,” says cell A3.

A10 is the cell to go to. Any of the following techniques may be used to combine the data of the above three units:

=A1&” “&A2&” “&A3

=CONCATENATE (A1,” “A2,” “A3)

The unit containing this formula does not change –, but rather the formula's result and value. As a result, Excel is unable to spelling checker like "text." And it may, however, return to merged source cells, but not to the unit where figures appear as a single component. If you pay attention, after the spelling checker is started, it ignores the formula-joined text and returns to the source cells at the top of the worksheet.

Spell check words in the formula

According to the previous statement, Microsoft Excel does not examine text that has been joined by assigning a single excel formula, and we also cannot spell check text that is a portion of formula since it is contained inside the formula container, which predictive text do not have entry to.

Spell check does function if you're in the editing process or pick the text that's part of the formula. This operates in the same manner that we would if we wanted to check a section of text.

2.6 Excel’s Proofing Options

You're probably wondering how Excel manages to extract this Spelling feature. Simply said, it performs the same functions as people. If we can't recall the spelling of a word, we check it up in the dictionary. Similarly, Excel includes a built-in dictionary. It also makes use of that dictionary behind the scenes.

It's now clear why, even if the term is right, Excel occasionally fails to detect it. Color, for example, maybe written as color. However, according to Excel's default United States Dictionary, the color is incorrect. However, by consulting the proper dictionary, this may be resolved. To accomplish so, we'll need to use Excel's settings.

Select File> Options from the File menu. Click proofreading in the excel functions box on the left side. You now have all of the choices you need.

To get color disorder or color, you may alter the settings below, including the dictionary language.

OceanofPDF.com

Chapter 3: Formulas and Features of MS Excel

Until you establish some sort of connection among the different items, a worksheet is just a dead collection of numbers and text. This is accomplished via the use of formulae that conduct computations and provide outcomes. We'll go over some formula fundamentals, such as how to create simple arithmetic and text formulae, how to grasp operator precedence, how to copy and move worksheet formulas, and how to use range names to make formulas simpler to write and comprehend.

For beginners to become extremely competent in financial analysis, they must first master the fundamental Excel formulae. Microsoft Excel is widely regarded as the company standard in data analysis software. In terms of data processing, financial modeling, and presentation, Microsoft's spreadsheet application is the most popular among investment bankers, as well as financial investigators. This guide will provide you an overview of fundamental Excel features as well as a list of them.

The benefits of learning Microsoft Excel are many, even though the initial stages may seem daunting. Some people may get buried in the spreadsheet lingo and become much more perplexed than originally began. Let's go through the most frequent terms you'll run over as an Excel consumer.

3.1 Basic Terms in Excel

Are you unsure what the difference between a workbook and a worksheet is? What is the best way to tell whether a unit is active or not? You're not the only one who feels this way. Knowing the terminology used in Excel gives you a better understanding of what you can do with it.

The benefits of learning Microsoft Excel are many, even though the initial stages may seem daunting. Some people may get buried in the spreadsheet lingo and become much more perplexed than originally began. Let's go through the most frequent terms you'll run over as an Excel consumer.

Workbook

An Excel spreadsheet file is referred to as a workbook. The workbook stores all of your information and enables you to filter and compute the results. A Shared Workbook is a workbook that can be read and updated by many users on the same network.

Worksheet

Worksheets are documents that are included inside a workbook. Workbooks, often known as spreadsheets, may include numerous worksheets. The tabs at the bottom of the screen will show you which of the worksheets you're working on right now. An active worksheet and active sheet are another name for this.

Unit

In a worksheet, a unit is a rectangle or block. Any information you wish to include in your worksheet must be entered into a unit. Depending on what you want to achieve, units may be color-coded, show text, numbers, and the results of computations. A unit that is presently open for editing is known as an active unit.

Columns and Rows

The alignment of your units is defined by columns and rows. The columns are vertically aligned, whereas the rows are horizontally aligned.

Column and Row Headings

These headers are the grey spaces that are lettered and numbered immediately outside of columns and rows. When you click on a heading, the whole row or column is selected. The headers may also be used to change the row height or column width.

OceanofPDF.com

Workspace

A workspace, including worksheets in a workbook, enables you to open several files at once.

Ribbon

The Ribbon is a set of command tabs located above the worksheet. Behind each tab on the Ribbon is a plethora of choices.

Unit Reference

A unit reference is a collection of coordinates that uniquely identify one unit. It is made up of letters and digits. A5 would, for instance, refer to the unit at the intersection of column A or row 5.

Unit Range

A unit spectrum is a group of units that have been recognized as such based on several criteria. Excel can identify the range, commonly known as an array, by placing a colon (:) among unit references. A1:C1, for example, might tell the formula to check at all units in a box limited by columns B and D or rows 4 and 9, while B4:D9 might instruct the formula to examine at all units in a box limited by columns B and D as well as rows 4 or 9. A 3-D reference is a range that spans several worksheets inside the same workbook.

Merged Unit

A merged unit is made up of two or more units that have been united.

Template

A template is an Excel workbook or worksheet that has been prepared to assist users in completing a particular task. Stock analysis, process maps, and calendars are examples of this.

Operator In an expression, operators are letters and signs that specify which calculations must be performed. Operators don't have to be basic mathematical kinds; they may also be a comparison, text concatenation, and reference operators.

Formula

A sequence inside a unit generates a value. It must start with an equal symbol (=). A mathematical equation, unit references, features, or an operator may all be examples. An expression is another name for a formula.

Formula Bar

The Formula Bar is located between the Ribbon and the workbook and displays the data of an active unit. In the event of formulae, the equation bar will show all of the formula's components.

Feature

Features are Excel formulae that have been pre-programmed. They're designed to make potentially complicated formulae in a spreadsheet easier to understand.

Error Code

If Excel detects a problem with a calculation, Error Codes show.

Unit Formatting

This is the process of altering the appearance of unit data in a spreadsheet. Only the visual appearance of the units is altered when they are formatted; the value inside the units remains unchanged.

OceanofPDF.com

Conditional Formatting

Formatting is only applied to a unit if it satisfies certain criteria, including duplicate values and values that are above or below a certain threshold.

Filter

Filters are rules that may be used to determine which rows of a worksheet should be shown. Data including conditions or values may be used in these filters.

Freeze Panes

Freezing Panes enables you to choose certain columns and rows on the worksheet to stay visible even while navigating, including header units that identify a section.

AutoFill

This allows you to copy data to many units with ease.

AutoSum

This feature adds up the figures in your spreadsheet as well as shows the result in a column of your choice.

AutoFormat

This is a program that applies a format to units that meet certain requirements. It may be as basic as font size and alignment.

Data Validation

This tool helps you avoid entering erroneous data into your worksheet. Drop-down lists for popular words are most often created using this method. Data validation ensures that the data being input is consistent and accurate.

Pivot Table This is a data summarization tool that is most frequently used to automatically sort, average, and sum up data. The data is retrieved through one table, and the findings are presented the other. Pivot Tables make it simple to extract particular data from a big data set.

Pivot Chart

This kind of chart serves as a visual representation of pivot tables. The user may offer a degree of interaction with the data by creating graphical representations of the pivot table data.

Pivot Area

The pivot area is a location on the sheet that you may move a Pivot Table column to rearrange the way analysis is presented.

Source Data

This is really the data that went into making your pivot table. It may come from the spreadsheet itself than from an external database.

Values Area

Value regions are defined as the units in a pivot table that provide summary information.

Item

In your pivot table, they are sub-categories of fields. If you have a State field, the values might be Alabama, Alaska, and so on.

3.2 How to use Features and Formulas?

Until you establish some sort of connection among the different items, a worksheet is just a dead collection of numbers and text. This is accomplished via the use of formulae that conduct computations and provide outcomes. We'll go over some formula fundamentals, such as how to create simple arithmetic and text formulae, how to grasp operator precedence, how to copy and move worksheet formulas, and how to use range names to make formulas simpler to write and comprehend.

For beginners to become extremely competent in financial analysis, they must first master the fundamental Excel formulae. Microsoft Excel is widely regarded as the company standard in data analysis software. In terms of data processing, financial modeling, and presentation, Microsoft's spreadsheet application is the most popular among investment bankers, as well as financial investigators. This guide will provide you an overview of fundamental Excel features as well as a list of them.

The Fundamentals of Formulation

The majority of worksheets are designed to answer particular questions, such as: What is the profit of the company? Is it true that your expenditures are above and are under budget, or if, by how much? What is an investment's future value? What will the size of an employee incentive be this year? Excel formulae may be used to address these issues and a plethora of others. The same basic structure can be found in all Excel formulas: an equal sign (=) followed by one or more operands, numbers, unit references, ranges, range names, and feature names. One or more operators, which are symbols that join the operands in some manner, such as the addition sign (+) and the larger sign (>), separate the operands.

Formula vs. Feature

The formula in Excel is a user-created equation, while a Feature is a pre-programmed computation in the spreadsheet application. This tutorial will take you through the differences and similarities between formulas and features in Excel. Simple calculations, such as calculating totals for a row or column of data, are possible using Excel. More complicated circumstances, such as calculating mortgage payments, addressing engineering or math issues, and building financial models, may benefit from formulas and features.

Examples of a Formula

A formula is started when a user enters an equal’s sign in a unit.

The following are some examples of formulas:

Examples of a Feature

A feature is started when a user enters an equals sign accompanied by a predetermined set of letters (or hits the Fx button in the formula bar).

3.3 Time-saving Techniques to Enter Data into Excel

There are five popular methods to enter basic Excel formulae while evaluating data. Each approach has its own set of benefits. As a result, before we get into the major formulae, let's go through those techniques; you may set up your selected workflow right away.

OceanofPDF.com

Simple insertion: Writing a formula into the unit

Inserting basic Excel formulae is as simple as writing a formula in a unit and using the formula graph. Typically, the procedure begins with an equal indicator carried by the title of the Microsoft Excel feature.

Excel is clever in that it shows a pop-up feature suggestion as you start entering the feature name. You'll choose your choice from this file. Do not, however, hit the Enter button. And use the Tab button to continue inserting more choices. Or, you can get an incorrect name error, which looks like ‘#NAME?'. Simply pick the unit again and finish your feature in the formula.

Using Input Feature Option from Formulas Key

The Excel Insert Feature dialogue box is all you need whenever you wish to complete control over your feature's insertion. For this purpose, go through the Formula button or choose Insert Feature from the first option. All of the features you'll need to do your financial survey will be accessible in the dialogue box.

Choosing a Formula towards one of the Sets in Formula key

This choice is for people who wish to rapidly get to their favorite features. Click the Formulas key or pick your chosen group to locate this menu. To open the other menu with a record of features, click. You may then choose your preferred option. If your chosen group isn't shown on the key, select the More Features option - it's most likely buried there.

Utilizing AutoSum Feature

AutoSum feature is your first choice for fast and daily jobs. So, go towards the Home page and select the AutoSum option to the right corner. Then enter the caret to reveal additional formulae that were previously concealed. This feature is also accessible after the Insert Feature option in the Formulas tab.

Quick Input: Utilize Recently Used Keys

Utilize the recently used option instead of retyping your latest recent formula if you find it tedious. That is on the Formulas button, right side of AutoSum, as the third menu choice.

3.4 Fundamental Excel Formulas for Workflow

Although you may now enter and feature your chosen formulae, let's look at some fundamental Excel features to get you to begin.

Sum: The SUM feature is the first Excel formula that you should learn. Numbers from an option of rows or columns from your chosen range are typically aggregated.

Excel Formulas END OF MONTH

=EOMONTH may be used to discover the current month's final day, as well as future months' last days. Use =EOMONTH instead of switching back and forth among a calendar and a spreadsheet (START DATE, 0). Take this method a step further and add =EOMONTH to compute the following month (start-date, 1).

OceanofPDF.com

Division

To use the division formula in Excel, type =A1/B1 into the units you want to divide. To divide unit A1 by unit B1, this formula utilizes a forward slash, "/." For example, if A1 is five and B1 is 10, the decimal value returned by =A1/B1 is 0.5.

One of the most basic operations you can do in Excel is division. To do so, choose an empty unit, type an equals symbol, "=," and then the two (or more) numbers you want to divide, separated by a forward slash, "/." When you press Enter, your chosen quotient should show in the highlighted box.

Subtraction

In Excel, insert the units you're subtracting in the format =SUM to execute the subtraction formula (A1, -B1). By putting a negative sign before the unit you're removing, you may use the SUM formula to subtract it. For example, if A1 is 10 and B1 is 6, =SUM (A1, -B1) performs 10 + -6 and returns 4.

Subtracting, like percentages, lacks its own formula in Excel, but that doesn't imply it can't be done. There are two methods to subtract any numbers (or values inside units).

Using the =SUM formula

Enter the units you want to minus in the form =SUM (A1, -B1), with a minus number (defined with such a hyphen) until the unit with the value you want to remove. To get the difference between the two units in the parenthesis, press enter.

Using the format, =A1-B1

To minus several values from each other, enter an equals sign, then your first number and unit, a hyphen, then the value, as well as the unit you want to remove. To get the difference between the two numbers, press Enter.

OceanofPDF.com

Multiplication

In Excel, including the units, you are multiplying in style =A1*B1 to execute the multiplication formula. An asterisk is utilized in this formula to multiply unit A1 by unit B1. For instance, if A1 is ten and B1 is 6, the result of =A1*B1 is 60.

You may believe that multiplication numbers in Excel have their own formula or that the "x" character is used to indicate the multiplication of several numbers. It's as simple as using an asterisk — *.

Point out a blank unit in an Excel worksheet to multiply two or more numbers. And, in the format =A1*B1*C1..., input the values and units you wish to multiply together. Every figure in the formula will be efficiently multiplied by the asterisk.

To return your chosen product, press Enter. Take a look at the screenshot above to see how this appears.

PERCENTAGE

To use the % formula in Excel, type =A1/B1 into the columns you want to obtain a percentage for. Highlight the unit, go to the Home tab, and choose "Percentage" from the numbers menu to transform the decimal value to a percentage. Although there isn't a specific Excel "method" for percentages, Excel keeps it easy to transform the number of every other unit into a %, so you're not trapped figuring or rejoining the numbers.

Excel's Home tab has the fundamental option for converting a unit's value to a percentage. Choose this tab, select the unit you wish to convert to a percentage, then choose Conditional Formatting from the drop-down option next to it (this menu key may say "common" at first). And, from the drop-down menu that shows, choose "Percentage." The number of every unit you've marked will be converted to a percentage.

OceanofPDF.com

Random Number Generator

Using =RANDBETWEEN in a spreadsheet, you can quickly pick random integers (SELECT VALUES). You can use this formula to choose integers from data in a spreadsheet. Another use of this technique is to choose a winner from a list of 100 names by instructing Excel to select the winning row.

Array

The syntax of an array formula in Excel is = (Start Value 1: End Value 1) * (Start Value 2: End Value 2), which wraps a basic formula in brace characters. Instead of individual units being added to or multiplied by one another, hitting ctrl+shift+center will compute and return values from several ranges.

It's simple to calculate the sum, product, and quotient of individual units using the =SUM formula and the units, values, or range of units you wish to work with. But what if there are several ranges? How do you calculate the total value of a large number of units?

Numerical arrays are a convenient method to run many formulas in a single unit at the same time, resulting in a single final sum, differential, product, and quotient. The array formula in Excel is ideal for calculating total sales income from a number of sold items, for example. Here's how you'd go about doing it:

To begin utilizing the array equation, write "=SUM," then put the first of two (or three, and four) unit ranges you'd want to multiply together in parenthesis. Here's an example of how far you've come: =SUM (C2:C5)

After that, after the final unit of the initial range you used in your calculation, add an asterisk. Multiplication is denoted by this symbol. Enter your second range of units after this asterisk. This second range of units will be multiplied by the first. =SUM (C2:C5*D2:D5)

Are you ready to hit the Enter key? Not so quickly... Excel reserves a separate keyboard action for arrays due to the complexity of this

calculation. Ctrl+Shift+Enter after closing the parentheses on your array formula. This will treat your formula as an array, enclosing it in brace characters and correctly returning the product of the two ranges.

OceanofPDF.com

Chapter 4: MS Excel’s Text Function

4.1 CONCATENATE

=CONCATENATE a handy formula for combining values from multiple cells into a single cell. When you need to merge data from several cells into one cell, this method saves you time and hassle. Instead of having to do it by hand, =CONCTENATE can accomplish it in half the time and with half the number of clicks.

4.2 PROPER

Excel may be used for more than simply data analysis; it can also be used to organize and sort data. =PROPER is an excellent formula to have in your back pocket when entering huge quantities of text into Excel since it transforms a cell of text to a larger sense, where the initial letter of each word is capitalized, and the rest of the letters are lowercase.

Left, Right and Mid

When you need to remove the leftmost characters from a string, use the Left function. Syntax =left (text, num_char)

The Right function may also be used to retrieve the rightmost letters from a string.

To extract characters from the center of a string, use Excel's Mid function. Syntax = MID (text, start_char, num_chars)

4.3 Trim

The TRIM function ensures that disorderly spaces do not cause problems in your routines. It guarantees that there are no vacant spots. TRIM only works on a single cell, unlike other functions that may work on a group of cells. As a result, it has the drawback of duplicating data on your spreadsheet.

OceanofPDF.com

=TRIM( text )

Example:

TRIM(A2) – Fills in the blanks in cell A2's value.

4.4 Len

The Len function in Excel calculates the size of a string, which is the total number of lines in the string. Syntax = LEN(text) . When determining length, spaces are taken into account.

4.5 Find ()

When you need to know the location of certain characters in a string, you may use Excel's Find function. Syntax =FIND (find_text, within_text, [start_num])

4.6 Search Function

The SEARCH function returns the location of one text string inside another (as a number). If the search string appears more than once, SEARCH returns the location of the first occurrence. SEARCH does not care about the case, but it does accept wildcards. To conduct a case-sensitive search, use the FIND function. SEARCH gives a #VALUE error if it fails to locate anything. It's also worth noting that if the find text is null, SEARCH will return 1. When find text originates from a cell and the cell is empty, this may result in a false positive. =SEARCH (find_text, within_text, [start_num])

4.7 Textjoin Function

With a delimiter between each value, the TEXTJOIN Function combines figures from 2 and more than two strings.

OceanofPDF.com

=TEXTJOIN (delimiter, ignore_empty, text1, [text2], …text_n)

The following parameters are passed to the TEXTJOIN function:

Delimiter

The line that separates every text number in the resultant string. A comma and a space figure are the most frequent delimiters.

Ignore empty

This parameter determines whether or not blank cells are used in the final text. Unless the parameter is TRUE, empty values will be ignored. When it is wrong, the blank values will be added to the outcomes.

Text 1, text 2,.text n

The texts we want to combine. We may use the TEXTJOIN method to combine approximately 252 lines consecutively.

4.8 Lower Function

A text string is converted to all lowercase characters using the LOWER function. Text, which may be a text value or a cell reference, is the only parameter for the LOWER function. LOWER lowercases all uppercase letters in the text value. The formatting of numbers, punctuation, and spaces is unaffected. LOWER removes the number formatting from numbers and converts them to text.

=LOWER (text)

4.9 Upper Function

Excel's Upper function transforms text from lower case to upper case.

Syntax =UPPER (Text)

4.10 Substitute

The Excel substitute function allows you to replace current text in a string with new content.

Syntax =SUBSTITUTE (text, old_text, new_text, instance number)

OceanofPDF.com

Chapter 5: MS Excel’s Counting and Logic’s

Features

5.1 IF

When you wish to arrange your content according to the rules, the IF feature is often employed. The greatest thing about the formula of IF is that it allows you to include formulae and features.

If the value at C3 is smaller than the value at D3, the condition is true. If the reasoning is correct, set the cell value to TRUE; otherwise, set it to FALSE.

5.2 MAXIMUM & MINIMUM

These MAXIMUM and MINIMUM features assist in determining the extreme numbers in a span of outcomes.

5.3 EVEN & =ODD

This formula is useful when dealing with data that has a lot of decimals. =EVEN rounds up to the closest even number, whereas =ODD rounds up to the nearest odd number. If you're dealing with negative values, these formulae will round down to the closest even and an odd number.

5.4 Not Feature

The feature may be used to see whether two values are not equal. It will return TRUE if we provide it TRUE and FALSE if we give it FALSE. As a result, it will always return a logical value in the other direction.

The NOT feature comes in handy as a financial analyst when we need to know whether a certain condition was not fulfilled.

OceanofPDF.com

Formula

A logical and numerical value should be used as the argument. If the provided logical argument is a numeric number, zero is regarded as FALSE, or any other numeric value is considered TRUE.

5.5 OR

The OR feature is a rational feature that may be used to evaluate several conditions at once. OR returns one of two values: Right or Wrong. To check A1 for "x" or "y," for example, choose this;

The OR feature, coupled with the AND feature, may be utilized as a logical test within the IF feature to prevent unnecessary nested IFs.

The OR feature verifies several logical conditions at once, approximately 255 criteria, which are provided as logic. Each case (logical1, logical2, etc.) should be a right or wrong-returning expression or a TRUE or FALSE-evaluable item. Cell references, logical expressions, arrays, and constants may all be used as inputs to the OR feature.

An OR feature is utilized to evaluate several logical checks at once and output right when any results are right.

The OR feature will examine all of the input numbers or return right when any of them are right. The OR feature may return wrong if all logical evaluate to wrong. Any integer other than zero will be examined as right in Excel.

AND/OR features will combine the results into one value. Consequently, they cannot be utilized in array procedures that need an array of outputs to be returned. You can utilize Boolean logic to get around this restriction. See Array formulae using AND/OR logic for additional details.

5.6 AND

The AND feature in Excel is a logical feature that is used to need several conditions simultaneously. AND either return right or wrong. Use =AND (A1>0, A110) to see whether a number inside A1 is higher than zero and less than ten. The AND feature, coupled with the OR feature, can be used as a logical test within the IF feature to prevent unnecessary nested IFs.

The AND method verifies several logical conditions at once, up to 255 factors, which are provided as arguments. Each parameter (logical1, logical2, and so on.) must be a right or wrong-returning expression and a right or false evaluable item. Equation, cell references, arrays, and logical statements may be used as inputs to the AND feature.

The AND feature evaluates several logical tests simultaneously and returns TRUE only if all of the results are TRUE.

5.7 IFS FEATURE

The IF feature executes a rational test or returns a single value when the result is right, and the next one is wrong.

The above-mentioned example, to "pass" scores over 70. Suppose features may be nested to test several conditions. To expand the logical test, the IF feature may be coupled with logical features like AND/OR.

When the IF method is utilized to perform a logical test, it responds differently based upon whether the result is True and False. The first parameter, logical test, is a phrase that returns TRUE and FALSE when evaluated. Although both values if true and value if false are allowed, at least one should be supplied. IF may produce an output, a cell pointer, and maybe another equation as a result.

We wish to award either "Pass" or "Fail" depending on a test score in the example above. A score of 70 or better is required to pass.

The IF feature may be used in a "nested" fashion. A "nested IF" formula is one in which at least a single IF feature is situated within another to test for many criteria and produce multiple potential outcomes. Each IF statement must be properly "nested" within another to ensure that the reasoning is accurate.

5.8 Contact

The COUNTA feature in Excel is a built-in statistical feature that measures the amount of non-blank (non-empty) cells in a cell range and cell citation. Cells A1 or A3 are filled with data, while cell A2 is blank.

The COUNTA feature may be used to count cells that contain a variety of data values. Text, values, Boolean numbers, date/time values, relative error, but also empty text (") are all examples of this. It gives you a numerical value.

How to Count Non-Blank Cells using COUNTA?

When there is a requirement to count the number of cells in single or multiple non-blank ranges, the COUNTA feature is utilized. Non-adjacent cell ranges are also possible.

The feature also counts the number of value parameters supplied. The value argument is a non-cell or non-range-of-cells parameter.

COUNTA is a feature that counts the following types of data:

The number of consumers on a register

The number of dealings in a specific period

The number of student exams delivered

The number of workers who have access to e-mail

The total number of presentations given

5.9 Count

The COUNT procedure refers to the number of cells in a span that solely contain a numerical value.

5.10 Countblank

The COUNTBLANK feature is classified as a STATISTICAL feature in Excel. COUNTBLANK is a feature that counts the number of empty cells in a span of cells. The feature may be helpful in financial analysis for identifying and collecting blank spaces in a specified range.

=COUNTBLANK(range)

Remember that the COUNTBLANK feature does not count any cells that include text, numbers, errors, or other data. Formulas that yield an empty string (") will be counted as blank.

The COUNTBLANK feature counts a cell as blank if it contains an empty text string and a formula that produces an empty text string.

Cells with a value of 0 are not considered blank, and they will not be counted.

OceanofPDF.com

Chapter 6: Conditional Features in MS Excel

6.1 AVERAGE IF

The AVERAGEIF feature in Excel presents the sum of values inside a span that satisfies specified conditions.

AVERAGEIF finds the average of values in a region that satisfies specified criteria. Numbers, strings, and references may all be used as criteria. Acceptable criteria may include 10, ">10," A1, or ""&A1.

The average range parameter is not required. AVERAGEIF calculates the average of mathematical equations in the range argument if the average range is not specified. AVERAGEIF will compute the average of the values in the average range if the average range is specified. For partial matching, AVERAGEIF criteria may contain rational operators (>,>,=) or place cards (*,?). AVERAGEIF is one of eight Excel features that divide rational requirements into two halves (range + criteria). Consequently, the syntax for creating criteria has changed, and AVERAGEIF needs a cell range for spectrum parameters; an array cannot be used.

Even when the conditions consider, AVERAGEIF will disregard empty units. In other words, AVERAGEIF will not include empty units in the average if they satisfy the criterion for zero. If no units in the range satisfy the requirements, AVERAGEIF returns #DIV/0!

Only one condition may be used using the AVERAGEIF feature. Use the AVERAGEIFS feature if you need to apply several criteria.

6.2 AVERRAGEIFS

The AVERAGEIFS feature in Microsoft Excel determines the total (arithmetic mean) among all values inside a span of units using various factors.

The AVERAGEIFS feature is a Statistical Feature that comes with Excel. It may be used in Excel as a worksheet feature (WS). The AVERAGEIFS feature is a worksheet feature that may be used in a formula in a worksheet cell.

6.3 SUMIFS FEATURE

SUMIFS is a feature for calculating the sum of units that satisfy several criteria. When neighboring units satisfy requirements based on dates, numbers, or text, SUMIFS may be used to total data. For partial matching, SUMIFS supports rational operators (>,,>,=) or place cards (*,?).

The SUMIFS feature adds units inside a span based on the criteria specified. SUMIFS, which, unlike the SUMIF feature, may apply several sets of criteria with multiple ranges. The first region is the one that will be added together. The criteria come in pairs (range/criteria), with just the first pair being needed. Provide an extra range/criteria pair to apply specific factors. There are a total of 127 range/criteria pairings that may be used.

For partial matching, rational operators (>,,>,=) or place cards (*,?) may be used. Criteria may also be dependent on the value of another cell, as shown in the example below.

SUMIFS is one of eight Excel features that divide rational requirements into two halves (range + criteria). As just a consequence, the syntax for creating requirements has changed, and SUMIFS needs a variation in data for limit parameters rather than an array.

The SUMPRODUCT and/or FILTER features may be used to modify data inside a span argument (for example, extracting the year from dates to use in criteria).

6.4 MINIFY

After applying one and more than one condition, the MINI FS feature may be used to determine a minimum value in data. Range/criteria pairs are used to apply conditions. MINI FS has the capacity to hold 126 range/criteria pairings. The size of each criterion range provided must be the same as the size of the min range.

Dates, numbers, and text may all be used as criteria in the MINI FS feature. MINI FS supports partial matching with rational operators (>,,>,=) or place cards (*,?).

Even when the requirements meet, MINI FS will disregard empty units. To put it another way, MINI FS will not count empty units that satisfy the requirements as zero. MINI FS, on the other hand, will return 0 if no units meet the requirements.

MINI FS is one of eight Excel features that divide rational requirements into two halves (range + criteria). According to consequence, the syntax for creating criteria has changed, and MINI FS needs a variation in data for region parameters rather than an array.

6.5 COUNTIES

The COUNTIFS feature in Excel calculates the number of units that satisfy one and more than one condition. COUNTIFS can handle criteria on dates, figures, text, as well as other variables. For partial matching, COUNTIFS offers rational operators (>,,>,=) or place cards (*,?).

In Excel, the COUNTIFS feature calculates the values of units inside a span that meet one of the provided conditions. COUNTIFS, unlike the previous COUNTIF feature, may apply several conditions at the same time. Only the first pair of range/criteria pairings is needed when using conditions. You must provide a new range/criteria pair for each subsequent condition. There are a total of 127 range/criteria pairings that may be used.

For partial matching, rational operators (>,,>,=) or place cards (*,?) may be used. Rules may also be dependent on the value of some other cell, as seen in the example beneath. COUNTIFS is one of 8 Excel features that divide rational requirements into two halves (range + criteria). As a consequence, the syntax for creating criteria has changed, and COUNTIFS needs a variation in data for range parameters; an array cannot be used.

6.6 AVERAGE

Simple averages of data, such as the average number of shareholders in a particular shareholding pool, should come to mind when using the AVERAGE feature.

OceanofPDF.com

Chapter 7: Functions of Date and Time, Entering

and Managing Data

7.1 Weekday Function

The WEEKDAY method represents a date and produces a value from 1 to 7 that represents the weekday. The serial number and return type parameters are sent to the WEEKDAY function. The serial number must be a proper Excel date formatted as a serial number. The return type is a numeric code that specifies which day of the week is treated as the first.

=WEEKDAY (serial_number, [return_type])

7.2 Date Function

The DATE function in Microsoft Excel delivers the numeric date value for a date. The Schedule function is an Excel built-in function that is classified as a Time/Date Function. It may be used in Excel as a WS. The DATE function is a worksheet function that may be used in a formula in a worksheet cell. If you're searching for the VBA version of the DATE function, please see our DATE function (VBA) page since it has a completely different syntax.

=DATE( year, month, day )

7.3 Edate Function

The EDATE function in Excel produces a date that is on the same day of the month, n months ago or in the future. Expiry dates, credit limit, and other due dates may all be calculated using EDATE. To obtain a date in the future, use a positive number for months and a negative value for dates in the past.

=EDATE (start_date, months)

7.4 Datedif

The DATEDIF function in Microsoft Excel calculates the difference between the two date values depending on the given interval.

The DATEDIF operation is a Date/Time Function that is included in Excel. It may be used in Excel as a worksheet function. The DATEDIF function is a worksheet function that may be used in a formula in a WorkSheet cell.

=DATEDIF( start_date, end_date, interval )

7.5 End of Month

The EOMONTH function may be used to determine the final day of a month based on a specified date.

=EOMONTH(B5,0)

7.6 Today & Now

The parameters NOW and TODAY are two critical functions. They let you build dynamic workbooks based on your computer's system date.

We'll utilize these features to make workbooks like this one.

Worker timesheet with an automatic calendar

Payments to suppliers are being tracked.

Delays in delivery

=TODAY()

=NOW()

7.7 Network Day

In Excel, the NETWORKDAYS Function decides the number of working days between two dates. The number of weekends is automatically omitted when using the function. It also enables you to exclude certain holidays from the tally and just count working days. It's classified as a Time/Date Function in Excel. This method may be used in Excel to compute working days between two dates. The net workdays function is helpful in finance and accounting for determining employee benefits due on days worked, the number of workdays available throughout a task, or the number of business days needed to address a customer issue, among other things. =NETWORKDAYS(start_date, end_date, [holidays])

7.8 Work Day Function

Generally speaking, the Workday function in Excel is classified as a Date/Time function since it returns the date that occurs after or before a given number of working days, ignoring weekends and holidays, is provided.

A weekday in Excel is a highly efficient function that can be used for a variety of tasks such as counting working days, work schedules, Excel Gantt chart creation, workday scheduling, computing the number of workdays worked by an employee and performing other financial analysis tasks.

=WORKDAY(start_date, days, [holidays])

7.9 Entering Data

Simply pick a cell and start typing to input data in Excel. The text will display in both the cell and the formula bar above. Enter to inform Excel to approve the data you've entered. The data will be inserted right away, and the cursor will move one cell down.

7.10 Editing Data

Double-clicking the cell location or using the Formula Bar may modify the data that has been put in it. You may have noticed that the data you entered into a cell location displayed in the Formula Bar as you wrote it. The Formula Bar may be used to input data into cells as well as modify data that has previously been entered. The steps that follow show how to input and then modify data that has been entered into a cell location.

7.11 Auto Fill

Excel includes a function that allows you to input data automatically. You may use the Automatic update command to automatically expand a predictable sequence (for example, 1, 2, 3...; days of the week; hours of the day). You can also use AutoFill to propagate formulae; put up the formula once, then use AutoFill to propagate it to the rest of the cells.

7.12 Moving Data

You can transfer data from one cell to another in Excel, so you don't have to enter the data into a new cell and then delete the data from the previous one. Because the layout of a worksheet has changed, you may need to rearrange data inside it.

7.13 Adjusting Columns and Rows

A new workbook's row and column sizes are set to almost the same width and height by default. You may change the column width and row height in Excel in a variety of ways, including covering text and combining cells.

7.14 Hiding Columns and Rows

You may find that there is information on your worksheet that you no longer need. You may also wish to print just columns A–F and columns H– J, omitting column G when printing your worksheet. You may just conceal the information rather than rearranging your worksheet for any of these instances. In addition, you may conceal information in particular cells.

7.15 Inserting Columns and Rows

Choose the row above or the column to the right of the insertion location. Alternatively, you may click on any cell in that row or column.

To open the drop-down list for the Insert button, choose Home → Cells and press the icon to the right of the Insert button.

Select Insert Page Rows or Insert Page Columns from the menu.

OceanofPDF.com

Chapter 8: Excel Problems and their Solutions

with Tips and Shortcuts

8.1 Excel Security

Whether you distribute it internally or outside, you may wish to protect your Excel file from being inadvertently changed or overwritten. A few methods to safeguard your work if you don't want someone to remove your formulae or information. You may lock your data in a worksheet structure or a workbook, and you can even create a password to protect it.

You may secure a workbook to prevent users from removing, moving, or renaming worksheets, as well as viewing hidden worksheets. Click the Protect Workbook button on the Review tab.

8.2 Hashes in Cell

The most frequent reason for this issue is that the cell is too small to show the computed value. If a cell isn't big enough to show the full number, certain cell formats (such as decimals) may be reduced. However, certain formats (for example, dates and times) need that the cell is large enough to show the full value. If the cell is too small, a row of hashes will be shown.

8.3 Solution

Changing the cell width is a simple solution to this issue. Slide the bar dividing the column headings (as seen in the picture on the right) until the cell is larger.

If you double-click on this strip, the cell will resize itself to accommodate the information.

8.4 Copy Formula

Whenever you duplicate and paste formulae in Excel, the cell references are automatically adjusted. Consider the case when I have the expression =A1+A2 in cell B1, when I copy cell B1 and put it in cell B2, the formula changes to =A2+A3. This occurs because Excel changes the references to ensure that the columns and rows now relate to the updated rows and columns.

8.5 Fill in the Cells Rapidly

In cell A1, type 1.

Select Home –> Editing –> Fill –> Series from the drop-down menu.

Quickly Fill Cells with Numbers Without Dragging Fill in the blanks in Excel Series.

Make the following choices in the Series dialogue box:

Columns in series

Linear in nature

One as a step value

Excel - Series DB - Fill Numbers in Cells Quickly without Dragging - Stop Value: 1000

Click the OK button.

The digits 1 to 1000 will be filled in the cells A1:A1000.

Select ‘Rows' in the ‘Series in' choices if you wish to fill the values in the row rather than the column.

Fill in cell A1 with a value other than one if you wish to start from a different place. You may also choose a different step. Select 2 to obtain all digits with a two difference, for example.

8.6 Choose to Remove Duplicate Key

Duplicate Remover may be started by clicking the button on the Ablebits Data tab.

Select the range from which duplicate entries should be removed.

Select the Duplicates checkbox.

Leave just the columns of interest selected and uncheck the others to identify and eliminate duplicates by key columns.

Select Delete values from the drop-down menu. Finish by clicking the Finish button.

8.7 Fast Aggregation

The Excel AGGREGATE function produces an aggregate calculation, such as AVERAGE, COUNT, MAX, and so on, with hidden rows and mistakes ignored if desired. A set of 19 operations is possible, which are indicated in the first parameter by the function number.

8.8 Formula View

Excel, by default, shows the results of formulae in the spreadsheet rather than the formulas themselves. You may, however, have Excel show the formulae so you can see how they're put together.

Navigate to the Formulas tab.

To see the formulas, click the Display Formulas button.

To conceal the formulae, click the Display Formulas button once again.

8.9 IF Error

Excel's IFERROR function is used to catch and handle mistakes in formulae and computations. IFERROR examines a formula and returns another value you provide if it evaluates to an error; otherwise, it returns the formula's result.

IFERROR(value, value_if_error

8.10 IFNA

If a formula produces a #N/A error, the Microsoft Excel IFNA function provides an alternative value. The IFNA function is an Excel built-in function that is classified as a Logical Function. It may be used in Excel as a worksheet function (WS). The IFNA function may be used as part of a formula in a worksheet cell as a worksheet function.

IFNA( formula, alternate_value )

8.11 Importing Tables from the Internet

After you've found the website with the information you need, you can use the From Web tool to import the data straight into Excel with just a few clicks, adjusting the import settings along the way.

The following steps will show you how to import a data table from the internet:

Excel should now be open.

Choose From Web in the Get & Transform Data category on the Data tab. The dialogue box "From Web" will appear.

Screenshot of the Excel Data tab's From Web tab

Choose Basic, then enter or paste the URL into the box and click OK. Select Link to the website if asked.

Image of the From the Web dialogue box

Choose the tables to import in the Navigator box. Excel separates them if it understands how to parse content blocks (text, tables, and images). Place a checkmark next to Select various units to import more than one data asset.

A screenshot of Excel demonstrating the ability to import data from the Web is shown on a PC.

A preview displays on the right side of the box when you choose a table. Select Load if you wish to load the table. In a new worksheet, the table appears.

The Queries & Connections window is located on the right side of the screen. Choose a table from the Queries & Connections window to see if you've imported several tables.

8.12 Excel Shortcuts

8.13 Tips and Techniques for Excel 2021

Select All with One Click

You may be familiar with the shortcut of Ctrl + A shortcut for selecting all, but few are aware that all data may be chosen in seconds with just one section of the corner option, as seen in the image below.

Opening Files in Bulk

When you have many files to manage, there is still a convenient method to open them all with just one click rather than opening them one after one. Just select files you want to open and then hit the Enter button on your keyboard; all of the files will be opened simultaneously.

Switching Between Excel Documents

It's very inconvenient to switch between spreadsheets when we have many open files. You may easily switch between various files by pressing Ctrl + Tab. This feature applies to several other files, such as various Windows tabs in the browser while running Windows.

OceanofPDF.com

Creating New Shortcut Menu

Save, Undo Typing, and Repeat Typing are the three most used shortcuts on the top menu. If we'd want to utilize additional shortcuts, such as Copy-Cut, we may do so as follows:

Construct a Diagonal Line in a Cell

You may require a diagonal connection in the first box to divide various characteristics of columns and rows while making a classmate's address book, for example. What is a great way to maximize it? Everybody understands that Home->Font-> Borders allows you to alter a cell's border and even add various colors. If you select More Borders, though, you'll receive more surprises, such as a vertical line. You may now create it right away by clicking it and save it.

Multiple new columns or rows should be added.

You may be familiar with the process of changing a second new column or row, but if you need to add and over a few of these, doing this procedure X times will consume a significant amount of time. In order to add X rows over or to the left, the simplest way is to drag and choose X rows or columns from the drop-down menu (X is more than one). You may create a new row or column by right-clicking the selected rows or columns and selecting Insert/Add from the drop-down menu. There will be additional rows added over or to the outside of the row that you previously selected.

Copy and Move data in cells as quickly as possible

If you wish to transfer a column of data rapidly in a spreadsheet, choose it and transfer the mouse to the border of the spreadsheet. When the cursor turns to a cross-arrow icon, you may drag the column around as much as you like. Is it OK if you need to print or copy the information? Hold down your Ctrl key while dragging to reposition the data; the column will replicate all of the data from the previous column.

OceanofPDF.com Delete Empty Cells in a Short Period of Time

Some data standards include identifying will be missing for various reasons. If you need to eliminate them for accuracy reasons, especially when calculating the average number, the fastest way is to filter out empty cells and remove them all at once. Select the column you want to filter, then go to Data->Filter and undo. When the horizontal button comes, choose All, and then Blanks as the last option. All of the blank cells will emerge at the same time. Go to Home and select Delete from the drop-down option to remove all of them.

Using a Wild Card in a Vague Search

You probably know how to use Ctrl + F to start a fast search, but there are two main wild cards used during Spreadsheets to start a nonspecific search: Asterisk and Question mark. This is utilized when you're not sure what you want to happen. A question mark represents one character, while an asterisk represents one or more letters. What if you really need to locate a result that has both a question mark and an asterisk? Remember to add a Wave Bar at the top of everything.

Create a One-of-a-Kind Value in a Rows and Column

Even though you are aware of Filter's primary function, few people are familiar with the concept of Advanced Filter, which is often used when a particular value must be extracted from data in a column. After choosing the column, go to Data->Advanced. A pop-up window will appear. Copy to a different location, which should be the second red rectangle area. Then, to choose the target location, enter the number or select the region button. Column C may be utilized to generate a unique age, which in this instance will be displayed in Column E. Only unique records should be selected, then click OK. It is recommended that you transfer to a separate location since the particular feature in row E may vary from the original data in column C.

OceanofPDF.com

Shift data from a column to a row

You'd use this functionality to shift data for a better presentation; however, if you know how to utilize Paste's Transpose function, manually inputting all material will be the last step you'd have to do. Here's how to do it:

Move the cursor to another blank spot and copy the data you wish to transpose.

Go to Home, then Paste, and then choose Transpose. This function will not work unless you first copy the desired data.

Thoroughly Hide Data

Almost many users are aware of how to conceal data by clicking right and selecting the Hide feature, but in case there is just a little amount of data, this may be readily seen. The Format Cells method is the finest and simplest approach to completely conceal data.

Create a text using &

It is not essential to utilize a complicated formulation for as long you understand how to utilize &. Using this symbol, you have complete freedom to create whatever word you choose. To begin, select the cell which will be used to display the composite result and enter the formula using the & symbol. When you press Enter, all of the texts in B2, A2, D2 and C2 will be combined to become LzUS55@ in the F2; this is the result of all of the texts in A2, B2, C2, and D2 being combined.

OceanofPDF.com

Transforming the Text Case

we've done our best to prevent complex formulation by using all of the tips and techniques provided here. However, there are certainly straightforward and basic formulations to demonstrate, such as LOWER, PROPER, and UPPER, which may be used to alter texts for a variety of reasons. A word's initial character will be capitalized in all cases; however, text may be changed to entirely lower case using LOWER; and ONLY PROPER will capitalize the first letter of a phrase.

Starting with 0 as an input value

Whenever the input value began with a zero, Excel automatically deletes the zero. This issue may be readily addressed by inserting a single quotation. Before the first zero, make a mark, rather than resetting the Format Cells.

With AutoCorrect, you can input complicated terms faster.

If you have to, enter the exact same value again. Although it is difficult to do so, the best option is to utilize the AutoCorrect feature, which will automatically update your content with the right information. Consider a name, Alice Smith, which may be shortened to AS. As a result, if we type AS, it will autocorrect to Alice Smith. Replace the content with a proper content in the red rectangle section of File->Options->Proofing->AutoCorrect Options, as shown below.

Get More Status through One Click

The status of data at the lower of an Excel sheet, such as Sum and Average Value, is familiar to most users. However, did you know that you can obtain additional information by moving the cursor to the lower Tab and then click right

OceanofPDF.com

Double-click a sheet to rename it

Renaming sheets may be done in a variety of ways, but most excel users will just right-click and select Rename, and we know it wastes our time. The simplest method is to click double, after which you may rename it immediately.

OceanofPDF.com

Chapter 9: Charts, Graphs and Pivot Table in MS

Excel

9.1 Creating Pivot Table

Create an Excel table to arrange the data into rows and columns before creating a pivot table. You may build a pivot table once you've prepared your source data. First, check which pivot table designs Excel recommends.

Any cell in the source data table may be selected.

Select the Insert tab from the Ribbon.

Click Recommended PivotTables in the Tables group.

Scroll down the list in the Recommended PivotTables box to view the recommended layouts. To see a bigger version of a layout, click on it.

suggested pivot tables

Click OK after selecting the layout you wish to use.

9.2 Filtering

AutoFilter is the fastest and most convenient method to filter data in a table (or list). When we say "filter," we're simply saying that we're temporarily concealing the rows we don't want to view. This feature shows drop-down lists at the top of each column and enables the user to filter the data by selecting unique values within each column. These drop-down lists may then be used to filter the data in your table, either singly or in combination. When you "filter" a table, any rows that don't match the value you selected from the drop-down list are buried.

The filters available in each column are determined by the data category. Number Filters, for example, are available in columns with numbers, whereas Text Filters are available in columns with text, and Date Filters are available in columns with dates. In each category, there are several built-in sorting features that may be useful:

Date Filters

These filters are very durable. Data may be sorted by day, month, year, week, and a quarter.

Text Filters

The most useful default text filters are Begins With, Contains, and Does Not Contain. You may build a new filter if none of the current filters meet your requirements. We'll accomplish it immediately with the help of Dates.

As previously discussed, click the arrow next to the column you wish to sort, then choose Custom Filter from the Filters for that column. The Custom AutoFilter box that appears is extremely user-friendly. Make a phrase that explains the filtering you want using the drop-down options.

Number Filters

Top 10, Below Average and Above Average are the most helpful for businesses.

9.3 Change Summary Calculation

When you build a pivot table report, Excel defaults to collecting or summing the elements to summarise the data. You may wish to use functions like Min, Max, and Count Numeric instead of Sum or Count. There are a total of 11 choices available. However, the most frequent cause for changing a summary computation is that Excel has opted to count rather than total your data erroneously. When you insert a numerical field to the Values section of a pivot table, the default summary function is Sum or Count. The default function cannot be altered; it is determined by the values of the field:

The sum will be the default if the field includes numbers.

The count will be the default whether the field includes text or blank cells.

To choose a different summary function once a field has been added to the pivot table, follow these steps:

Change a cell in the Value field by right-clicking on it.

Select Summarize Values from the pop-up menu. By

Select the Summary Function you wish to utilize by clicking on it.

9.4 Sorting Data by Specific Trait

Whenever you are sorting data, it is critical to determine whether you want the sort to apply to the whole worksheet or just a certain cell range.

Sort range

In a range of cells, you may sort the information in that range, which can be useful when dealing with a sheet that includes a number of tables. The sorting of a range has no effect on the rest of the worksheet's information. It is possible that the default sorting choices will not be able to arrange data in the order that you need it to be. Excel, on the other hand, enables you to build a custom list in which you may choose your own sorting order.

Choose a cell in the column you wish to sort by and press Enter.

Select the Data tab and then the Sort command to sort the data.

On the Data tab, there is a Sort button.

The Sort dialogue box will be shown after that. Then, in the Order box, choose Custom List... from the drop-down menu for the column you wish to sort by.

The Custom Lists dialogue box will be shown after that. Choose NEW LIST from the Custom Lists: drop-down menu.

Fill in the List entries: box with the items in the custom order you want them to appear.

Sort sheet

One column is used to arrange all of the information in your worksheet. When the sort is performed, all of the information that is related across each row is retained together.

9.5 Fine Tune the Calculator

You already know that when you enter a field to the Values box, Excel makes a guess as to what calculation you want to conduct on the data. Generally, it is assumed that you wish to execute a sum operation on the field, which totals all of the values in the field. This computation, on the other hand, is not necessarily the correct one. Because Excel makes it simple to alter the kind of calculation you're doing, you can save time. In fact, as you'll see in the next sections, you may use the same pivot table to conduct several calculations at the same time, as well as to include custom formulae into the mix. You may change the computation that the pivot table performs by following the following steps:

Locate the relevant field in the Values box of the PivotTable Field List pane by typing the field name into the search box. By selecting Value Field Settings from the drop-down arrow, you may customize your fields.

The "Sum of Quantity" item in the Values box may be changed if you wish to alter the current operation, which is the summation of the Quantity values for each row in a group.

Select a different choice from the drop-down menu under the "Summarize by" header.

If you wish to specify an alternative number format for the summary information to be shown, click the Number Format button, choose a new format, and then click the OK button.

When you choose Number Format, Excel displays a condensed version of the Format Cells dialogue box, which has just the Number tab and no other tabs.

The number of decimal places may be changed here, as well as the currency symbol, among other things, and so on.

To dismiss the Value Field Settings dialogue box, press the OK button.

As soon as new information is entered into Excel, the pivot table is updated.

9.6 Macros

Macros are pieces of code that automate work in a program; they allow you to add your own small features and enhancements to help you accomplish exactly what you need to do, quickly and with a single click of a button, without having to learn a new language. Macros are a type of code that is used to automate work in a program. When working with a spreadsheet program such as Excel, macros may be very useful. They are more powerful than the usual functions you put into a cell (for example, =IF(A2100,100, A2), which are hidden behind the regular user interface. These macros make Excel do the heavy lifting for you. They take the role of operations that you would normally do manually, such as formatting cells, copying data, or computing totals.

As a result, you may rapidly replace monotonous activities with a few clicks. Create macros by simply recording your actions in Excel and saving them as repeated steps, or you may use Visual Basic for Applications (VBA), a basic programming language that is integrated into Microsoft Office, to create macros that are more complex. Understanding how to automate Excel is one of the simplest methods to make your job more efficient, particularly given the fact that Excel is utilized in so many different work processes.

Assume that you export analytics data from your content management system (CMS) once a week in order to produce a report on your website. The only issue is that those data outputs aren't always in an Excel-friendly format, which may be frustrating. They're jumbled, and they often include much more information than is necessary for your report. To do so, you must clear up empty rows, copy/paste data into the appropriate locations, and build your own charts to display data and make it print-friendly.

It may take you many hours to accomplish all of these tasks. If only there was a way to click a single button and have Excel handle everything for you in an instant... Is it possible for you to predict what we're going to say next? All it takes is a few minutes to set up a macro, and then that code may be used to do the necessary tasks on a consistent basis. It's not even close to being as tough as it seems.

9.7 Importance of Pivot Table

A pivot table takes a data field that has been provided by the user and turns the head of each column into a data option that can be readily manipulated by the user in the table. Columns holding data may be readily deleted from, added to, or changed about in a table with relative simplicity. Long spreadsheets of raw data may be transformed into user-friendly and useful summaries with this software. The information may be summarised in a variety of ways, including frequencies and averages. There are many advantages to using a pivot table in Excel, which is detailed here.

Easy to Use

The fact that pivot tables are simple to use is a significant benefit. By moving columns to various parts of the table, you may quickly summarise data. With a click of the mouse, you may rearrange the columns in any way you like.

Easy Summary of Data

Another major advantage of pivot tables is that they make it simple to summarise data out of thousands of rows and columns of unstructured data, the table aids in the creation of a succinct summary. You can condense a lot of information into a little amount of space by using these tables. The information may be summarised in an easy-to-understand manner. Users may name and organize the data in whatever manner they choose, and they can rearrange the rows and columns to suit their requirements.

OceanofPDF.com

Easy Data Analysis

Excel pivot tables allow you to manage huge amounts of data in a single operation. These tables enable you to deal with a huge quantity of data while only seeing a few data columns. This makes huge amounts of data easier to analyze.

Find Data Patterns

Pivot tables in Excel enable you to build customized tables from huge data sets. This kind of data manipulation will aid in the discovery of any recurrent patterns in the data. As a result, precise data forecasting will be easier.

Helps in Quick Decision Making

A pivot table is a useful Excel reporting tool because it enables users to quickly evaluate data and make choices based on it. This is a significant benefit in the industrial sector, where making accurate and fast choices is critical.

Quick Report Creation

One of the most useful aspects of excel pivot tables is that they make it easier to generate reports. This eliminates the need for you to spend lengthy and exhausting hours manually generating reports. Aside from that, the table allows you to include connections to external sources in the report you've prepared.

9.8 Chart and Their Types

Following are the types of charts.

Area

Pie

Line

Column

Bar

Scatter

9.9 The Pie Chart

A pie chart shows a single set of data. A data series is a set of numbers that may be plotted in a row or column. Excel presents the data as proportionate slices of a pie using the row heading series identifier as the chart title. The design of the pie chart may be changed so that numeric values or percentages appear on top of the pie slices.

9.10 The Line Chart

The Line Chart is very useful for showing patterns. The Y-axis (vertical axis) always shows numerical numbers, whereas the X-axis (horizontal axis) shows time or another category. The Line Chart works well for showing patterns in many data.

The Layered Line Chart and the 100 percent Stacked Line Chart are also offered, which may be used with or without markers. Although a three-dimensional line chart is available, it does not show data effectively in three dimensions.

9.11 The Column Chart

The Column Chart analyses one set of data points well, but it really shines when comparing several series. The outline is highlighted in red. Because each data series in Excel is assigned a distinct color, it's simple to observe how a specific series varies over time or to compare several series over a particular time period. The Nested Column Chart is very well-liked.

9.12 The Bar Chart

The Bar Chart is similar to a Column Chart turned upside down. The numeric numbers are shown on the x plane of a Bar Chart. The kind of data and the user's choice determine whether to use a Bar Chart vs. a Column Chart. Creating both charts and comparing the findings is sometimes worthwhile. Bar charts, on the other hand, are better at displaying and comparing a wide number of series than other chart styles.

All bar charts are available in two-dimensional and three-dimensional versions. The Layered Bar Chart and the 100% Stacked Bar Chart are both available in Excel. In addition to the other chart styles, recent Excel versions allow you to use cylinders, pyramids, or cones instead of bars.

9.13 The Scatter Chart

A Scatter Chart is used to compare the values of two series across time or in some other area. Scatter Plots (U. of Illinois) claims that "Scatter plots are identical to line graphs in that they display data points on horizontal and vertical axes. They do, however, serve a very particular function. Scatter plots depict how one variable influences another. Correlation is the term used to describe the connection between two variables."

If they both grow in the same way, the series pair has a Positive Correlation; if they both decline in the same way, the series pair has a Negative Correlation. Otherwise, there is no correlation between them. The horizontal axis is not labeled with labels from the worksheet. The X-axis is chronologically numbered. The Scatter Chart may be made in a variety of ways: the data points can be marked with markers, and the points can be disconnected or linked with smooth or straight lines.

9.14 The Area Chart

Region Charts are similar to Line Charts, except the area under the plotline is solid. Area Charts, like Line Charts, are mainly used to display patterns over time or in other categories. The Layered Area Chart and the 100 percent Stacked Area Chart are also available. Each is available in two formats: 2-D and full 3-D with X, Y, and Z axes. Many series of data are often shown badly in the 2-D form of the Area Chart since series with lower values may be entirely buried.

9.15 Excel Graphs

Excel has many applications in professional life; it allows us to analyze, extract, and sort information from data. One specific feature of Excel allows us to visualize the insights we've obtained from our gathered data. This feature allows us to provide facts in a visual manner that is simple to comprehend. We're talking about excel graphs. The majority of graphs frequently used in statistics are supported by Excel. When it comes to comparing datasets, analysis, and making presentations, creating various kinds of graphs offered in Excel based on our data is extremely simple and straightforward.

9.16 Types of Excel Graphs

The following are the most popular graph kinds in Excel:

Area Graph

Column Graph

Pie Graph

Line Graph

Scatter Graph

The Scatter Graph

When using Excel, a scatter graph is a straightforward depiction of data points that is easy to understand. A comparison of at least two sets of data with a restricted number of data points is performed using this method. Graphs in Excel may be created in a variety of ways, including hierarchical graphs, radar graphs, waterfall graphs, and combo graphs, which are mixtures of two or more graphs. Everything is utilized in accordance with certain criteria met by the data, such as the kind of data and the number of data points, among other things.

The Pie Graph

As the name implies, this graph is data visualization in the shape of a circle or pie. This type of graph is used to display percentages as a whole. Let's take an example. If we wanted to compare between teams who performed how much work, we'd show that through a pie graph in a simple-and-easy-to-understand manner.

Line Graph

The next kind of graph we'll look at is a line graph. When we really need to display data like a rising or reducing series over time, we utilize this kind of graph. This is a great Excel graph to use for showing trends and comparing results.

The Bar or Column Graph

A bar graph, often known as a column graph in statistics, is the next item on the list. When we want to view and compare data over a range, we utilize these various kinds of graphs.

In Excel, you may create stacked columns, 100 percent stacked columns, 3D columns, and other kinds of bar graphs. These graphs may be used to represent large datasets.

The Area Graph

It is accessible from the line graph menu by selecting the area graph option. This is being used for the same purpose as a line graph in that it visualizes trends and allows for data comparisons.

9.17 Creating Tables

In order to insert a table, follow the procedures outlined below.

To choose a single cell inside the data set, just click on it.

The table may be found in the Tables category on the Insert tab.

Excel will choose the data for you on its own initiative. Select 'My table contains headers' from the drop-down menu and clicks on OK.

Excel automatically generates a table with a professional layout for you. Despite the fact that this seems to be a standard data range to you, numerous sophisticated capabilities are now available at the push of a single button.

OceanofPDF.com

Chapter 10: Named Ranges

10.1 Creating Named Range

Named ranges are one of Excel's crusty old features that few people are familiar with. They may seem strange and frightening to new users, and even experienced users may ignore them because they appear useless and complicated.

However, named ranges are a really useful feature. Formulas may be made *much* simpler to write, read, and manage using them. They also make it simpler to reuse formulae (more portable). It's simple and quick to create a named range. Pick a group of cells and name them in the name-box option. When you hit return, the following Name appears.

10.2 Creating a name for a constant in Excel

Click Define Name in the Defined Names group on the Formulas tab.

The dialogue window for a new name appears.

In the Name text box, type the Name.

To provide a tax rate, for example, you might enter a tax rate.

Select the current worksheet's Name from the Scope drop-down box to specify the range name for only that worksheet rather than the whole Workbook. Normally, you should leave the Scope option set to Workbook so that you may apply your constant in a calculation on any of the Workbook's pages. Only change the scope to a certain worksheet if you're certain you'll only use it in formulae on that worksheet.

Change the original cell location with the constant value or a formula that calculates the constant in the option with Refers To text box following the equals sign (=). For example, while using Refers To text field, enter 7.5 percent (or.075, either would work) for a tax rate. Click the OK button.

10.3 Defining any Name for a Formula Now that we've seen how simple Name Ranges and Name Constants are to use, we can explore another lesser-known feature of Names: the ability to construct custom formulae. For instance, assume we often employ the formula which is stored in A101 and is actually.

Duplicated across several columns with the row 101. In this instance, creating a customized formula that performs this in every cell on 101 rows might be preferable.

You may now substitute =SalesLessCosts for the calculation in column A101. Also, you can duplicate this across 101 rows to alter the relative references, exactly as the formula.

The fact that we chose A101 before proceeding to Insert-> Name ->Define and utilized comparative references in

While adding it in the "Refers to" option/box is the cause for this.

10.4 Naming Rules

There are certain guidelines for Excel names, and here's what Microsoft says is permissible. Although it seems to be self-evident, a couple of the rules aren't as unbreakable as they appear. One of the following characters must be the initial character of a name:

underscore (_)

periods

letter

a backslash (\).

letters

The remaining characters in the Name can be

numbers

underscore characters

OceanofPDF.com

The following items are not permitted:

Space characters are not permitted in a name.

A$35 or R2D2 are examples of names that cannot resemble mobile phone numbers.

Excel utilizes the letters C, c, R, and r as selection shortcuts. Thus they can't be used as names.

The case of the names does not matter. North and NORTH, for example, are regarded as synonyms.

10.5 Advantages of utilizing Name

Naming ranges is a convenient method to refer to a particular cell or range without having to memorize its address. Instead, you may refer to it by its Name. This is especially helpful if you'll be referring to that cell often. The following are the main benefits of utilizing named ranges:

Finding a named cell takes less time

Using named ranges in formulae is easier.

VBA references are easier to find.

Adding hyperlinks to documents with ease

10.6 Tables

Excel Tables may have a confusingly common name, but they are jam-packed with capabilities. Excel Tables are for you if you require a range that extends to accommodate fresh data and formulae that remain up to date automatically.

10.7 Creating Tables

Any cell in your data collection may be selected.

Click the Table button or use the Ctrl + T shortcut on the Insert tab in the Tables group.

When you open the Create Table dialogue box, all of the data is automatically chosen for you; you may change the range if necessary. Make sure the My table has headers option is checked if you want the first row of data to become the table headers.

Click the OK button.

10. 8 Adding Data Tables

add a data table to a chart; click anywhere on it.

Although data tables may be added to charts on a normal worksheet, this is not a usual practice since the data is already shown on the worksheet.

Click the Data Table icon in the Labels group on the Chart Tools Layout tab.

None to delete a data table, Show Data Table, and Show Data Table with Symbolic Keys are all options.

Select a Data Table option from the menu.

To view more formatting choices for data tables, click More Data Table Option.

Click the OK button.

10.9 Formatting Tables

In Excel, the Format as Table icon was introduced to the Home ribbon tab. Unfortunately, the majority of people believe it is a formatting option. It is, instead, a database option. You may tell Excel to consider a table as a simple database by selecting the Format as Table icon.

10.10 Finding Data Tables

Table names are displayed alongside other specified names in the Formulas tab of the Ribbon > Name Manager. They have a distinct symbol next to them, but you can use the Filter option at the upper right to display just tables to make things clearer.

OceanofPDF.com

Chapter 11: Benefits, Applications and features of

MS Excel 2021

Excel has introduced some new features that make it all the more effective and smooth. Let's have a look at them. MS Excel, the world's most commonly used split sheet and data management tool when using, offers more versatility and interoperability across devices and systems since it is the most extensively used. Organizing, analyzing, and showing your data with Microsoft Excel has never been easier, regardless of whether you are using a PC, a Mac, an iPad®, an iPhone®, an AndroidTM tablet, or an AndroidTM smartphone. Aside from offering exceptional versatility, better number-crunching capabilities, and simple accessibility, MS Excel also provides many advantages to its users.

11.1 Benefits of Using Excel

MS Excel, the world's most commonly used split sheet and data management tool when using, offers more versatility and interoperability across devices and systems since it is the most extensively used. Organizing, analyzing, and showing your data with Microsoft Excel has never been easier, regardless of whether you are using a PC, a Mac, an iPad®, an iPhone®, an AndroidTM tablet, or an AndroidTM smartphone.

Aside from offering exceptional versatility, better number-crunching capabilities, and simple accessibility, MS Excel also provides many advantages to its users, including the following:

Easy to reformat and rearrange data

Use various color hues, bold, and italics to highlight the essential facts in your spreadsheets. Extremely helpful for displaying a variety of statistics related to the same subject, like accounting information such as pre-tax profit and the company's balance carried forward. Furthermore, MS Excel enables users to choose a suitable color scheme for fast analysis.

OceanofPDF.com

Conditional Formatting

Conditional formatting in MS Excel allows you to alter the appearance of a cell depending on the data it contains. For example, you may choose red for numerals under a thousand and blue for those above a thousand.

Process data and analyze with graphs and charts

MS Excel will number squeeze and evaluate your data to summarise it for a clearer vision with preview choices, giving you the option to choose the best way to tell your narrative if you provide the correct data.

Layout your data

Microsoft Office Excel is a spreadsheet or workbook program that enables you to arrange text data in a worksheet or workbook format. Excel worksheets and workbooks may be used to combine data and information from different files and places into a single file that can be crunched and analyzed.

Sharing and Connectivity

Through Share Point or One Drive, MS Excel enables you to share and work with many other members of your group or friends group. It's as simple as giving each person a link with the same file. After you've saved your MS Excel file to One Drive, you and other users may collaborate on it in real-time.

Identify and analyze trends and patterns in large amounts of data

MS Excel simplifies the process of identifying and analyzing patterns in data that spans up to one million rows and 16,000 columns in length.

11.2 Business Uses

The list of Most Popular Uses of Microsoft Excel in Business is as follows:

Make your work easier

MS Excel has several fantastic features that help us save time by making our job easier. Filtering, sorting, and searching are all very useful techniques. Using these fantastic tools in conjunction with the pivot tables, tables may help you complete your project quicker.

Security

Because it protects your personal information, security is essential. With the assistance of basic visual programming, MS Excel keeps all of your files secure by password-protecting them.

Assist in developing future strategy

It allows you to create charts and graphs, so you may set goals for yourself with the aid of graphs, and pie charts can show you how far you've come.

Spreadsheets and Data recovery

One of the most beneficial features of Excel is if your data is lost for whatever reason, you may simply recover it. There are also spreadsheets, which make work simpler and more consistent.

Storing and Analyzing data in Excel

MS Excel may be used to quickly examine a huge quantity of data in order to spot new patterns. Charts and graphs assist you in better summarizing and retaining data.

Mathematical formulas make calculation easy.

Another notable feature of Excel is the abundance of formulas available for various operations such as determining average, sum, and so on, allowing you to do large computations quickly.

OceanofPDF.com

Online access

The best use of excel is that no matter where you are, you have your device or not, but you can easily excess it online from anywhere and anytime.

Add complexity to data presentations.

Following that, you may utilize MS Excel to enhance the complexity of your data presentations, which means you can enhance the data bars, highlight any particular things that you want to emphasize and make your data even more appealing quickly and simply.

Manage expenses

With the assistance of Microsoft Excel, you can keep track of your expenditures. You may simply create a table of your expenditures, and with the assistance of the mathematical formulae supplied by Microsoft Excel, you can readily determine the total amount owed to your creditors.

Keeps data combined at one place

This is one of the finest Excel applications. You may save your data in a single place, making it easier to locate your files. It saves you time since you don't have to go through all of the data in various directories.

11.3 Features of Excel 2021

Excel has introduced some new features that make it all the more effective and smooth. Let's have a look at them.

11.4 Worksheet Navigation

Click the worksheet title tab of the worksheet to see from the collection of worksheet title tabs in the lower-left side of the workbook to go from one worksheet to another in Excel. Alternatively, you may use the keyboard shortcut “Ctrl” + “PageUp” to return to the previous worksheet. Alternatively, you may use a keyboard shortcut to go to the next worksheet by pressing “Ctrl” + “PageDown” at the same time on your keyboard.

The "Previous Sheet" and "Next Sheet" buttons to the left of the worksheet name tabs can only be used after the worksheet name tabs have slid beneath the horizontal scroll bar, and only then can you use them to relocate the names of the worksheet tabs out from underneath the horizontal scroll bar until you can see the worksheet name tabs you want to view. Then, to see the worksheet, select its worksheet name tab. Additionally, right-click either the "Previous Sheet" or "Next Sheet" buttons to open the "Activate" dialogue box and choose the name of the worksheet to see. To activate the chosen worksheet, click the "OK" button after choosing the sheet name to see.

11.5 Zoom Out/IN

Zooming out and in using the keyboard

You may now zoom in and out in Excel 2016 and subsequent versions by pushing Ctrl + Alt + plus sign (+) or Ctrl + Alt + negative sign (-). (-). Zooming out and in using mouse

To zoom out and in using a mouse-wheel in the center of the mouse, hold down Ctrl while moving the mouse scroll wheel backward or forward.

Using the keyboard to open the Zoom dialogue

You may use the keyboard to zoom out and in of the Zoom dialogue on the Ribbon:

Hold down the Alt key. The Ribbon has key tip badges or labels.

To change the zoom level, use the down and up arrow keys. Enter a zoom percentage if you choose Custom.

To access the View tab, press w.

To bring up the Zoom dialogue box, press q.

Enter the code.

To select zoom levels in Excel, use the zoom dialogue box.

Quick-Access Toolbar will now include zoom buttons.

You could also put zoom buttons on the Quick Reach Toolbar and access them with keyboard shortcuts:

In the Ribbon, select the File tab, then Options. A dialogue box displays on the screen.

In the left-hand category, choose Quick-Access Toolbar.

Select All Commands from the box under Choose-commands.

Select the button you'd want to include. Click Zoom in this instance with a magnifying glass.

For every button, click Add. You may want to include Zoom Out and Zoom In as well.

Close the window by clicking it.

Add zoom controls to Quick-Access Toolbar using the Excel Options dialogue box.

11.6 Excel by Default Keyboard Shortcut

OceanofPDF.com

Conclusion

To organize and manage data, spreadsheet programs like MS Excel utilize a set of cells organized into rows and columns. They can also use charts, histograms, and line graphs to show data. MS Excel allows users to organize data in order to see various aspects from multiple angles. Microsoft Visual Basic is a programming language that may be used to build a range of sophisticated numerical techniques in Excel. Developers have the option of creating code directly in the Visual Basic Editor, which includes Windows for troubleshooting and organizing code modules.

MS Excel, the world's most commonly used split sheet and data management tool when using, offers more versatility and interoperability across devices and systems since it is the most extensively used. Organizing, analyzing, and showing your data with Microsoft Excel has never been easier, regardless of whether you are using a PC, a Mac, an iPad®, an iPhone®, an AndroidTM tablet, or an AndroidTM smartphone.

MS Excel is extensively used these days by everyone since it is extremely useful and saves a lot of time. It has been in use for several years and is updated with new capabilities every year. MS Excel's most remarkable feature is that it could be utilized anywhere and for any type of task. It's utilized for things like billing, database administration, analysis, inventory, finance, business activities, and complicated computations, among other things. It may also be used to do mathematical computations and to store significant data in the form of charts and spreadsheets.

MS Excel protects your files, ensuring that no one else may access or corrupt them. You may password-protect your files with the assistance of MS Excel. MS Excel may be accessed from any location and at any time. If you don't have access to a laptop, you may use your phone to work on MS Excel. MS Excel has so many advantages that it has become an unavoidable part of millions of people's lives. MS Excel offers a variety of tools and features that make work easier and save time.

MS Excel has a plethora of features that make your job a lot easier and save you time. There are fantastic tools for sorting, filtering, and searching that make your job even easier. You can complete your job in much less time if you mix these tools with tables, pivot tables, and other tools. Multiple components may be readily found in huge quantities of data to assist in the resolution of a variety of issues and queries.

Excel, developed by Microsoft, has long been the industry standard in spreadsheet software. You do not, however, have to pay to get access. For more than 30 years, Microsoft's Excel has been the industry standard in spreadsheet software.

It has become a very important tool for companies all over the globe as a major component of the enormously popular Microsoft Office productivity suite. While a Microsoft 365 subscription makes sense in the business sector, it may not be appropriate for you. We'll teach you how to use Microsoft Excel fully legally and for free in this post.

MS Excel will number squeeze and evaluate your data to summarise it for a clearer vision with preview choices, giving you the option to choose the best way to tell your narrative if you provide the correct data.

MS Excel has become a must-have for different types of corporate computing, like looking at daily, weekly, or monthly figures, tabulating payroll or taxes, and other comparable business operations, thanks to the autosum and other improvements. Microsoft Excel has become a major end-user technology, helpful in training and professional development, thanks to a variety of easy application cases. MS Excel has been included in basic business diploma courses on business computers for a number of years, and temporary employment agencies may evaluate people for a variety of clerical tasks based on their abilities with Microsoft Word and Microsoft Excel.

OceanofPDF.com

Table of Contents

Chapter 5: MS Excel’s Counting and Logic’s Features

Chapter 6: Conditional Features in MS Excel

Chapter 7: Functions of Date and Time, Entering and Managing Data

Chapter 8: Excel Problems and their Solutions with Tips and Shortcuts

Chapter 9: Charts, Graphs and Pivot Table in MS Excel

Chapter 10: Named Ranges

Chapter 11: Benefits, Applications and features of MS Excel 2021

Chapter 1: What is Microsoft Excel?

Chapter 2: Understanding and Getting Started

Chapter 3: Formulas and Features of MS Excel

Chapter 4: MS Excel’s Text Function

Chapter 5: MS Excel’s Counting and Logic’s

Chapter 6: Conditional Features in MS Excel

Chapter 7: Functions of Date and Time, Entering

Chapter 8: Excel Problems and their Solutions

Chapter 9: Charts, Graphs and Pivot Table in MS

Chapter 10: Named Ranges

Chapter 11: Benefits, Applications and features of