Excel Formulas and Functions 2024

GuideBook
An in-depth Manual for Beginners to Master the Latest Version of Excel and its Most Useful
Innovative Features

Gerard
Brandon
Copyright © 2024 Gerard Brandon
All rights reserved.

It is not legal to reproduce, duplicate, or transmit any part of this document by either electronic
means or in printed format. Recording of this publication is strictly prohibited

Disclaimer
The information in this book is based on personal experience and anecdotal evidence. Although the
author has made every attempt to achieve an accuracy of the information gathered in this book, they
make no representation or warranties concerning the accuracy or completeness of the contents of this
book. Your circumstances may not be suited to some illustrations in this book.

The author disclaims any liability arising directly or indirectly from the use of this book. Readers are
encouraged to seek Medical. Accounting, legal, or professional help when required.
This guide is for informational purposes only, and the author does not accept any responsibilities for
any liabilities resulting from the use of this information. While every attempt has been made to verify
the information provided here, the author cannot assume any responsibility for errors, inaccuracies or
omission.
 Contents
INTRODUCTION
CHAPTER ONE
About Microsoft Excel
Key Features
Formulas and Functions
Data Analysis and Visualization
Advanced Features
Integration and Compatibility
What's new in Excel?
List of Excel Terms and Terminologies
CHAPTER TWO
Basic Excel Functionalities
How to add new rows and columns
How to create charts
How to use calculators
How to use conditional formatting
How to use text-to-columns
How to use color schemes
How to use auto filter
How to use the cursor
How to use dragging
How to organize
How to use Excel Add-in
How to use Cell Comments
Using Drawing Freehand Shapes
Generating dates and date ranges
How to Customize Ribbons
How to convert text to cells
CHAPTER THREE
Inserting and Formatting
Using Pivot Tables
 Using Illustration
Using Shapes
Using a textbox
How to use Links and Comments
Adding a hyperlink to an Excel workbook
How to use Characters, Colors, Size
How to use Alignment, Merge, and Wrap
Exploring cell data types and addressing common challenges
Conditional formatting and table format
How to add and remove columns/cells
CHAPTER FOUR
Getting to Know the Excel Ribbon Tabs
What are Excel Ribbons?
Excel Tab Components
Understanding the Tabs in Excel
Using the Insert Tab
Using the Page Layout Tab
Using the Formulas Tab
Using the Data Tab
Using the Review Tab
Using the View Tab
Using the Help Tab
CHAPTER FIVE
Getting Started with Excel
How to open Microsoft Excel
How to open a new workbook
Mastering Keyboard Shortcuts
How to launch an existing workbook
How to work on your Excel Sheet
How to zoom in and out of your worksheet
How to enter data into a cell
How to identify a cell name
How to copy and paste data
How to add a new worksheet
How to rename a worksheet
 How to color a worksheet
How to save your file
CHAPTER SIX
Formatting Basics
How to format your data
How to format font style
How to format with bold, italics, and underline
How to format your font color
How to format the bottom border
How to format the number type
How to format overlapped data
How to repeat a format using the format painter
How to format data into a table
How to rotate text directions
How to insert a new row and column into your table
How to delete a cell
How to hide and unhide data
How to Unhide Data
CHAPTER SEVEN
Excel Cell Referencing
Range reference in Excel
List of Excel reference styles
How to create references in Excel
Understanding the Different Types of References
How to change Excel cell reference in a formula
Using the Find and Replace function
Using Relative and Absolute References
Using Excel's Offset Function
How to cross-reference in Excel
Using INDEX and MATCH Functions
Using the IF function to simplify cross-referencing
Using Conditional Formatting
Using Pivot Tables for Cross-Referencing Data
How to reference another sheet in Excel
Using References in Formulas
 Transferring References Between Sheets
How to change between different reference types
How to Change Reference Types
CHAPTER EIGHT
Working with Excel Cells
How to understand Excel cells
How to insert content in a cell
How to delete a cell
How to copy and paste cell content
How to drag and drop the cell
How to use the fill handle in a cell
CHAPTER NINE
Formatting Excel Cells
How to change the font
How to change the font size and color
How to apply background colors using the fill color
How to add borders to the cells
How to change the text alignment of your border
How to change the columns, rows, and cells in a worksheet
How to change the column width
How to change the height of the row
How to wrap text and merge cells
CHAPTER TEN
Excel Formulas
List of Excel Basic Functions
How to delete formulas and keep the calculated value
CHAPTER ELEVEN
Using Excel Formulas and Functions
How to insert a formula in Excel
Using Excel Functions
How to insert a function in Excel
Using Excel formulas and functions
How to use the AVERAGE function
How to use the MAX function
Using the MAX function with multiple ranges
 How to use the MIN function
How to use the TEXTJOIN function
How to use the LEN function
How to use the COUNT function
How to use the TRIM function
How to use the XLOOKUP function
How to use the SORT function
How to use the UNIQUE function
CHAPTER TWELVE
Working with Charts
How to insert a chart
Adjusting the Size and Position of the Chart
Tips for Creating Clear and Concise Charts
Exploring Different Chart Types and Their Applications
Dealing with Chart Problems
How to add a title to a chart in Excel
How to add a title to a chart in Excel
How to change chart style
Changing chart layout in Excel
Customizing the Chart Layout
Guidelines for Creating Charts Design
How to move a chart
How to resize a chart on your worksheet in Excel
CHAPTER THIRTEEN
Data
How to import data text file
How to sort and filter
How to use auto filter
How to use an advanced filter
How to validate data
How to remove duplicates
CHAPTER FOURTEEN
Tips and Tricks in Excel
List of available Excel shortcuts
How to use Ideas
 How to remove blanks from a worksheet
How to get rid of duplicate data
How to transpose on your worksheet
How to add text to columns
How to insert a screenshot into your workbook
How to insert multiple rows
How to Create People Graph
How to highlight text and numbers
Excel tips to optimize your use
CHAPTER FIFTEEN
Troubleshooting issues
How to Solve Formula Errors
How to Solve Data Formatting Issues
Solving Printing Problems in Excel
Finding Solutions to Errors in Excel's Data Validation
Changing the Customization Settings
CONCLUSION
 INTRODUCTION
Microsoft Excel is a highly versatile spreadsheet software that has become
indispensable in a wide range of industries, including business, finance,
engineering, and data analysis. Excel's formulas and functions are the
driving force behind its versatility and efficiency. They empower users to
effortlessly perform complex calculations, automate tasks, and analyze data.
Formulas in Excel are used to calculate values within your worksheet.
Formulas always begin with an equal sign (=) and can include a mix of
numbers, cell references, operators, and functions. As an illustration, the
formula =A1+B1 combines the values in cells A1 and B1. The flexibility of
formulas allows users to create calculations that are perfectly suited to their
unique requirements, whether they are simple or intricate.
Functions, however, are predefined formulas that simplify complex
calculations by encapsulating them into a single command. Excel provides a
wide range of functions, each tailored to serve a specific purpose. Typical
functions include adding numbers with SUM, calculating the mean with
AVERAGE, finding data in a table with VLOOKUP, and using conditional
logic with IF. Users can handle a wide range of tasks, from financial
forecasting to statistical analysis, by combining these functions.
Having a good grasp of Excel's formulas and functions can greatly improve
efficiency and precision. Users can effectively manage large datasets,
uncover trends, and make well-informed decisions. Whether you're just
starting or have experience with data manipulation, becoming proficient in
Excel's formulas and functions is a valuable skill that can greatly enhance
your capabilities.
 CHAPTER ONE
About Microsoft Excel
Microsoft Excel, a spreadsheet application developed by Microsoft, has
become an essential tool in both professional and personal settings since its
launch in 1985. Excel, a component of the Microsoft Office suite, is widely
recognized for its powerful data manipulation capabilities. It provides a
wide range of functionalities, from basic data entry and simple calculations
to advanced data analysis and visualization.

Key Features
Excel operates on a grid of cells arranged in numbered rows and letter-
named columns. The grid-based structure of this system enables users to
efficiently organize and manipulate data. The cells in the spreadsheet can
hold different types of information, such as text, numbers, and dates.
Furthermore, cells can contain formulas that can perform calculations using
data from other cells. This feature is essential for building data models that
are both dynamic and flexible.

Formulas and Functions

Excel's functionality is greatly enhanced by its wide range of pre-built
functions and the option to create personalized formulas. Functions such as
SUM, AVERAGE, and VLOOKUP are essential for performing arithmetic
operations, aggregating data, and searching for specific values within a
table. The use of advanced functions like INDEX and MATCH allows for
more complex data retrieval operations, while functions such as IF, AND,
and OR serve as the foundation for logical decision-making in spreadsheets.

Data Analysis and Visualization

Excel provides users with powerful tools like PivotTables and PivotCharts
for data analysis. These tools enable users to interactively summarize and
explore large datasets. PivotTables allow for the extraction of valuable
insights from data by reorganizing and summarizing it while preserving the
original dataset. Visual representations such as charts and graphs offer a
clear way to analyze data, allowing for easy identification of trends,
patterns, and outliers.

Advanced Features
Experienced users can take advantage of Excel's advanced features. Macros,
crafted in Visual Basic for Applications (VBA), enable the streamlining of
repetitive tasks, resulting in a boost in productivity. Ensuring data integrity,
data validation restricts the type of data that can be entered into a cell. In
addition, Excel offers collaboration capabilities such as real-time co-
authoring, allowing multiple users to work on the same spreadsheet at the
same time and easily track changes.

Integration and Compatibility

Excel's seamless integration with other Microsoft Office applications
greatly enhances its utility. It is possible to import data from Access
databases or link it from other Excel workbooks, allowing for smooth data
management across various platforms. In addition, Excel is compatible with
a variety of file formats such as CSV and XML, allowing seamless
integration with external applications and systems.

Practical Applications
Excel's wide range of applications makes it suitable for a variety of
different uses. It is widely utilized in business for financial modeling,
budgeting, forecasting, and performance tracking. Excel is a valuable tool
in education, allowing teachers and students to create grade books, analyze
research data, and manage projects. Excel is commonly used by individuals
in their personal lives for tasks like household budgeting, meal planning,
and organizing personal schedules.

Continuous Improvement
Excel is regularly updated by Microsoft, with new features and
improvements being made in response to user feedback and advancements
in technology. The latest updates have brought in some impressive AI-
powered features. For instance, Ideas can provide valuable insights and
suggestions by analyzing the data in your spreadsheet. Additionally,
dynamic arrays have been introduced to make complex data manipulation
tasks much simpler.

What's new in Excel?

Microsoft Excel has introduced a range of new features in 2024, which have
significantly improved its functionality across various platforms.
Here are a few important updates:
Excel for the Web

1. Chart Data Task Pane: The Chart Data Task Pane is a useful
feature that enables users to conveniently edit chart data
directly on the web. It provides controls for configuring the
appearance of hidden or empty cells and the display of data
series.

2. Images and Data Types in PivotTables: PivotTables now

supports the inclusion of images and different data types such
as Stocks and Geography. This enhancement adds more
information and visual appeal to PivotTables.

3. Sync Forms Data to Excel: Easily sync form responses with

Excel for real-time updates and simplified data analysis.
 4. Export to CSV: The Export to CSV feature enables users to
easily transfer their data to CSV format, making it simpler to
share data across various platforms.

5. Power BI Connected Tables: It allows users to effortlessly

create tables in Excel that are directly connected to Power BI
datasets. This seamless integration simplifies the process of
data analysis and integration, making it more efficient and user-
friendly.

6. Office Scripts for E1 and F3 Licenses: Office Scripts are now

accessible to a wider range of Office 365 users, allowing them
to automate repetitive tasks and increase productivity.

Excel for Windows and Mac:

1. New Default Theme: The Office suite, including Excel, now

has a refreshed default theme that brings a modern and
improved look to documents. The new font, color palette, style,
and line weights enhance document accessibility.

2. Insert Pictures in Cells: This feature enables users to easily

insert pictures into cells, treating the images as cell data that
can be sorted, filtered, and included in formulas.

3. Evaluate Performance: This tool assists users in identifying

and resolving performance issues in their workbooks by
providing suggestions for optimizing unnecessary formatting.

Excel for iPad

Using PivotTables on iPad: Perform complex data analysis on the go with
the optimized PivotTable feature for iPad.

List of Excel Terms and Terminologies

Presented below is a compilation of frequently used Excel terms and
terminologies:
Basic Terms

1. Workbook: A file in Excel that contains one or more

worksheets.

2. Worksheet: A spreadsheet that is part of a workbook.

3. Cell: A cell is formed when a row and a column intersect in a

worksheet.

4. Range: A collection of two or more cells.

5. Row: Horizontal lines of cells, labeled with numbers.

6. Column: Cells arranged in vertical lines, labeled with letters.

7. Cell Reference: A cell's unique identifier is formed by

combining the column letter and row number, such as A1 or
B2.

8. Active Cell: The active cell is the cell that is currently selected
and allows for data entry or editing.

Functions and Formulas

9. Formula: An expression used for performing calculations or

operations within a cell.

10.
Function: Excel provides predefined formulas that
allow for specific calculations, such as SUM and AVERAGE.

11.
SUM: The SUM function is used to add together a
range of cells.
 12.
AVERAGE: The AVERAGE function calculates
the mean of a range of cells.

13.
IF: IF is a logical function that can be used to
return different values based on whether a condition is true or
false.

14.
VLOOKUP: The VLOOKUP function is used to
search for a value in the first column of a range and retrieve a
value from a specified column in the same row.

15.
HLOOKUP: Similar to VLOOKUP, HLOOKUP
searches for a value in the first row and returns a value in the
same column from a specified row.

16.
INDEX: The INDEX function retrieves the value
of a cell within a specified range by using its row and column
numbers.

17.
MATCH: The MATCH function is used to search
for a specific value within a range and retrieve its relative
position.

18.
COUNT: A function that calculates the total count
of cells with numerical values.

19.
COUNTA: A function that tallies up the cells that
are not empty.
Data and Formatting
 20.
Cell Formatting: Personalizing the look of cell
content, including font, color, and borders.

21.
Conditional Formatting: Applying specific
formatting to cells that meet certain criteria.

22.
Data Validation: A feature that limits the type of
data or values that users can input into a cell.

23.
Pivot Table: An invaluable tool for summarizing,
analyzing, exploring, and presenting data.

24.
Chart: A visual representation of data from a
worksheet.

25.
Filter: Filter is a useful feature that enables you to
selectively display rows in a list based on specific criteria.

26.
Sort: Sorting involves organizing data in a
particular order, which can be either ascending or descending.
Advanced Features

27.
Macro: A series of instructions that streamline
tasks.

28.
VBA (Visual Basic for Applications): This is a
programming language that is commonly used to create macros
and automate tasks in Excel.
 29.
Solver: An add-in that is commonly used for
optimizing and solving intricate equations.

30.
What-If Analysis: Tools like Goal Seek, Scenario
Manager, and Data Tables are utilized for forecasting and
exploring various scenarios through What-If Analysis.

31.
Array Formula: An array formula is a powerful
tool that allows for performing multiple calculations on one or
more items in an array.
Navigation and Tools

32.
Ribbon: The ribbon in Excel is located at the top
of the program and consists of tabs and commands.

33.
Formula Bar: The formula bar is where you can
input or modify data, formulas, or functions in the active cell.

34.
Name Box: The Name Box displays either the cell
reference or the name of the selected cell or range.

35.
Sheet Tab: The sheet tab is located at the bottom
of the Excel window and shows the name of a worksheet. It
provides a convenient way to switch between different sheets.

36.
Freeze Panes: Freeze Panes is a useful feature that
allows you to keep selected rows or columns visible while
scrolling through a worksheet.
 37.
Split Panes: This feature conveniently divides the
window into two or four separate panes, making it easier to
compare data.

38.
Protect Sheet/Workbook: Features to prevent
unauthorized changes to the data in a worksheet or workbook.

39.
Track Changes: Track Changes is a useful feature
that highlights any modifications made to a workbook, making
it easier to review and keep track of any changes.

Data Tools

40.
Data Table: A data table is a collection of cells that
demonstrates the impact of modifying one or two variables in
your formulas on the outcomes.

41.
Slicer: A handy tool that allows you to effortlessly
filter data in PivotTables and PivotCharts through a visual
interface.

42.
Power Query: This is a powerful tool that allows
you to seamlessly connect, combine, and refine data from
different sources.
 CHAPTER TWO
Basic Excel Functionalities
The features and functions of this spreadsheet application are widely used
for personal and business purposes. The application has maintained its
popularity due to its straightforwardness, dependability, and adaptability.
Excel is widely used by individuals worldwide to effectively organize data
and make informed decisions for their businesses. It has become the most
widely utilized spreadsheet application. Here, we have gathered a
comprehensive list of the various features and functions that you can find in
Excel for your reference.
Additionally, Excel provides you with the capability to generate charts. You
can modify the way your data is presented, making it simpler to visualize.
Excel offers support for a variety of 2D and 3D charts, enabling you to
visually represent your data in different formats. This program enables you
to effortlessly create various types of charts, such as pie charts, histograms,
and gauge bars, in a matter of minutes.

How to add new rows and columns

Adding new rows or columns to your workbook in Excel is a
straightforward process. By adopting this approach, you can generate more
comprehensive analyses of your findings. In addition, this allows for greater
creativity and the ability to present multiple data points simultaneously in a
chart. In addition, Excel provides the option to hide or relocate rows and
columns as needed for your analysis. Excel provides users with the
capability to input and modify content, including text and numerical data.
You can achieve this by directly typing the information into your
worksheets or importing data from raw text files.
How to create charts
Excel offers a variety of chart options (line, graph, bar, image) to enhance
the visual appeal of your work. For a more polished and professional look,
you have the flexibility to customize the visual presentation of your charts.
You can customize the color scheme, column widths, and borders. You can
also enhance the visual appeal by incorporating images into your charts and
adjusting chart elements like titles or axes.

How to use calculators

 This function
is widely used and highly regarded in the world of spreadsheets. This
feature enables basic arithmetic operations such as addition, subtraction,
multiplication, and division. By using this method, you can seamlessly
handle numerical tasks without the need to launch a separate program.
How to use conditional formatting

You can use this feature to generate charts and graphs that effectively
showcase various data elements, taking into account both current and
historical conditions. This is a valuable tool for identifying trends and
patterns in data or pinpointing deviations from important criteria. Excel
provides a wide range of additional functions, including percentile charts,
pivot tables, charts with multiple series, and more.

How to use text-to-columns

This feature enables you to divide text into columns and merge it with other
data, such as numbers or dates. With this feature, you can effortlessly
arrange numbers and dates into rows rather than columns.

How to use color schemes

This tool allows for the automatic coloring of cells based on their values.
By using this method, you can effortlessly visualize a distribution or trend
within your workbook.

How to use auto filter

This function is designed to highlight rows of data that meet specific
criteria. This tool enables you to effortlessly filter content by substituting
specific portions of your data with alternative data sources. The AutoFilter
feature is highly effective for efficiently filtering data, allowing you to
focus on your top priorities at any given moment. This method allows for
efficient sorting of large volumes of data, displaying only the most relevant
information. This feature enables you to easily sort data by column or row
and provides the flexibility to filter data in various directions.

How to use the cursor

The cursor indicates the position for entering new text. With this tool, you
can effortlessly navigate to a different cell and explore a different section of
your data. By utilizing this feature, you can effortlessly navigate through
various sections of your workbook, eliminating the need for multiple tools.

How to use dragging

When you want to move a column in a sheet, simply grab the label and the
entire column will come along with it. With this feature, you can easily
rearrange different sections of your worksheet. You can easily transfer
content between cells, folders, or even other files.
Using multi-threading
Excel can use the computer's power effectively by running multiple
calculations simultaneously, resulting in efficient performance. The speed at
which you can compute your spreadsheet is greatly affected. You can easily
multitask on your computer. The program interface remains open and
accessible, allowing for spellchecking while typing in a cell, among other
additional features.

Cell numerical operation

This tool enables users to carry out mathematical operations on the values
stored in cells. This tool can be used to perform various mathematical
operations on numbers within a cell.

How to organize
With this feature, you can easily organize and arrange content in multiple
ways. You have the option to sort data in either ascending or descending
order, for example.

How to use Excel Add-in

Enables the installation of add-ins that enhance the functionality of the base
application. You can incorporate various tools for data visualization,
internet information retrieval, and real-time content generation.

How to use Cell Comments

You have the option to provide a comment for a specific cell. These
comments are visible when you hover over the cell and disappear when you
move away from it. Text is valuable in data tables, as it serves as
informative notes for other users.

Using Drawing Freehand Shapes

You can use this tool to easily generate custom patterns in the chart area.
Excel offers a wide range of unique patterns that can be used to create
visually appealing designs.
Generating dates and date ranges
You can use this tool to generate dates for your charts. The date features in
Excel are straightforward and user-friendly. As an illustration, one can
generate a graph with numerous series and assign a corresponding date to
each series, enabling the observation of trends and patterns across various
periods.

Collaborative Features
With Excel, you can easily collaborate with others by sharing workbooks
and allowing them to make edits, save their changes, and send them back to
you. Additionally, you have the option to create files with password
protection, which effectively restricts other users from modifying the
content.

How to Customize Ribbons

The ribbon is a toolbar that is displayed at the top of a workbook.
Customizing the ribbons is a breeze as users have the flexibility to add or
remove tabs, as well as rearrange them to their liking. There is a wide range
of commands available for you to utilize in your workbook, allowing you to
format cell contents and insert graphs, among other functions.

Data import and export

This feature enables you to easily import data from various sources, such as
XML files, and export it into different formats, like HTML, which are
better suited for publishing purposes. It is possible to import additional
Excel spreadsheets into your current one, allowing you to efficiently
perform calculations on a vast amount of data. Additionally, you have the
option to export data to another workbook, enabling you to seamlessly
switch between projects without the risk of losing any progress on your
current one.

How to convert text to cells

This feature enables you to duplicate a cell containing text and transform it
into a cell that utilizes numbers, dates, or formulas. If you're looking to
streamline the task of substituting text with different forms of data, this
feature is quite handy.
Excel is an incredibly powerful tool for analyzing large volumes of data.
With a well-crafted workbook, you have the power to analyze data from
various sources and present it in a visually appealing manner, making it
easily accessible and comprehensible. By simply clicking a few buttons,
you can effortlessly create visually appealing charts and graphs that
effectively display the trends in your data. This tool allows you to
efficiently organize and manipulate your data, making it easier to present in
a clear and comprehensible manner. Having a solid understanding of Excel
can be beneficial when it comes to visualizing your data. However, the
proper approach to working with Excel varies from person to person.
Having a good understanding of the pros and cons can be beneficial when
navigating the various decisions associated with this software.

Typically, if you discover that Excel offers additional possibilities for

presenting your work or helps you navigate complex problems.
 CHAPTER THREE

Inserting and Formatting

Inserting
Excel introduces a convenient feature that allows users to easily insert
tables into their spreadsheets. Tables are a useful tool for organizing data in
a structured manner, with rows and columns.
To insert a table, position your cursor in cell A1 and select Insert > Table...
from the menu bar. You can specify the size of the table, the number of
rows, columns, and more by opening up a dialog box.
One of the most common errors in Excel occurs when a table is mistakenly
inserted into the wrong cell of a spreadsheet. If you want to insert a table
into a different cell, you can either choose an empty cell or enable the
editing options. For instance, if you want to insert a table that spans
multiple rows, it is advisable to place the table in a column of the same row.
Follow the steps below to steer clear of these errors and create a table that
exudes professionalism:

1. Begin by choosing the specific section that you would like to

transform into a table.

2. Next, you can easily create a table by navigating to the Insert

menu and selecting Table.

3. A dialog box will appear, allowing you to specify the desired

number of rows and columns.

4. Click OK and your table will be displayed.

Using Pivot Tables

Pivot tables are commonly referred to as a fancy term in Excel, but in
essence, they serve as a tool for summarizing data. Pivot tables are designed
to efficiently summarize data within an Excel workbook, allowing you to
analyze specific subsets of your data without the need for external tools or
extensive modifications to your worksheet.
A pivot table is capable of transforming raw data into meaningful
summaries. It achieves this by providing summary totals for each field and
further breaking down these totals into counts of distinct values for each
field.
The Pivot Table is a powerful tool for visualizing data, but it can be
challenging to extract meaningful insights from it without patience and
skill. When you search for Excel Pivot tables in your workbook, you'll find
a collection of them that have been created using the raw data in your
workbook.
Below are the basic steps:

1. First, you'll want to create a pivot table

To get started, you'll want to create a Pivot table to easily view the data in
your workbook. Creating a pivot table is easily done through the ribbon
menu. Click on the "DATA" tab and select "PivotTable."
 2. Next, proceed to generate a Pivot table using a selected range
of cells.
For the next step, you'll need to choose the specific data range you wish to
summarize in your pivot table. This can go in one of two directions or get in
shape by simply moving the cursor.

3. Choose and specify the column you want to use for sorting.
To view the desired information, simply choose the column and click on the
corresponding arrow. By selecting the column, you can consolidate all the
data into a single line. Choosing the appropriate sort order for this step is
crucial for the Pivot table to accurately process the data.

4. Create a summary field that allows for quick visualization of

your data.
Continue with the same process to choose the remaining columns, and
you'll end up with a Pivot table that provides a comprehensive view of your
data. Additionally, there are summaries available from various fields that
can provide valuable insights into your data.

Using Illustration
Adding an illustration in Excel involves the simple task of inserting a
picture into your spreadsheet. It can be quite beneficial when you need to
present data or enhance the readability of text.
Illustrations can be easily added to an Excel document by using the insert
tab. You have multiple options for adding illustrations to your spreadsheet,
such as Wordart, text boxes, shapes, and pictures. Another option is to
utilize the shapes tool in Excel to design your illustration.

Using Shapes
By selecting the shapes icon in the Insert tab, a comprehensive list of all the
shapes at your disposal will be displayed. Select any shape and place it into
your spreadsheet.
After adding a shape, you have the freedom to select it and effortlessly
adjust its position using the control handles. To adjust the size of the shape,
simply click on the resize handle situated at the top right corner of the
shape. Adjusting the size of shapes allows you to modify the amount of
white space surrounding them in your spreadsheet.
To modify the color of a shape, simply select it using your mouse and click
on the box icon found at the right end of the control handle. You will see the
customization pane. Click on the "Select More fill options" located in the
top left corner and then select the "Fill options" button. A new pane for
customization will be displayed. To change the default color, simply click
on the color fill button and select your preferred color.

Using a textbox
If you would like to incorporate written content into your illustration, you
have the option to use a textbox. The textbox option can be found
conveniently beside the WordArt option in the insert tab. This feature
allows you to easily insert text, images, or a combination of both into a
designated box and adjust the box size accordingly. The box can be
positioned anywhere on your spreadsheet.
Once you've added a textbox, simply select it using your mouse pointer. At
that point, control handles will appear on the box. These handles allow you
to easily modify the size, position, and color of the box.

Using WordArt
Wordart is a pre-designed image available in Word that can be used to
enhance your data presentation. The Insert tab offers a convenient option
called Wordart that allows you to effortlessly add a Wordart picture to your
spreadsheet. After selecting that option, a variety of wordarts will be
displayed for you to choose from. Simply select the WordArt and it will be
inserted into your spreadsheet.

Using Images
Inserting pictures from your computer or other devices into Excel is a
breeze using the insert tab. To add pictures to your spreadsheet, go to the
insert tab and click on the Pictures option. From there, you can select the
images you want to display from your computer or other devices. After
you've chosen your desired pictures, they will be automatically inserted and
displayed within your spreadsheet.
In addition, you have the option to include images from a specific website
or attach them. To insert a picture into a specific cell, you will need to first
select the desired cell using your mouse. Next, navigate to the insert tab and
choose the option to insert pictures. It offers a variety of placeholders for
your use. Choose the placeholder you want and download the image link
from your browser.
This can be incredibly useful for creating charts or even illustrations. This
tool is user-friendly and particularly beneficial for children who are
exploring shapes and learning to manipulate them independently. Using
Excel's illustration features can greatly enhance the visual appeal of
spreadsheets, making them more engaging for designers.

Using Charts
When it comes to creating graphs for your report, using Excel is highly
recommended. Graphs are visual tools that help illustrate statistical data
clearly and concisely. Excel offers a variety of default chart types that can
be easily customized to suit your preferences. Customizing charts is
possible by adding colors, titles, and labels. There is a wide variety of
charts available for use in your spreadsheet, each suited for the different
types of data you have collected. You have the option to create a chart on
your own or find one from an online source.
Presented below are additional chart types for your reference:

Column Chart: This is commonly used to showcase the

proportional shift in a set of data, such as sales or costs.

Line Chart: A line chart is a useful tool for visualizing

changes in data over time.

Pie Chart: A pie chart is commonly used to visually represent

the proportions or percentages of a whole.

Bar Charts: Bar charts are commonly used to visually

represent a set of values for different categories.

Area Chart: An area chart is a useful tool for visualizing

changes in data over time, whether it shows an upward or
downward trend.

Scatter Chart: A scatter chart is commonly used to illustrate

the correlation between multiple variables.

Stock Chart: A stock chart is commonly utilized by novice

traders to demonstrate the potential of a specific stock in the
stock market.

Radar Chart: A useful tool for visualizing data changes along

a single dimension.

Combo Chart: The Combo Chart, also known as a time series

chart, is a versatile visualization tool. This chart is a fusion of
 two distinct chart styles. This tool is effective in illustrating the
progression of data over a specific duration.
It's impressive how they give you complete control over the appearance of
your data, down to the finest details. Excel offers a wide variety of charts to
choose from. All charts can be fully customized, allowing you to
personalize the background, title, data, and axes, among other elements.

How to use Links and Comments

Adding links and comments to excel workbooks is a popular method for
enhancing the workbook and making it more interactive. It can serve
various purposes, such as facilitating collaboration, offering insights on an
entity, or allowing users to leave comments on the data in their workbook.
However, when constructing your workbooks, it is important to be mindful
of the potential for links to become disorganized and difficult to manage.
Here is a technique to enhance the legibility of hyperlinks within an Excel
workbook. Let's begin by discussing the importance of displaying external
links and the reasons why internal links are occasionally required.
Links in Excel workbooks serve as references to information or values
outside of the workbook. It's worth noting that an internal link can also be
present in a workbook, which goes against the assumption that it's always
the case. These are commonly utilized as references between worksheets,
serving as helpful tools for navigation. As an illustration, you can create a
worksheet containing your product names and another worksheet
containing the corresponding information. To enhance user experience,
consider using an internal link between the two sheets to provide a helpful
navigation aid.
Unfortunately, clicking on this link will only redirect users back to the same
sheet, regardless of their current location on the dashboard. Although it may
appear to be a challenge, it can be quite useful when dealing with a
straightforward collection of worksheets. One option is to create a separate
worksheet for product descriptions, allowing users to easily navigate and
find the information they need. One way to navigate back to the product
description sheet is by using an internal link within the first sheet.
Excel workbooks often include links and comments to enhance the user
experience and make the workbook more interactive. Hyperlinks can offer
additional insights on a subject or transport the reader to a different
resource. Comments provide a convenient way for authors to communicate
with others, such as editors or coworkers, without disrupting their
workflow. Here are a couple of methods to assist users in engaging with
workbooks.
Keep your comments within 80 characters and refrain from using any
formatting. Comments can be easily inserted using the "insert comment"
command. Comments will be visible to anyone who opens the workbook
unless they have disabled comments in the view tab of the Excel options
dialog box. Comments can be easily viewed in the comments window when
editing the workbook.
Adding comments to an Excel workbook is a straightforward process.
Here's how you can do it:

1. First, choose the cell where you would like to insert the
comment.

2. To add comments, navigate to the insert tab on the ribbon and

click on the appropriate option.

3. Complete the comment.

4. Implement any desired formatting or include any relevant

supplementary details.
Links can be quite useful for readers who are seeking additional
information on a particular topic or who want to navigate between different
pages. The links are dynamic and will automatically adjust if any changes
are made to the worksheet, such as moving, renaming, or deleting it.
Adding links to cells with merged cells or table elements is not possible.
Links can be found in the insert tab under the links section.
Links can be included either independently or as part of a hyperlink. A
hyperlink functions as a clickable connection to an external file, such as
another Excel workbook or an online webpage.
Learn how to easily incorporate links and hyperlinks into your Excel
workbook.
 1. Choose the cell where you wish to insert the link/hyperlink.

2. To insert links or hyperlinks, navigate to the insert tab on the

ribbon and click on the appropriate option.

3. Choose the desired link or hyperlink from the submenu.

4. Complete the link parameters.

5. Click the OK button.

Comments are similar to links, but they are displayed in the comments
window instead. Hotkeys can be assigned to links, but unfortunately,
comments do not have this feature. You can easily add links and comments
by using keyboard shortcuts in the insert comment or insert link command.
Links have a character limit of 255 and can include formatting. Hyperlinks
can easily be added using the insert hyperlink command. Just like
comments, links in the workbook are visible to anyone who opens it, unless
they have disabled links in the view tab of the Excel options dialog box.

Adding a hyperlink to an Excel workbook

1. To access the desired option, navigate to the "Insert" tab

located at the top.

2. Click on "Hyperlink".

3. Click on the "Text link" option.

4. Select one of the options below:

Copy and paste the text from your spreadsheet into an email or
document. Feel free to add a hyperlink to any website of your
choice on your spreadsheet.
Indeed, feel free to type anything you desire, including emojis!
Click "OK" to proceed.
Users can freely express their thoughts on the subject, allowing other
readers to gain a deeper understanding of the content. In addition, if you
want to promote your company name or link discreetly within your
workbook, this function is perfect for you.

Formatting
If you're tired of the monotonous Excel grid and labels, it's time to unleash
your creativity. We will demonstrate how to use different formatting tools to
transform your spreadsheet into a visually appealing masterpiece.

How to use Characters, Colors, Size

Formatting Excel can be challenging within the Excel software. However,
you can simplify the process by utilizing pre-established patterns and styles.
By utilizing this approach, you can selectively implement the methods that
resonate with you into your spreadsheet. You can easily duplicate characters
and designs in other cells without having to manually apply the styles
repeatedly. Every type consists of a collection of preconfigured formatting
options.
Styles are designed to be a time-saving feature that allows you to easily
apply consistent formatting across your entire spreadsheet. There are two
reasons why it is beneficial to store these styles in the same document as the
text formatting. Having experience with various documents that have
similar formatting requirements allows one to easily locate and apply the
appropriate styles.
Additionally, if you happen to forget the name of a particular style, you can
rely on the convenience of the "Styles" option. This feature allows you to
apply multiple formats to a cell or block of cells, building upon the existing
text formatting. Styles are incredibly useful for ensuring a consistent design
across all documents, including Excel spreadsheets. Excel offers a wide
range of predefined styles that can be easily applied to format any block of
text with speed and efficiency. Applying consistent formatting throughout
an entire workbook is made incredibly simple with this method.
Formatting a spreadsheet as a whole can be easily accomplished by using
different fonts and sizes. You can also specify how each style is applied to
text in the cell. If you desire to have the initial word in each cell bolded,
while the rest of the words should be italicized, here's what you can do.
Typically, you'd need to apply both of these styles separately to each word.
With styles, selecting a predefined option that combines these two settings
is all that's required.
Whether it's a single row or multiple rows, any modifications made will be
applied to all of them at once. To access the desired options, navigate to the
'format' tab and select 'cells.'
Next, navigate to the 'home' tab where you'll find various sections to
explore. To modify the font size, you have two options. You can either
select a different font size from the drop-down menu next to the font or
manually enter a new size in the designated box.
If you want to modify the color of your font, you have a couple of options.
You can click on the drop-down arrow next to the font and choose a
different color from the provided list. Alternatively, you can directly click
on the font color box and manually enter a new color.
There is a wide range of formatting options available to suit your needs.
Experiment with different formatting styles to enhance the visual appeal of
your spreadsheets and make them stand out.

How to use Alignment, Merge, and Wrap

Manipulating cells in Excel allows you to achieve your desired visual
presentation. Merging and wrapping alignments share similarities, yet they
also have distinct characteristics. It's important to be aware of the first
action, which involves changes in alignment.
Adjusting the spacing between columns or cells within a column can greatly
enhance the readability of your spreadsheet. You have the option to align
your text to the left, right, or center. In the next section, we will cover how
you can align cells vertically. By default, the alignment is set to left, but you
have the flexibility to change it to either right or center. If you prefer to
align one cell at a time, you have the option to merge cells and wrap text
and other objects. These two actions are categorized together as part of the
alignment group, ensuring there is no confusion between them. By default,
all three options (Alignment, Merge, and Wrap) are disabled. After enabling
the feature, you can align, wrap, or merge any cell by using the buttons
located on the bottom row of cells in the row and column headers.
To use the alignment command, simply click on a cell and then expand the
alignment button located at the bottom of your spreadsheet. There are five
available options: left, right, center, justify, and vertical alignment. The
justify option will generate two columns instead of a single column. The
cells are now of equal height, but they are stacked on top of one another. To
center your text vertically in a cell, simply click on the vertical alignment
icon and choose the 'center' option. This feature comes in handy when you
need your columns to align perfectly. In certain situations, this may not be
possible. If you require that functionality, it is recommended to use the
wrap option instead.
It is important to ensure that the text or objects are properly aligned and not
overlapping each other. Wrap smoothing automatically adjusts column
widths to prevent gaps along the side when there are too many rows
between them. You have the option to modify the default spacing of 10
characters by adjusting the wrap width. When text or objects are positioned
too closely together, it can result in an unattractive appearance. To ensure
proper wrapping around a picture, utilize the wrapping feature. This can be
useful if your text is spread across multiple columns. Having numerous
wrap lines in your spreadsheet can accumulate at the bottom of your screen,
occupying precious space. Adjusting the wrap margin can help you make
changes to this. You have the option to set the number of wrap lines that
will be displayed, with the default being 0 characters. To conceal those
additional lines, you can use the hide wrap feature. Similarly, merged cells
follow the same principle.
Although merged cells and wrapped cells have some similarities, they also
have some differences. When cells are merged, they overlap and leave a
slight gap between them that cannot be removed, which may be
bothersome. Occasionally, there may be a need to combine cells. With
Wrap, you can adjust the alignment of text in its cell, whether it's horizontal
or vertical. The rotate tool enables users to easily rotate numbers or text
within their cells. The gap becomes apparent when viewing the spreadsheet
in print mode. Unfortunately, it is not possible to modify the margin of
merged cells. However, you can reduce the size of the cells by using the
"move cells to justify" option.
Let's discuss the wrapping and aligning options individually since they may
appear similar, but they have distinct differences. Justify is quite
straightforward as it ensures that your cells have uniform height.
There are numerous techniques to enhance the readability of your
spreadsheets. We have previously covered the alignment, merge, and wrap
features that Excel offers. There are various techniques to make your
spreadsheet stand out and distinguish it from others. Alignment, merge, and
wrap are all useful features that can enhance your spreadsheet's
functionality. These straightforward choices not only enhance the
professional appearance of your spreadsheet but also contribute to its
overall improvement.

Exploring cell data types and addressing common challenges

Formatting your cells in spreadsheets is evolving with each passing year.
Several common issues can occur when working with dates in Excel
spreadsheets, such as the problem of numbers being displayed as dates.
Formatting cells in Excel can be affected by frequent updates and rule
changes, which can alter the way you should format them periodically. Now
let's delve into some common challenges you might come across and
discover effective strategies to overcome them. Now is the perfect
opportunity to familiarize yourself with the latest formatting guidelines in
Excel.
It is important to understand that cell formatting in Excel can be
accomplished in two ways: through the use of Formatting Cells or by
formatting your data. We highly recommend formatting your data as it is a
crucial technique. Excel offers greater flexibility and allows for more
precise control over the display of your data. Nevertheless, it is important to
format cells in certain situations to understand how Excel will handle your
data during formatting.
Each data type in Excel has its unique format:

Dates: Short dates are written in the format dd/mm/yy, for

example, 01/04/2024. Medium dates are written as d mmmm,
 yyyy, such as Jan 4, 2024. Long dates are written as dddd,
mmmmm yy, for instance, Wednesday, January 04, 2024.

Time: hh: mm :ss (e.g. 1 :49 :23 PM )

Numeric: 0,,-,,+,-,.# (e.g. 4,478.4)

Currency: $#,##0.00 (e.g. $1,000.5)

Percentage: 0%,#0% (e.g. 0%,20%)

Fraction: 1/2 = 0.5, 1/4 = 0.25.

Formatting your data is a straightforward process. To include a date in your
spreadsheet, you can use either the short or medium-to-long format. The
format used will vary based on how it was entered. If it was entered as text,
such as "hh:mm" for time or "mm/dd/yy" for date, then the short format will
be used. If it was entered as numbers, then either the medium or long
format will be used.
Excel analyzes the cell's contents and attempts to determine the most
suitable format for better readability. It is crucial to understand that Excel
will attempt to present data according to its expectations.

Conditional formatting and table format

Conditional formatting options are available in most spreadsheet
applications. You can customize the display of your data based on specific
conditions. These visual techniques can greatly simplify your work, even if
you're not familiar with any formulas. However, what if you are interested
in creating an infographic? Table formatting is incredibly useful as it
enables the input of various data types within a single column. When
inputting values, each cell is formatted based on the data type in the
column.
This section will explain how to effectively combine these two functions to
produce visually appealing and easily readable spreadsheets, regardless of
screen size.
 1. Applying conditional formatting to individual columns
There are specific functions available for applying conditional formatting to
individual columns:
Table format function: Create unique and customized table formats for
your data using the table format function. The primary objective of this
function is to identify and highlight cells that meet the specified criteria.
List format functions: Formatting functions in list format allow for the
creation of custom table formats using the same function as the table format
one.
List boxes: They are commonly utilized to present large amounts of data in
a visually organized manner. They serve as a useful tool for displaying
various values. List boxes are presented in a two-dimensional format. If you
utilize them, the columns in your data list cannot be altered or rearranged,
and you are unable to directly edit individual cells within the list box. When
no formatting rules are specified, Excel will automatically apply the default
formatting rules to the selected data set.

2. Applying conditional formatting to multiple columns

Three conditional formatting functions are available for multiple columns,
enabling you to conveniently group related cells together. One of the
functions allows for the highlighting of rows based on specific conditions.

3. Formatting tables with conditional formatting

Table format conditional formatting allows you to create personalized and
distinctive table formats for your data. The main objective of this function
is to highlight cells that meet the specified criteria.
To format the table, you can either click the table format icon in the toolbar
or use the shortcut Ctrl+Alt+L.
Once you've done that, simply select one of the available options:
Table Column: Efficiently inputting values across multiple rows
simultaneously.
Table Drop-Down List: Allows for efficient entry of values in multiple
rows simultaneously.
Table Widget Boxes: Create conditional formatting rules for table widget
boxes that apply different formatting colors based on the combination of
data type and cell value.

Specify the elements you want to highlight in the conditional

formatting dialog box, along with additional options like format
color, font size, style, and background color.

When all the conditions are met, Excel will automatically

highlight the corresponding cells. To highlight additional cells
that meet some or all of your conditions, simply select the 3-
conditions option in the dialog box and click on the Plus icon.

Go ahead and click OK to see the updated appearance of your

data.

4. Formatting lists using conditional formatting

The list format for conditional formatting allows users to create
personalized table formats using the same function as the table format.
Create impressive data visualizations for your Powerpoint presentations,
slide shows, or PPTX files.

To access the list style feature, you can either click on the
corresponding icon in the toolbar or use the keyboard shortcut
Ctrl+Alt+L.

Enter information in your data lists by specifying the desired

appearance using this dialog box. By ensuring consistency in
the format used for each item in the list, you will be required to
choose only one item for each type of format.
 After inputting your formatting rules, ensure to enable the
"Show" option located at the top of the dialog box. This will
prompt Excel to highlight any cell that satisfies all of the
specified formatting conditions.

Go ahead and click OK to see how your data appears now.

Specify the elements you want to highlight in the conditional

formatting dialog box, along with additional options like format
color, font size, style, and background color.

5. Conditional data using list boxes

The use of conditional formatting with list boxes allows for the creation of
data-rich displays, where the values within each section of the list box are
effectively showcased. Allow me to provide you with an illustration:

To access the Listbox feature, you can either click on the

Listbox icon located in the toolbar or use the keyboard shortcut
Ctrl+Alt+L.

Select one of the following options. Clicking on the first option

will automatically apply the pre-set conditional formatting rules
to display your data accordingly:

Specify the elements you want to highlight in the conditional

formatting dialog box, along with additional options like format
color, font size, style, and background color.
Conditional formatting can enhance the visual appeal of your Excel data
sets, giving them a more vibrant and engaging look. You can easily
emphasize certain cells to effectively present data clearly and
understandably. By combining these three functions, you can effortlessly
produce captivating visual art while efficiently extracting the desired
information.

How to add and remove columns/cells

Starting with a spreadsheet is common, but occasionally, additional
columns or cells are necessary to accommodate fresh data. Here's a simple
guide to help you begin!
how to add a column?
To add a new column in Excel, navigate to the "Insert" tab on the Ribbon
and select "insert column." Alternatively, you can right-click on the
desired location and choose "insert." In both cases, you have the option to
select "right" for vertical spacing or "left" for horizontal spacing.
how to delete a column?
To remove a column, you have a few options. You can click on the "Insert"
or "Manage" tab from the Ribbon, find the column you want to remove,
and choose the "delete column" option. Alternatively, you can right-click
on the column you wish to remove and select "remove," or right-click on an
empty cell and choose "delete."
how to add a cell?
Find the row you wish to add to, then click and drag down from the bottom
right corner of an empty cell in that row.
How to remove a cell?
To remove a cell, simply right-click on an empty cell and select "delete."
Alternatively, you can use the "Remove" tab from the Ribbon.
Having the ability to create a new worksheet in Excel can be incredibly
useful, particularly when you have numerous projects to manage and need
to keep everything organized. Additionally, the flexibility to add or remove
rows and columns based on your requirements is a valuable feature. In
today's information-driven world, the demand for Excel proficiency is
higher than ever before. However, becoming an Excel expert is no simple
task and requires a range of skills that may not come naturally to everyone.
 CHAPTER FOUR
Getting to Know the Excel Ribbon Tabs
What are Excel Ribbons?
The Ribbon serves as the central control panel for Excel. Excel provides a
convenient ribbon that offers quick access to various commands. Acting, a
command is executed by the user. Creating a new document/workbook,
saving a document, printing a document, and more are all examples of
commands.

Excel Tab Components

Ribbon Start Button
This feature allows users to access various commands, including creating
new documents, saving current work, printing, and customizing Excel
options.

Ribbon Tabs
These tabs help organize and categorize similar commands. The home tab is
essential for performing basic commands like formatting data for improved
presentation, sorting information, and locating specific data within the
spreadsheet.

Ribbon Bar
Ribbon bars are used for similar group commands. The Alignment ribbon
bar is used to group all the commands that are employed in aligning data
together.

Understanding the Tabs in Excel

Using the Home Tab
The home tab includes the frequently used commands in Excel. The options
available under the home tab include clipboard, font, alignment, styles,
editing, and more. Here is a brief explanation of the tabs.

Clipboard
To display the Clipboard Task Pane, simply click on the dialog launcher
located in the bottom right corner of the Home tab.

Paste: Clicking on this button will transfer the current contents

from the clipboard. The drop-down menu includes various
commands such as Paste, Formulas, Paste Values, Use Text
Import Wizard, No Borders, Paste Link, Transpose Refreshable
Web Query, Paste as Hyperlink, and Paste Special. The As
Picture extension includes features such as Copy as Picture,
Paste Picture Link, and Paste as Picture. The enabled
commands will depend on the type of object that has been
copied.

Cut- (Ctrl + X). Copy the current selection to the clipboard.

Copy - (Ctrl + C). Simply copy the current selection to the

clipboard for future pasting. In addition to other options, the
drop-down menu also includes the command to Copy as
Picture.

Format Painter: The Format Painter function allows you to

easily copy the format of a cell or range and apply it to another
 location.

Font
To access the "Format Cells" dialog box and Font tab, simply click on the
dialog box launcher located in the bottom right corner of the Home tab.
Under this tab, you'll find various options to customize the appearance of
your text. These include choosing the font type, adjusting the font size,
increasing or decreasing the font, and applying bold, italic, or underline
formatting.

Font Type: Choose the font type from the drop-down menu. It
has a list of available font styles for you to choose from.

Font Size: Choose the font size from the drop-down menu.
You can modify the font size of your text.

Enlarge the Font Size: This will make the font size of the
selected text larger, moving it to the next size option in the Font
Size box.

Reduce Font Size: This action will decrease the font size of
the selected text to the next smaller size available in the Font
Size box.
 Bold: Make text bold by using the shortcut Ctrl + B. You can
use it to emphasize the current selection.

Italic: Use the keyboard shortcut (Ctrl + I) to apply italics.

Applies the italics formatting to the selected text.

Underline: Use the keyboard shortcut (Ctrl + U) to underline

text. Button with a Dropdown. The button allows you to switch
the current selection to bold. The drop-down menu includes the
options: Underline and Double Underline.

Borders: The button adds a border to the current selection. The

drop-down menu includes the available commands: Use the
following options to customize the appearance of your borders:
bottom, top, right, left, no, outside, all, thick box, bottom
double, thick bottom, top and bottom, top and double bottom,
top and thick bottom, draw border grid, draw border, line color,
erase border, line style, and more borders.

Fill Color: The button applies a color to the context of the

current selection. The drop-down menu includes the following
options: Standard Colors, Theme Colors, No Fill, and More
Colors.

Font Color: The button allows you to modify the color of the
font currently selected. The drop-down menu includes the
following options: Automatic, Standard Colors, Theme Colors,
and More Colors.

Alignment
To access the Alignment tab in the "Format Cells" dialog box, simply click
on the dialog box launcher located in the bottom-right corner of the home
tab.
Tip Align: Align text to the top of the cell.

Middle Align: Align text to the center of the cell.

Bottom Align: Align text to the bottom of the cell.

Orientation - Drop-Down menu: Adjusts the current selection

to either a vertical or diagonal angle. The drop-down menu
includes the following options: Angle Counterclockwise, Angle
Clockwise, Vertical Text, Rotate Text Down, Rotate Text Up,
and Format Cell Alignment.

Text Wrapping: Ensures that the text is displayed on multiple

lines, allowing you to easily view the entire content.

Align Left: Data can be aligned to the left side of the cell.

Center: Data is aligned to the middle of the cell.

Align Right: Data can be aligned to the right side of the cell.

Decrease Indent: To decrease the indent, use the keyboard

shortcut (Ctrl + Alt + Shift + Tab). Reduce the indent by one
level or eliminate it.

Increase Indent: Adjust the indentation by using the keyboard

shortcut (Ctrl + Alt + Tab). Indentation is increased by one
level.
 Merge and Center - Dropdown. The button combines the
chosen cells and aligns the content in the new cell. The drop-
down menu includes the following options: Merge & Center,
Merge Cells, Merge Across, and Unmerge Cells. The Merge
Across function combines the cells within a single row.

Number
To access the "Format Cells" dialog box, navigate to the home tab and
click on the dialog box launcher located in the bottom right corner.

Number Format: This provides a comprehensive list of all the

available number formats. Here are the available number
formats: General, Currency, Number, Accounting, Long Date,
Short Date, Time, Fraction, Percentage, Scientific, and Text.

Accounting Number Format: The button will apply your

preferred accounting number format to the current selection.
The drop-down menu includes the following options: English
(US), English (UK), Euro, and More Accounting Formats.

Percent Style: Use the Percent Style shortcut (Ctrl + Shift +

%) to format numbers as percentages. Apply the percent
number format to your data.
 Comma Style: Apply the comma style number format to your
current selection.

Increase Decimal: Expands the current selection by one

decimal place.

Reduce Decimal - Subtract one decimal place from the current

selection.

Styles

Conditional Formatting - Drop-Down: One can use the

conditional formatting commands to automatically apply cell
formatting. The commands included are Highlight Cells Rules,
Top/Bottom Rules, Color Scales, Data Bars, Icon Sets, Clear
Rules, New Rules, and Manage Rules.

Format as Table: Applying a Table Style to your current

selection will define it as a table. The drop-down menu consists
of the following commands: Light, Dark, Medium, New Table
Style, and New PivotTable Style. The New Table Style displays
the dialog box for selecting a new table style. The New
PivotTable Style displays the dialog box for the "New
PivotTable Style."
 Cell Styles – Dropdown: Applying a specified cell style to
your current selection is a straightforward process. The drop-
down menu consists of the following options: Custom, Good,
Bad, and Neutral, Data and Model, Titles and Headings,
Number Format, Themed Cell Styles, New Cell Style, and
Merge Styles.

Cells

Insert a Button with a Drop-Down Option. The button is

used to insert cells into the current selection, causing the cells
below to shift downward. The drop-down menu includes
options for inserting cells, sheet columns, sheet rows, and
sheets.

Delete - Button with Dropdown. The delete button consistently

removes the current selection, causing the cells to shift
upwards. The dropdown menu includes options such as
deleting cells, deleting sheet columns, deleting sheet rows, and
deleting the entire sheet.

Format: Button with Drop-Down. The format option offers a

variety of helpful commands that can be applied to columns,
rows, and sheets. The drop-down menu includes a variety of
commands such as adjusting column and row widths, hiding
and unhiding sheets, renaming and moving sheets, protecting
 sheets, changing tab colors, and formatting cells. The Hide &
Unhide extension includes various commands for hiding and
unhiding rows, columns, and sheets.

Editing

AutoSum - Button with Drop-Down. You can find the same

command on the Formulas Tab, which includes functions such
as Sum, Average, Count Numbers, Max, Min, and more.

Fill: Choose from the options in the drop-down menu. You

have the flexibility to fill the selection in any direction and into
a range of adjacent cells. The drop-down menu includes
various commands such as Down, Up, Right, Left, Across
Worksheets, Justify, Series, and Flash Fill.

Clear - Dropdown menu. This function allows you to clear the

current selection or delete specific items. The drop-down menu
includes various commands for clearing different elements such
as all, contents, formats, hyperlinks, comments, and removing
hyperlinks.

Sort & Filter: Sort and filter using the drop-down menu. Sort
the currently selected data or current region in either ascending
or descending order and apply filters to reduce the number of
visible rows.
 Find & Select - Dropdown menu. This feature allows you to
locate and substitute specific input or selected data. The drop-
down menu includes various commands such as Find, Replace,
GoTo, GoTo Special, Comments, Formulas, Constants,
Conditional Formatting, Select Objects, Data Validation, and
Selection Pane.

Using the Insert Tab

The insert tab allows you to add different inputs to your Excel worksheet.
Here are the available insert options:
Tables

PivotTable: The PivotTable feature presents the "Create

PivotTable" dialog box. This tool is used for organizing and
summarizing intricate data into a PivotTable. The drop-down
menu included the options: PivotTable and PivotChart. The
PivotTable displays the "Create PivotTable" dialog box, while
the PivotChart showcases the "Create PivotTable and
PivotChart" dialog box.

Recommended Pivotable: The "Recommended PivotTables"

dialog box displays a selection of suggested PivotTables. The
tool showcases a range of personalized pivot tables that are
highly suitable for your specific data.

Table - (Ctrl + T). Opens the dialog box for creating a table.
Organizing your data in a table format enhances its usability by
allowing for effortless filtering, sorting, and formatting.
Illustrations

Pictures: The "Insert Picture" dialog box allows you to

browse your file system and select a file.

Online Pictures: Explore and add images from a variety of

online sources.

Shapes - Drop-Down. The drop-down menu includes a variety

of commands, such as Recently Used Shapes, Rectangles,
Lines, Basic Shapes, Equation Shapes, Flowchart, Block
Arrows, Stars, and Banners.

Icons: Displays the dialog box for inserting icons. Icons can be
used to visually convey information through symbols.
 3D Models: This feature enables users to easily insert a 3D
model and effortlessly explore it from various perspectives by
rotating it. The drop-down menu includes the options "From a
File" and "From Online Sources."

SmartArt: The SmartArt feature provides a wide range of

options for creating visually appealing graphics. By selecting
the "Choose a SmartArt Graphic" dialog box, you can
explore various categories such as List, Cycle, Process,
Hierarchy, Matrix, Relationship, Pyramid, Picture, and
Office.com.

Screenshot: You can easily insert a snapshot from your

Windows. The drop-down menu includes the options: Available
Windows and Screen Clipping.

Clip Art: The Clip Art feature allows you to easily insert
drawings, sounds, movies, photos, and more into your
presentation.

Add-ins

Get Add-ins: Opens the Office Add-ins window, allowing you

to view all the add-ins available in the Office store.

My Add-ins: The Office Add-ins window will show you a

comprehensive list of all the add-ins currently installed on your
system.
Charts
To access the "Insert Chart" dialog box, simply select your data and click
on the dialog box launcher located in the bottom right corner of the tab.
When a chart is active, the "Change Chart Type" dialog will be displayed.
Recommended Charts: Insert recommended charts for the
selected data. Excel can automatically detect the data type you
have and provide helpful suggestions for the most appropriate
charts to use.

Column or Bar: Choose between a column or bar chart using

the drop-down menu. This tool allows for a visual comparison
of values across multiple categories. The drop-down menu
consists of several commands, including 2-D Column, 3-D
Column, 2-D Bar, 3-D Bar, and More Column Charts.

Line or Area: Choose between a line or area by using the

drop-down menu. This tool is effective for illustrating trends
across various periods, such as years, days, and months. The
drop-down menu includes various commands such as 2-D Line,
3-D Line, 2-D Area, 3-D Area, and More Line Charts.

Pie or Doughnut: Choose between a pie or a doughnut from

the drop-down menu. This is a common method for illustrating
the relative size of different parts within a whole. The drop-
down menu includes various commands for creating different
types of pie charts, such as 2-D Pie, 3-D Pie, Doughnut, and
More Pie Charts.

Hierarchy: Drop-down menus are a common feature in

hierarchical structures. This is a common method for
comparing individual components to the overall entity. The
drop-down menu includes options such as Treemap, Sunburst,
and additional Hierarchy Charts.

Statistical drop-down menu: This tool is used for statistical

analysis of your data. The drop-down menu offers a variety of
commands, including Histogram, Box and Whisker, and More
Statistical Charts.

Scatter XY or Bubble - Drop-Down. It demonstrates the

connection between a set of values. The drop-down menu
includes options such as Scatter, Bubble, and More Scatter
Charts.

Waterfall or Stock: This drop-down menu offers a variety of

commands including Waterfall, Funnel, Stock, Surface, Radar,
and More Stock Charts.

Combo: Combo is a useful option when dealing with a wide

range of values or when working with a combination of
different data types. The drop-down menu includes two
commands: Combo and Create Custom Combo Chart.

Maps - Drop-Down. The drop-down menu includes the

options: Filled Map and More Map Options.

PivotChart - Button with Dropdown. This can be used to

visually summarize and analyze complex data. The button will
bring up the dialog box for creating a PivotChart. The drop-
down menu includes the options: PivotChart and PivotChart &
PivotTable.

Surface or Radar: The drop-down menu offers three options:

Surface, Radar, and More Charts.

Additional Charts - Dropdown Menu. Provides access to a

variety of different chart types. The drop-down menu includes
 a variety of commands, such as Stock, Doughnut, Surface,
Radar, Bubble, and All Chart Types.

Tours

3D Map: This function is utilized to assign graphical data to a

3D map. You can use it to generate data visualizations within
your workbooks. The button opens the window for "Power
Maps for Excel." The drop-down menu consists of two
commands: Launch Power Map and Add Selected Data to
PowerMap. To use this 3D map feature, it is necessary to
enable the Data Analysis add-ins.

Sparklines

Line: The "Create Sparklines" dialog box allows you to insert

a line chart within a single cell, with each line representing a
row of data in your selection.

Column: The column option allows you to access the "Create

Sparklines" dialog box, enabling you to effortlessly insert a
column chart into a single cell.
 Win/Loss: Clicking on the "Create Sparklines" option will
open a dialog box where you can insert a win/loss chart into a
single cell.

Filter options

Slicer: The slicer is a powerful tool that allows you to visually

filter data in your Tables.

Timeline: The timeline filter allows you to easily interact with

dates in your tables, pivot charts, and pivot tables.

Links
Link: Pressing Ctrl + K is a useful keyboard shortcut. Opens the dialog box
for inserting a hyperlink. This feature allows you to easily create links
within your document for convenient webpage navigation.

Text

Text Box: Insert a textbox that can be placed anywhere on the

page.

Header & Footer: Your cursor will be automatically

positioned in the header box. After performing this action, the
"Header & Footer Tools" will be displayed.

WordArt - Drop-Down: Provides a selection of Word Art

styles for you to insert. The drop-down menu offers a wide
selection of different styles to choose from.

Signature Line: The button adds a signature line that specifies

the person who needs to sign. The drop-down menu displays
two commands: Microsoft Office Signature Line and Add
Signature Services.
 Object: The "Object" dialog box allows you to insert
entrenched objects.

Symbols
With this feature, you can easily insert symbols that are not available on
your system keyboard.

Equation - Drop-Down: Here, you can easily add your desired

equation to your workbook. The drop-down menu includes
various commands such as Area of Circle, Expansion of a Sum,
Binomial Theorem, Fourier Series, and Insert New Equation.

Using the Page Layout Tab

Themes

Themes - Dropdown options: By making adjustments to fonts,

colors, and effects, you can completely transform the
appearance and atmosphere of your workbook. The drop-down
menu consists of the following commands: Built-in, More
 Themes on Microsoft Office Articles, Browse for Themes, and
Save Current Theme. Here are the available built-in themes:
Office, Facet, Ion, Integral, Retrospect, Boardroom, Organic,
Wisp, Slice, Berlin, and Frame. The tooltip displays the current
theme, with the default theme being "Office".

Colors: Changing the color of the document is possible. The

application presents a comprehensive list of available colors
and provides the option to modify the color element of the
current theme. The drop-down menu offers two options: Built-
in and Customize Colors.

Font Selection - Dropdown Menu: You can change the font

style of your texts. The application provides a comprehensive
list of available fonts and allows you to easily modify the font
style within the current theme. The drop-down menu offers two
options: Built-in and Customize Fonts.

Effects - Dropdown menu: The effect tool allows you to

modify the overall appearance of your document, including
options like adding shadows or shading. The list of available
effects is displayed, giving you the ability to modify the effect
element of the current theme.
Page Setup

You can modify the settings of your workspace through the page setup. You
can also modify various aspects such as the orientation, margin,
background, and page size.
 Margins: Allows you to select from a range of pre-set margin
options or gives you the flexibility to create your custom
margins. The drop-down menu offers a selection of commands
including Normal, Narrow, Wide, and Custom Margins.

Orientation: You can easily switch between Landscape and

Portrait layout.

Size: You have the option to choose from a variety of paper

sizes for your document. The "Page Setup" dialog box, Page
tab, displays the More Paper Sizes option.

Print Area: Choose the desired print area from the drop-down
menu. You have the option to select the specific section of the
sheet that you wish to print. The drop-down menu includes the
options to set the print area or clear the print area.

Breaks: The drop-down menu includes the following

commands: Insert Page Break, Remove Page Break, and Reset
All Page Breaks.

Background: This feature allows you to easily incorporate a

background image into your worksheet. If an image is assigned
to the current worksheet, the caption will change to 'Delete
Background'.

Print Titles: The "Page Setup" dialog box, Sheet tab is where
you can find the print titles option. You have the option to
specify which rows or columns should be repeated on every
printed page.

Scale to Fit
 Width: The width of the printout can be adjusted to fit a
specific number of pages. The combo box includes options
such as Automatic, 1 to 9 pages, and More Pages. The More
Pages feature allows you to access the "Page Setup" dialog
box by selecting the Page tab.

Height: This feature allows you to adjust the height of the

printout to accommodate a specific number of pages. The
combo box includes options such as Automatic, 1 to 9 pages,
and More Pages. The "Page Setup" dialog box and Page tab
can be found in the More Pages section.

Scale: Scaling allows you to adjust the size of the printout,

either making it smaller or larger as needed. The page scale can
be adjusted in increments of 5%. Additionally, it offers a
convenient way to access the Page Setup.

Sheet Options

Gridlines View: Toggle the display of gridlines on the active

worksheet with the Gridlines View option.

Printing Gridlines: Allows you to choose whether or not to

include gridlines when printing.
 Headings View: The Headings View option allows you to
easily toggle the visibility of row numbers and column headers
on the active worksheet.

Headings Print: Toggle the option to print row and column

headers.

Arrange

Bring Forward: The button allows you to bring the selected

object forward, effectively reducing the number of objects that
hide it. The drop-down menu includes the options of Bring
Forward and Bring to Front. Using the Bring to Front function
will place the highlighted object in front of all other objects.

Selection Pane: The Selection Task Pane allows you to easily

view and modify the order or visibility of all your objects.

Send Backward: Move an element to a lower layer using the

Send Backward button and its accompanying drop-down
menu. The button allows you to move the selected object to a
lower level, effectively hiding it behind other objects. The
drop-down menu includes the options to Send Backward and
Send Back.

Align: This action will alter the position of the chosen object
on your worksheet. The drop-down menu includes various
commands for aligning and distributing objects, as well as
options for snapping to shapes and grids. Additionally, you can
view gridlines to assist with positioning.

Group: You can combine objects as a single unit, allowing you

to format them collectively. The drop-down menu includes the
options: Group, Ungroup, and Regroup.

Rotate: You have the option to rotate or flip the selected

object. The drop-down menu includes a variety of commands
 for rotating and flipping images.

Using the Formulas Tab

Insert Function: Use the Insert Function shortcut (Shift + F3).

Dropdown. You can input a function (formula) into your
current cell. The "Insert Function" dialog box allows you to
search for a specific function or view the list of functions
grouped.

AutoSum: The button will automatically insert the Sum

formula into the cell, calculating the total of the selected cells
and placing it below the selected cells. The drop-down menu
includes a variety of commands such as Sum, Average, Max,
Min, Count Numbers, and More Functions.

Recently Used: Offers convenient access to the most recent 10

functions you have used.

Financial - Drop-Down: Efficiently access a wide range of

financial functions that can be seamlessly integrated into your
worksheet under the Financial category.

Logical: Get instant access to a comprehensive range of logical

functions in the Logical category, ready to be incorporated into
your worksheet.

Text: Get instant access to a wide range of text functions in the

Text category that can be easily added to your worksheet.
 Date & Time: Offers convenient access to a comprehensive
range of date and time functions within the Date & Time
category, which can be easily incorporated into your worksheet.

Lookup & Reference: Get instant access to a comprehensive

range of lookup or reference functions in the Lookup &
Reference category that can be easily added to your worksheet.

Math & Trig: Offers convenient access to a comprehensive

range of Mathematics and Trigonometry functions. There are
some valuable additions to your worksheet available in the
Mathematics & Trigonometry category.

Additional Functions - Dropdown Menu: Presenting a drop-

down menu that provides access to additional functions,
including Statistical, Information, Engineering, Compatibility,
Cube, and Web categories.

Defined Names

Name Manager: The Name Manager feature allows you to

easily locate and edit all the names used in your workbook.

Define Name: The drop-down menu includes the following

commands: Define Names and Apply Names. The Define
Names feature allows you to easily create named ranges at both
the workbook and worksheet levels. It also provides a
convenient "New Name" dialog box for this purpose.

Use in Formula: The drop-down menu includes a

comprehensive selection of names used in the workbook,
allowing you to conveniently insert them into the current
formula.

Create from Selection: This feature automatically creates

names based on the selected cell. This feature allows you to
 assign a name to a specific range of cells using a title that you
have provided.

Formula Auditing

Trace Precedents: This feature reveals the connections

between cells, showing which cells impact the value of the
currently selected cell.

Trace Dependents: The "Trace Dependents" feature also

shows arrows indicating which cells are impacted by the value
in the currently selected cell.

Remove Arrows: The button eliminates all the arrows created

by the trace precedents and trace dependents. The drop-down
menu includes the following options: Remove Arrows, Remove
Dependent Arrows, and Remove Precedent Arrows.

Display Formulas (Ctrl + '): The formulas are displayed in

each cell instead of the result.

Checking for Errors: This is a guide to help identify common

errors that may occur when using formulas. The drop-down
menu includes the following commands: Error Checking, Trace
Error, and Circular References. The Circular References
 extension will only be enabled if there is at least one circular
reference in the current workbook.

Evaluate Formula: This feature allows you to effectively

debug complex formulas by evaluating each part of the formula
individually. In other words, it enables you to step through a
formula calculation to ensure its accuracy.

Watch Window: The Watch Window feature enables you to

conveniently monitor the contents of cells and their
corresponding results while making updates to other sections of
the worksheet.

Calculation

Calculation Options: You can use the drop-down menu to

efficiently modify the calculation setting in the active
workbook to switch between Automatic, Manual, and
Automatic except for Data Tables.

Calculate Now: Calculate now by pressing F9. Offers a

convenient way to quickly calculate the entire workbook.

Calculation Sheet: (Shift + F9). Offers a convenient method

for quickly calculating the active worksheet.

Using the Data Tab

 Get Data: Uncover, link, and merge data from various sources.
The drop-down menu contains a variety of commands, such as
importing data from different sources, launching the query
editor, and adjusting query options and source settings.

From Text/CSV: Import data from a text, comma-separated

value, or formatted text file.

From Web: Retrieve data from a web page.

From Table/Range: This feature allows you to easily create a

new query that is connected to the table you have selected.
When the selected range is not a part of a table, it will be
converted into a table.

Recent Sources: Stay organized and seamlessly connect to

your most up-to-date data sources with our Recent Sources
feature.

Existing Connections: Import data from popular sources to

enhance your existing connections.

Queries & Connections

Refresh All: Press the key combination Ctrl + Alt + F5 to

refresh all. Selection menu. The button provides a
comprehensive overview of the data in the current workbook,
sourced from external inputs. The drop-down menu contains
the following options: Refresh, Cancel Refresh, Refresh Status,
and Connection Properties.

Queries & Connections: Queries & Connections allows users

to easily access data connections that are linked to external data
sources beyond the workbook.

Properties: The properties of cells connected to data sources

determine how they are updated and what content is displayed.
 Edit Links: This feature enables you to easily access and
modify any connections between your current spreadsheet and
other file types.

Sort & Filter

Sort A to Z: This function arranges the data in ascending

order, organizing it alphabetically or numerically from the
lowest value to the highest value.

Sort Z to A: This feature allows you to arrange your data in

descending order, whether it's in alphabetical or numerical
form. It ensures that your information is organized from the
highest value to the lowest value.

Sort: Sort functionality allows for efficient data organization

and retrieval by arranging values in a desired order.

Filter: Enable or disable filtering for the selected cell.

Clear: Resets the filter and sort settings for the currently
selected range of data.

Reapply - (Ctrl + Alt + L): By reapplying the filter and

sorting to the current selection, you can ensure that any changes
you've made are incorporated.
 Advanced: Use advanced filtering techniques to apply complex
criteria.

Data Tools

Text to Columns: Text to Columns allows you to effortlessly

divide a single column of text into multiple columns.

Flash Fill: Flash Fill is a feature that conveniently populates

values into the cell for you.

Remove Duplicates: This function removes duplicate entries

from a row.

Data Validation: You can restrict the data that can be inputted
into a cell. These options are included in the drop-down menu.
Remove Invalid Data and Clear Validation Circles

Consolidate: This function allows you to efficiently summarize

data from multiple ranges and consolidate the results into a
single output range.

Relationships: The relationship feature allows for the creation

or modification of relationships between tables, enabling the
display of linked data from different tables on a single report.

Manage Data Model: Open the Power Pivot window to add

and prepare data or continue working on data in the workbook.
Forecast

What-If Analysis: With this function, you can experiment with

different values for the formula in your sheet by using the drop-
down options. The drop-down menu includes the following
commands: Scenario Manager, Goal Seek, and Data Table.

Forecast Sheet: Create a new worksheet to predict data trends

with the Forecast Sheet feature. You will be able to predict your
timeline data.

Outline

Group: You can use the button to easily group a selection of

rows and columns. The drop-down menu includes the
following options: Group and Auto Outline. The Auto Outline
command option generates an outline automatically based on
the current section.

Ungroup: You can use the button to separate a range of cells

that have been grouped. Outlines are eliminated from the
existing rows and columns. The drop-down menu includes the
options to ungroup and clear the outline. The Clear Outline
command option will remove all levels of outline.

Subtotal: This option will automatically insert subtotals for the

selected rows, allowing you to calculate rows of related data.

Show Detail: Expand a collapsed group of cells to view more

details.

Hide Details: Condense a collection of cells.

Using the Review Tab

Proofing
 Spelling: Check your spelling using the spell check function
(F7). You can use the spelling feature to easily verify the
accuracy of the spelling on the active worksheet.

Research: Toggle the display of the Research Task Pane to

access research options.

Thesaurus: Use the thesaurus to explore alternative

expressions for conveying your intended message.

Accessibility

Check Accessibility: This will show the Accessibility Checker

Task Pane. This acknowledges the potential for enhancing your
workbook to better accommodate individuals with disabilities.

Insights

Smart Lookup: The Smart Lookup feature will show you the
Smart Lookup Task Pane. By using this feature, you can gain a
deeper understanding of the selected text through the ability to
search for images, definitions, and other relevant articles.

Language
 Translate: This feature allows you to translate the selected text
into a different language.

Comments

New Comment/Edit Comment: Edit your comment by

pressing Shift + F2. Adds a comment to the cell that is
currently active.

Delete: Removes the chosen comment within a cell.

Previous Comment: Move to the previous comment in the

active workbook.

Next Comment: Move to the next comment in the active

workbook.

Show Comments: Toggle the display of the Comments task

pane.

Notes

Notes: The drop-down menu includes various commands such

as creating a new note, navigating to the next or previous note,
displaying all notes, showing or hiding a note, and converting
notes to comments.
Protect

Protect Sheet: Safeguard your worksheet from unauthorized

changes and restrict editing permissions to maintain data
integrity.

Protect Workbook: Ensure the security of your workbook by

preventing unauthorized individuals from making any
structural modifications.

Allow Edit Ranges: This useful feature allows designated

users to make adjustments to specific ranges within a workbook
or sheet. It is possible to implement password protection for the
ranges in your workbook.

Unshare Workbook: The option to unshare the workbook is

deactivated by default. When you open a workbook that is
currently being shared using the legacy "Share Workbook"
option, the feature will become activated, allowing you to
unshare the workbook.
Ink
Hide Ink: This option will not remove ink in your workbook but it will
hide it. The drop-down menu includes the following options: Hide Ink,
Delete All Ink on Workbook, and Delete All Ink on Sheet.

Using the View Tab

You can find the commands for hiding and viewing workbooks and
windows on this tab.
Exploring Workbook Views
 Normal: The normal view allows you to easily view the
worksheet. In addition, you can find a shortcut to this view in
the lower right corner of the status bar.

Page Break Preview: Page Break Preview is a useful feature

that allows you to adjust page breaks and visualize where they
will appear when the document is printed. In addition, you can
find a shortcut to this view in the bottom right corner of the
status bar.

Page Layout: The page layout displays the worksheet in the

format it will have when printed. The application features a
useful horizontal and vertical ruler that assists. Make sure to
include or modify headers and footers, row and column
headings, and scaling options. Also, double-check the margins.

Custom Views: Save your current display settings for future

use with Custom Views.

Full Screen: The workbook can be displayed in full-screen

mode. Pressing the Esc key will bring you back to the regular
screen mode.

Show
 Formula Bar: The formula bar can be toggled to show the
formula in a cell.

Ruler: The ruler feature allows you to easily toggle the display
of the vertical and horizontal rulers next to your document. You
can access it by switching to Page Layout view. The ruler
displays the units that are set in the regional settings by default.

Gridlines: The presence of gridlines enhances the readability

of the sheet by clearly demarcating the rows and columns.

Headings: Toggle the display of column headings and row

numbers.

Message Bar: The message bar is displayed to facilitate any

necessary actions.

Zoom

Zoom: Zoom functionality is available to adjust the level of

your worksheet according to your preference.

100%: Zoom your workbook to its standard size.

Zoom to Selection: Adjust the worksheet display to show only

the cells that are currently selected, filling the entire window.

Window
New Window: Create a new window to easily manage your
current workbook.

Arrange All: This feature allows you to conveniently arrange

all open windows side by side on your screen.

Freeze Panes: The drop-down menu includes the following

options: Freeze Panes, Freeze First Column, and Freeze Top
Row.

Split: The window can be split into resizable panes, allowing

for multiple views of the same workbook.

Hide: Hides the current workbook or window.

Unhide: This feature enables you to reveal a workbook or

window that was previously hidden.

View Side by Side: Compare and contrast the contents of two

workbooks by viewing them side by side.

Synchronous Scrolling: Enable synchronous scrolling to

synchronize the scrolling of two windows, ensuring they move
together. For this command to be enabled, the Side by Side
option needs to be active.

Reset Window Position: Resetting the window position allows

for equal screen sharing between the two windows.
 Save Workspace: This convenient option allows you to
preserve the arrangement of all currently open workbooks,
enabling you to easily restore this layout in the future.

Switch Windows: You can seamlessly switch between your

windows. The drop-down displays a comprehensive list of the
workbooks/windows that are currently open.

Macros

Macros: The button serves as a convenient shortcut to access

the View Macros feature, which allows users to view all
available macros. The drop-down menu includes the following
options: View Macros, Record Macro, and Use Relative
References.

Using the Help Tab

This tab provides convenient access to the Help Task Window and a
selection of helpful website links.

Help & Support

Help: The Help task pane will be displayed, providing access

to the home page where you can seek guidance from Microsoft.
 Contact Support: Access the Help task pane to seek assistance
from an Office support agent.

Feedback: The Feedback pane shows the Feedback tab

accessed through the File tab.

Show Training: Display the Help task pane to show the

training. Here are the training and learning materials for you to
explore.

What's New: Introducing the What's New task pane, which

provides a convenient way to stay updated with the latest
installed updates.

Community

Community: Join our community to connect with experienced

Excel professionals. Clicking on it will redirect you to the
Excel homepage.

Excel Blog: This will open your default browser and take you
to the Excel blog, where you can connect with fellow Excel
users.
 Suggest a Feature: This will automatically open your default
browser and direct you to the Excel page where you can submit
your ideas for new features that you would like to see in future
versions of Excel.
Tell Me Box
Discover a powerful search box that efficiently locates the commands you
need. Additionally, you have the option to input a question and receive
relevant search results.
 CHAPTER FIVE

Getting Started with Excel

How to open Microsoft Excel
The process of opening Microsoft Excel can differ slightly depending on
the operating system you are using. These are the steps to open Microsoft
Excel on both Windows and Mac:
For Windows users
Using the Start Menu:

Click on the Start button or press the Windows key on your

keyboard.

Enter "Excel" in the search bar.

Choose Microsoft Excel from the search results.

Using the Run Command:

Open the Run dialog box by pressing Win + R.

Enter "excel" and hit the Enter key.

Using the Taskbar:

If Excel is pinned to your taskbar, simply click on the Excel

icon.

Using File Explorer:

Go to File Explorer and find the folder where Microsoft Office

is installed. It's usually located in either C:\Program
 Files\Microsoft Office or C:\Program Files (x86)\Microsoft
Office.

Locate the EXCEL.EXE file and give it a double-click.

For Mac users

Using the Launchpad:

Click on the Launchpad icon located in the Dock.

Enter "Excel" into the search field located at the top.

Open Microsoft Excel.

Using the Applications Folder:

Open the Finder.

Head over to the Applications folder.

Locate and double-click on Microsoft Excel.

Using Spotlight Search:

To open Spotlight Search, simply press Command + Space.

Enter "Excel" and press the Enter key.

Some helpful suggestions

How to Create a Shortcut:

On Windows, you have the option to conveniently access Excel

by right-clicking on its icon and selecting either Pin to Start or
 Pin to Taskbar.

On Mac, you have the option to conveniently access Excel by

dragging its icon to the Dock.
Opening a specific file:

Opening an Excel file is as simple as double-clicking it, which

will automatically launch Microsoft Excel.

How to open a new workbook

Starting a new workbook in Microsoft Excel is a simple and easy task.
Here are the steps:

Start the Excel application on your computer.

When Excel is launched, the Start screen will be displayed.

Choose the "Blank workbook" option.

If you already have Excel open with a workbook, navigate to

the File menu located in the top-left corner. Choose "New."
Choose the option "Blank workbook" from the menu.

Mastering Keyboard Shortcuts

For Windows users, a quick way to open a new workbook is by

pressing Ctrl + N.

For Mac users, opening a new workbook is a breeze. Just press

Command + N and you're good to go.

Using the Ribbon Interface

Home Tab:

Make sure you are on the Home tab.

 Click on the "New" option located in the top-left corner of the
screen. From there, choose the "Blank workbook" option.

Excel for Web

Go to the Excel Online platform and log in using your

Microsoft account.

Create a new workbook by selecting "New blank workbook".

How to launch an existing workbook

If you want to open a workbook in Microsoft Excel, here are the steps you
can take based on your operating system:
For Windows users:
With File Explorer:

Open the File Explorer.

Go to the location where your Excel workbook is stored.

Simply double-click on the Excel file you wish to open. The

file should have an extension such as .xlsx, .xls, .xlsm, and so
on.

Using Excel:

Open up Microsoft Excel.

Select the File option located in the top-left corner.

Choose "Open" from the menu.

Go to the location of your workbook, choose it, and click Open.

Using Search:

Click on the Start menu or press the Windows key.

Enter the name of your Excel workbook.

Open the workbook by clicking on it in the search results.

For macOS users:

Using Finder:

Open the Finder.

Go to the location of your Excel workbook.

Simply double-click the Excel file you wish to open.

Using Excel:

Open up Microsoft Excel.

To begin, locate the File option in the top-left corner.

Choose "Open" from the dropdown menu.

Go to the location of your workbook, choose it, and click Open.

Using Spotlight:

To open Spotlight, simply press Cmd + Spacebar.

Enter the name of your Excel workbook.

Open the workbook by clicking on it in the search results.

How to work on your Excel Sheet

When it comes to working on an Excel sheet in Microsoft Excel, there is a
wide range of tasks that need to be tackled, including data entry, complex
calculations, and data analysis.
Here's a simple guide on how to work with Excel:

Start Microsoft Excel on your computer. To access Excel, you

can either search for it in the start menu or click on its icon in
the taskbar if you have it pinned.

When you open Excel, you will usually begin with a new
workbook. A workbook is a file that allows you to conveniently
store your data. Multiple worksheets can be included in each
workbook.

To enter data, simply click on a cell and begin typing. To

navigate to the cell below, press Enter. Alternatively, press Tab
to move to the cell on the right. Another option is to easily
transfer data from other sources into Excel.

When it comes to formatting data, you have the power to

customize its appearance. This includes adjusting the font size,
style, color, and alignment to your liking. In addition, you have
the option to apply various number formats, such as currency or
date formats, to your data.

Excel offers a wide range of powerful built-in functions that

can be used for calculations and data analysis. Begin a formula
by entering an equals sign (=) in a cell, then proceed with the
function name and its arguments. As an illustration, the formula
"=SUM(A1:A10)" calculates the sum of the values in cells A1
through A10. Functions like SUM, AVERAGE, MAX, MIN,
IF, VLOOKUP, etc. can also be used.

Excel workbooks can contain multiple worksheets, allowing for

efficient management of data. The functionality of Excel allows
you to easily manage your worksheets by adding, deleting,
 renaming, and rearranging them using the tabs at the bottom of
the Excel window.

Excel offers a wide range of options for creating charts and

graphs, allowing you to effectively visualize your data. To
create a chart, first, choose the data you want to include. Then,
navigate to the Insert tab and select the type of chart you prefer.

The ability to filter and sort your data allows you to narrow
down and organize the information according to your needs. To
apply filters and sort your data, you can use the Filter button in
the toolbar or the Sort & Filter options in the Data tab.

Excel provides a range of data analysis tools that can be

incredibly useful. One such tool is PivotTables, which enables
you to efficiently summarize and analyze extensive datasets.
Additionally, Excel's What-If Analysis tools allow you to
experiment with different scenarios by adjusting input values.

After completing your work on the Excel sheet, you have the
option to save your workbook by clicking on the File tab and
selecting Save or Save As. Another option for sharing your
workbook is to send it via email or save it to a shared network
location.

How to zoom in and out of your worksheet

Here are the steps to zoom in and out of your worksheet in Microsoft Excel:
Using the Zoom Slider:

Find the zoom slider in the bottom-right corner of the Excel

window. The visual representation consists of a slider
accompanied by symbols: a plus (+) and a minus (-) sign.

Adjust the slider by dragging it to the right to zoom in or to the

left to zoom out. Excel will automatically adjust the zoom level
as you drag.
Using the Zoom Controls:

Alternatively, you can use the zoom controls found on the

ribbon.

Head over to the "View" tab located on the Excel ribbon.

Within the "Zoom" group, the zoom controls are readily

visible. To zoom in, simply click the "+" button. Conversely, to
zoom out, click the "-" button.

Additionally, you have the option to click on the zoom

percentage display, which will open a dialog box allowing you
to enter a specific zoom percentage.

Mastering Keyboard Shortcuts:

Use keyboard shortcuts for efficient zooming in and out:

Pressing the Ctrl and + (plus sign) keys together will allow you
to zoom in.
Pressing the Ctrl and - (minus sign) keys together will allow
you to zoom out.
Pressing the Ctrl and 1 keys simultaneously will bring you back
to 100% zoom.

Using the Mouse Wheel:

To zoom in or out on your screen, simply hold down the Ctrl

key on your keyboard and use the scroll wheel on your mouse.
Scrolling up will zoom in while scrolling down will zoom out.

How to enter data into a cell

Inputting data into a cell in Microsoft Excel can be done easily. Here is a
clear and concise step-by-step guide:

Start Microsoft Excel on your computer.

 Begin by either creating a new workbook or opening an
existing one in the desired location for data entry.

Simply click on the cell where you'd like to input the data.
There are two ways to navigate: using your mouse to click on
the cell or using the arrow keys on your keyboard.

To input data, select the cell and begin typing the desired
information. Text, numbers, dates, or formulas can all be used.

Remember to press the Enter key on your keyboard after typing

the data. After confirming the entry, the selection will
automatically move to the next cell below if you're entering
data in a column or to the right if you're entering data in a row.

To make changes to the data you've entered, simply double-

click on the cell, make the necessary edits, and press Enter to
confirm.

Another option is to use the Formula Bar, which is situated

above the worksheet, to input or modify data. Simply click on
the formula bar, enter your data, and press Enter.

To enter data in multiple cells, just repeat steps 3 to 7 for each

cell.

How to identify a cell name

Cell names in Microsoft Excel usually refer to either a named range or a
named cell. These names are assigned to cells or ranges of cells to simplify
referencing them in formulas and functions. Here is a step-by-step guide to
help you identify a cell name in Excel:

Formulas Tab:

Head to the "Formulas" tab located on the Excel ribbon.

 Find the "Name Manager" option in the "Defined Names"
group. Click on it.

When you click on this, the "Name Manager" dialog box will
open. In this dialog box, you will be able to view a
comprehensive list of all the named ranges and cells in the
workbook.

Using Name Box:

The name box is conveniently positioned next to the formula

bar, allowing you to easily identify the name of the cell or
range that is currently selected.

To view a comprehensive list of all named ranges and cells in

the workbook, simply click on the drop-down arrow in the
name box. Here, you'll find a list of names assigned to cells.

Using Formulas:

Formulas can be used to verify whether a particular cell or

range has been assigned a name. As an illustration, the
combination of the CELL function and the NAMES function
can be used to ascertain whether a cell has been assigned a
name. Presenting an example formula:
If there is an error in retrieving the names from cell E1, the message "No
Name" will be displayed. Otherwise, if a name exists, the message "Name
Exists" will be displayed.
This formula is designed to verify if cell E1 contains a name. If a name is
found, it will display "Name Exists", otherwise it will display "No Name".

How to copy and paste data

Copying and pasting data in Microsoft Excel is a straightforward process.
Here is a detailed guide with clear instructions:
 To select the data, simply click and drag your mouse cursor
over the cells or range of cells you wish to copy.

Once you have chosen the data, there are two ways to copy it:
either right-click on the selected area and select "Copy" from
the context menu, or use the keyboard shortcut: Use Ctrl + C
(on Windows) or Command + C (on Mac) to copy.

Place your cursor in the desired cell for pasting the copied data.

To paste the content, simply right-click on the destination cell

and choose the "Paste" option from the context menu.
Alternatively, you can use the keyboard shortcut: To paste, use
Ctrl + V (on Windows) or Command + V (on Mac).

After pasting the data, Excel conveniently offers a range of

options to customize how the data is pasted. There are various
options available to suit your specific requirements, such as
"Paste Values Only" or "Paste Formatting."
Another option is to use the paste special function. This can be done by first
selecting the cell where you want to paste the content, and then accessing
the "Paste Special" option either through the right-click menu or the
"Home" tab in the ribbon. You can paste data with a range of options, such
as pasting only values, formulas, formatting, and more.

How to add a new worksheet

Here is a step-by-step guide:
Using the Ribbon:

Open Excel and go to the bottom of the Excel window, where

the worksheet tabs are located.

Simply right-click on any of the existing worksheet tabs.

Choose "Insert" from the context menu that appears.

 Select "Worksheet" and click OK. By performing this action, a
new worksheet will be added to the right of the currently
selected worksheet.

Keyboard Shortcut:

Just press Shift + F11. Inserting a new worksheet to the left of

the currently active worksheet is a simple process.

Using the "+" Button:

Alternatively, you can also find the "+" button at the end of the
worksheet tabs. In addition, a new worksheet will be inserted to
the right of the currently active worksheet.

How to rename a worksheet

Follow the steps below:

Find the worksheet tab situated at the bottom of the Excel

window.

To rename a worksheet, simply right-click on its tab.

To rename the tab, you can either double-click on the tab name
or select "Rename" from the context menu that appears.

Users can edit the tab name. Enter the new name for the
worksheet.

Confirm the new name by pressing Enter or clicking away from

the tab.

How to color a worksheet

Organizing and visually differentiating between different parts of your data
can be achieved by coloring a worksheet in Microsoft Excel.
Here is a step-by-step guide:

To select the range, simply click and drag over the cells or
range of cells that you wish to color.

After selecting the range, locate the "Fill Color" button in the
"Font" group on the "Home" tab of the ribbon.

Click on the drop-down arrow next to the "Fill Color" button.

Here's how you can access a color palette.

Select the desired color to apply to the cells you have chosen.
Click on it, and the selected cells will be filled with the chosen
color.

Another way to efficiently fill a selected range with color is by

utilizing a keyboard shortcut. Once you've chosen the range,
simply press Alt + H + H. You can open the color palette and
select the color of your choice.

Conditional formatting enables the automatic application of

formatting, including colors, to cells based on specific
conditions.

To use conditional formatting, choose the desired range for

formatting and navigate to the "Home" tab on the ribbon.

Go ahead and select "Conditional Formatting" from the

"Styles" group. Select the desired type of conditional
formatting and configure the conditions and formatting options
accordingly.

If the color you're looking for isn't available in the standard

palette, you have the option to select a custom color by clicking
on "More Colors" at the bottom of the color palette.
 To remove the color from the selected cells, simply select the
range and click on the "Fill Color" button once more. To
remove the fill, select "No Fill" from the palette or use the
shortcut Alt + H + H and then press N.

How to save your file

Follow the steps below to save your file:

Ensure that your Excel file is open if you have already created
or made any edits to it.

To access the desired options, locate the "File" tab positioned at

the top-left corner of the Excel window and proceed to click on
it.

Choose between "Save As" or "Save" depending on your

needs. If you're saving the file for the first time or wish to save
it with a different name or location, opt for "Save As."
However, if you're simply saving changes to the existing file,
select "Save."

When selecting the location, simply navigate to the desired

folder if you're using the "Save As" function. Using "Save" will
overwrite the existing file.

Provide a name for your file in the designated "File name"

field. It is important to select a name that is both descriptive
and memorable.

Beneath the "File name" field, there is a dropdown menu

labeled "Save as type." Simply choose the file format that best
suits your requirements. By default, Excel uses the ".xlsx"
format, but you have the option to select different formats such
as ".csv" or ".xls" if necessary.

After you've chosen the location, named your file, and selected
the file format, simply click the "Save" button to save your file.
 CHAPTER SIX

Formatting Basics
How to format your data
Properly formatting data in Microsoft Excel is crucial for enhancing its
readability and visual appeal.
Below are a few commonly used formatting techniques:

Font Formatting: In Excel, you can modify the font, font size,
style (bold, italic, underline), and color of the text. To format
the cells or range of cells, simply select them and navigate to
the Font group on the Home tab. From there, you can easily
apply the desired formatting options.

Number Formatting: Excel offers a wide range of number

formats, including currency, percentage, date, and time. To
choose the desired format, simply select the cells or range of
cells containing numbers and then use the Number Format
drop-down list in the Number group on the Home tab.

Alignment: The alignment options allow you to position text

both horizontally (left, center, right) and vertically (top, middle,
bottom) within cells. Adjust the alignment by using the
alignment options found in the Alignment group on the Home
tab.

Cell Borders: Adding borders to cells can provide a visual

distinction for data, making it easier to differentiate. To apply a
border style to selected cells or a range of cells, go to the Home
tab and find the Borders drop-down list in the Font group.
From there, you can easily select the desired border style.

Fill Color and Cell Color: The background color of cells can
be modified to emphasize specific data. To choose the desired
 color, simply select the cells or range of cells and then use the
Fill Color drop-down list in the Font group on the Home tab.

Conditional Formatting: The Conditional Formatting feature

enables you to effortlessly format cells according to specific
conditions. There are various ways to highlight cells based on
specific criteria such as values, thresholds, or text content. To
apply conditional formatting, you can select the cells or range
of cells. Then, navigate to the Home tab and click on
Conditional Formatting. From there, you can easily choose the
desired rule for your formatting needs.

Data Bars, Color Scales, and Icon Sets: Conditional

Formatting offers a range of visual formatting options,
including Data Bars, Color Scales, and Icon Sets. There are
various ways to enhance the visual representation of data in
Excel. Data bars use gradients to show the magnitude of cell
values, color scales assign different colors to cells based on
their values, and icon sets incorporate icons like arrows or flags
to indicate specific values.

Cell Styles: Excel offers a range of pre-designed cell styles that

bring together different formatting options, including font,
number format, and alignment. To access cell styles, go to the
Cell Styles group on the Home tab.

Clear Formatting: To remove all formatting from selected

cells, you can utilize the Clear Formatting option located in the
Editing group on the Home tab.

How to format font style

Properly formatting font styles in Microsoft Excel is crucial for enhancing
the readability and visual appeal of your data.
Here is a step-by-step guide:
 To format the cells, you have two options. You can either click
and drag to select multiple cells, or simply click on a single cell
if you only want to format that one.

The font options can be accessed from the ribbon toolbar

located at the top of the Excel window. If you're not already on
the "Home" tab, go ahead and click on it.

Find the "Font" group. The interface usually includes icons for
various font formatting options such as Bold, Italic, and
Underline.

Click on the small arrow located in the bottom-right corner of

the "Font" group. Opening the Font dialog box is a simple step.

Choose font styles:

The Font dialog box allows you to select from a range of font styles and
attributes.

Font: Choose the font type you prefer from the dropdown
menu. Excel usually offers a selection of frequently used fonts.

Font Style: Choose from the dropdown menu to select the font
style: Regular, Italic, or Bold.

Size: Select the desired font size from the dropdown menu or
input a specific size manually.

Effects: This section allows you to apply various effects such

as Underline, Strikethrough, and Superscript/Subscript.

Review and submit:

As changes are made in the Font dialog box, Excel provides a

preview of the selected text's appearance.
 After selecting the desired font style, simply click "OK" to
apply the changes.

Quick shortcuts:
Additionally, there are keyboard shortcuts available for frequently used font
styles:

Bold: Use the Ctrl + B shortcut.

Italic: Use the keyboard shortcut Ctrl + I to italicize text.

Underline: Shortcut for underlining: Ctrl + U

Conditional Formatting can be used to apply font styles based

on specific conditions or criteria. You can automatically format
cells based on their values or the contents of other cells.

How to format with bold, italics, and underline

Formatting cell contents in Microsoft Excel is not as straightforward as it is
in a word processing program. You cannot directly apply bold, italics, or
underlined formatting to the cells. Nevertheless, similar effects can be
accomplished through alternative approaches:

Bold: To make text bold, you can utilize conditional

formatting. Conditional formatting rules allow for the
formatting of cells based on specific criteria, such as values
exceeding a certain number or containing specific text. If the
necessary conditions are fulfilled, you have the option to apply
bold formatting to the text.

Italics: Just like bold, you have the option to apply conditional
formatting to make text appear in italics. To italicize the text,
you can set up a conditional formatting rule and select the
appropriate option in the formatting settings.
 Underline: Conditional formatting does not offer a direct
option to underline text. However, it is possible to use custom
number formats to create the illusion of underlining. As an
illustration, a custom number format can be created with an
underlined character "_" at the end. While this won't
technically underline the text, it can still produce a visual effect
that might meet your requirements.
To apply conditional formatting, follow these steps:

Choose the cells that you want to format.

Head over to the "Home" tab located on the Excel ribbon.

Find and select "Conditional Formatting" located in the

"Styles" group.

Select "New Rule" from the dropdown menu.

Choose the type of rule you want to create, such as one based
on a formula or specific text.

Establish the criteria for the rule and specify the desired
formatting, such as bold, italics, and/or underline effects.

Click "OK" to apply the conditional formatting rule.

How to format your font color

Microsoft Excel offers multiple options for formatting font colors:
Using the Ribbon:

Choose the cell or range of cells that you want to format.

Head over to the "Home" tab located on the Excel ribbon.

In the "Font" group, there is a button labeled "Font Color"

which is usually represented by an "A" with a colored bar
 underneath. Click on it.

A palette of colors will be displayed. Choose the color you'd

like to use for the selected text.

Using the Format Cells Dialog Box:

Choose the cell or range of cells that you want to format.

To access the "Format Cells" dialog box, you can either right-
click and choose "Format Cells" from the context menu or go
to the "Home" tab and click on the small arrow in the corner of
the "Font" group.

Go to the "Font" tab in the "Format Cells" dialog box.

Choose a color from the dropdown list in the "Color" section.

Mastering Keyboard Shortcuts:

You can use keyboard shortcuts to change the font color

without relying on the ribbon. To access the font color picker,
simply select the desired cell or range of cells and press Ctrl +
Shift + F.

How to format the bottom border

Here are the steps to format the bottom border in Microsoft Excel:

Choose the cell(s) or range where you wish to add a bottom

border.

Head over to the "Home" tab located on the Excel ribbon.

Within the "Font" group, a variety of border options are

available for your selection. To open the "Format Cells" dialog
 box, simply click the arrow icon located at the bottom-right
corner of the "Font" group.

Navigate to the "Border" tab in the "Format Cells" dialog

box.

Click on the icon that looks like the bottom edge of a square to
find the bottom border option. Typically, you'll find it as the
fourth icon in the top row of the border options.

If necessary, the line style, color, and thickness can be adjusted

using the options provided in the dialog box.

To apply the bottom border to the selected cell(s) or range,

simply click "OK".
If you're more inclined towards using keyboard shortcuts, you have the
option to select the cell(s) or range and press "Ctrl + Shift + Underscore
(_)" to apply a bottom border.

How to format the number type

Here are the steps to format numbers in Microsoft Excel:

Choose the cell or range of cells that you would like to format.

Head over to the "Home" tab located on the Excel ribbon.

Within the "Number" group, a drop-down list will be visible,

offering various formatting options. Simply click on it to open
it.

Select the preferred format from the options provided. There

are various options available, such as "Number," "Currency,"
"Percentage," and more. Choose the option that best fits your
requirements. If the format you desire is not visible, you have
the option to click on "More Number Formats" located at the
bottom of the list.
 After selecting a format, Excel will promptly apply it to the
chosen cells.
In addition, you have the option to further customize the number
formatting:

Once you've chosen the cell or range of cells, simply click on

the small arrow located in the bottom right corner of the
"Number" group on the "Home" tab. A dialog box will open to
allow you to format cells.

The "Format Cells" dialog box offers a range of tabs,

including "Number," "Alignment," "Font," and more. Select
the "Number" tab.

Here, you'll find a variety of choices for number formatting,

such as decimal places, currency symbols, and date formats.
Feel free to personalize these options to suit your preferences.

Click "OK" to apply the formatting.

How to format overlapped data

If you're looking to format overlap data in Microsoft Excel, it's important to
clarify what you mean by "overlap data." If you're referring to data that
appears in multiple columns or rows, you may need to adjust the cell layout
by splitting or combining cells to ensure proper organization.
Follow the steps below:

Figure out which cells have data that overlap.

If the overlap occurs within a single cell and you need to divide
the data into multiple cells, Excel's text-to-columns feature can
be used. Here is the step-by-step process:

➢ Choose the cell(s) that have overlapping data.

➢ Head over to the "Data" tab located in the Excel ribbon.

➢ Choose the option "Text to Columns."

➢ Use the prompts in the wizard to indicate the delimiter that

separates the data and select the desired location for the split data.

If you need to combine data from multiple cells into one, Excel
offers two functions that can help: CONCATENATE and
CONCAT. The CONCATENATE function has been around for
a while, while the newer CONCAT function is available in
newer versions of Excel. Here is the step-by-step process:

➢ To begin, in an empty cell, enter =CONCATENATE( or

=CONCAT(, depending on the version of Excel you are using.

➢ Choose the initial cell that contains the data you wish to combine.

➢ Type a comma.

➢ Choose the following cell that contains data.

➢ Make sure to repeat the process for all the cells you wish to
combine.

➢ Make sure to close the function by adding a closing parenthesis

and then press Enter.

When splitting or combining data, it may be necessary to adjust

the size of the cells to display the information correctly. To
adjust the column width or row height, simply select the
column(s) or row(s) that contain the affected cells.

Enhance the formatting by making use of various options like

altering fonts, colors, or borders to improve the visual appeal
and readability of the data.

Depending on the complexity of the overlap, you may have to

go through these steps again for other overlapping data sets in
 your spreadsheet.

How to repeat a format using the format painter

The Format Painter in Microsoft Excel is a handy tool that can help you
save time by easily copying formatting from one cell to another.
Here's a step-by-step guide on how to repeat the format using the Format
Painter:

To select the cell with the desired format, simply click on the
cell that has the formatting you wish to copy.

The Format Painter tool can be easily located in the "Home"

tab of the Excel ribbon, usually in the "Clipboard" group. It is
represented by a paintbrush icon. Simply click on it once to
activate it.

When you activate the Format Painter, the cursor will transform
into a paintbrush icon, allowing you to apply the desired
format. Select the cell or range of cells where you wish to apply
the copied format. The formatting from the original cell will be
applied to the new one in Excel.

Remember to use the Format Painter icon by double-clicking it

when you need to apply the same format to multiple cells or
ranges. You can keep the Format Painter active until you finish
applying the format. After completing your task, you can click
on the Format Painter icon again or press the Esc key to
deactivate it.

How to format data into a table

Organizing data into a table in Microsoft Excel can greatly enhance the
management and analysis of your information.
Here's a clear and concise step-by-step guide:

Start Microsoft Excel on your computer.

Input your data into the Excel worksheet. Ensure that every
column is accompanied by a heading.

Simply click and drag to highlight the cells that contain your
data.

To insert a table, select your data and navigate to the "Insert"

tab on the Excel ribbon.

To access the "Table" option, navigate to the "Tables" group

and select the option labeled "Table". Another option is to use
the keyboard shortcut Ctrl + T.

Make sure to confirm the range of data you want to convert

into a table when prompted by Excel. Make sure to select the
appropriate range and click "OK".

Excel will automatically apply a default table style to your data.

When you have a table selected, simply click on the "Table
Design" tab to access a range of predefined table styles to
choose from.

Take your table's appearance to the next level by making

adjustments to the table style options. This includes formatting
the header row, adding a total row, and creating banded rows,
among other possibilities.

You can input data directly into the table, and Excel will
automatically expand the table to accommodate the new
information.

Excel tables offer a range of useful built-in features, including

filtering, sorting, and totaling. To access these features, you can
either go to the "Table Design" tab or simply right-click within
the table.
 If you find yourself needing to add more rows or columns to
your table, Excel has a convenient feature that automatically
expands the table when you enter data into cells next to it. To
resize the table manually, simply click and drag the resize
handle located at the bottom right corner of the table.

You have the option to format your data within the table using
various Excel formatting tools such as font style, size, color,
and cell borders.

How to rotate text directions

Rotating text in Microsoft Excel is a straightforward process. Here is a step-
by-step guide:

Choose the cell(s) that contain the text you want to rotate.

Head over to the "Home" tab located on the Excel ribbon.

Find the "Alignment" group.

Within this group, you will come across an icon resembling a

tilted "A". Here is the "Orientation" button.

Select the "Orientation" button. A drop-down menu will be

displayed.

There are various rotation options available for the text in the
drop-down menu. There are various angle options available for
you to choose from, including "Vertical Text", "Rotate Text
Up", or "Rotate Text Down".

How to insert a new row and column into your table

Here are the steps to insert a new row or column into your table in
Microsoft Excel:
Adding a new row:
 Choose the row below where you would like to add the new
row. To accomplish this, simply click on the row number
located on the left side of the Excel window.

To access additional options, simply right-click on the row

number you have selected.

Click on "Insert" from the context menu that appears.

Adding a new column:

Choose the column adjacent to where you wish to add the new
column. To perform this action, simply click on the column
letter located at the top of the Excel window.

To access additional options, simply right-click on the letter of

the column you have selected.

Click on "Insert" from the context menu that appears.

Another option is to use keyboard shortcuts:

Pressing Ctrl + Shift + "+" (Plus key) on your keyboard will

allow you to insert a new row.

Pressing Ctrl + Shift + "+" (Plus key) on your keyboard

allows you to easily insert a new column.

How to delete a cell

Removing a cell in Microsoft Excel is a straightforward procedure. Here is
a step-by-step guide:

Simply click on the cell you wish to remove. To delete multiple

cells, simply click and drag to select a range of cells.

To delete the selected cell(s), simply right-click on it and select

"Delete" from the context menu.
 When you decide to delete, a dialog box will pop up, giving
you options on how to handle the surrounding cells. There are
different options available for shifting cells and rows/columns
in your selection. Select the option that best fits your
requirements and click "OK".
Another option is to use the keyboard shortcut:

To delete cells in Windows, simply select the desired cell(s)

and press the "Delete" key on your keyboard. Once more, you
will be asked to select how to shift the cells around.

On a Mac, you can easily delete cells by selecting them and

then pressing the "Fn" + "Delete" keys on your keyboard.

How to hide and unhide data

It's easy to hide and unhide data in Microsoft Excel. Here is a step-by-step
guide:
To Hide Data:

To select the data you want to hide, simply click and drag to
highlight the cells, rows, or columns.

After selecting the data, you can right-click on it. A context

menu will be opened.

Select "Hide": From the context menu, choose the option

labeled "Hide". Another option is to use the keyboard shortcut
"Ctrl" + "0" (zero). The selected data will be concealed.

How to Unhide Data

To select adjacent columns or rows, simply choose the ones

that are next to each other. This is particularly useful if you
have hidden some columns or rows. For instance, if column B
has been hidden, you should choose columns A and C.
 After selecting, you can right-click on the columns or rows you
have chosen. A context menu will be opened.

Select "Unhide" from the context menu by choosing the

appropriate option. Another option is to use the keyboard
shortcut "Ctrl" + "Shift" + "0" (zero). Here's how to reveal
the columns or rows you've chosen.

To unhide specific rows or columns:

To select the entire worksheet, you can click on the "Select All"
button. This button is usually found at the top-left corner where
the row and column headers intersect. Alternatively, you can
use the keyboard shortcut "Ctrl" + "A".

After selecting the entire worksheet, simply right-click

anywhere within the selected area. A context menu will be
opened.

Select "Unhide" from the context menu by choosing the

appropriate option. By following these steps, you can easily
uncover any concealed rows or columns in the worksheet.
 CHAPTER SEVEN

Excel Cell Referencing

Range reference in Excel
Microsoft Excel uses range references to refer to groups of cells on a
worksheet. This is a common practice when you need to carry out
operations or calculations on multiple cells at once.

Here is a step-by-step guide on creating a range reference:

Using Cell References: The use of cell references involves

specifying the starting and ending cells, separated by a colon,
to reference a range of cells. As an illustration, if you want to
refer to a range of cells from A1 to A10, the correct notation
would be A1:A10.

Named Ranges: Assigning a name to a range of cells allows

for easy reference in formulas. This can greatly enhance the
clarity and comprehensibility of your formulas.

Using Functions: Functions in Excel, such as SUM,

AVERAGE, MIN, MAX, etc., can take range references as
arguments. As an illustration, the formula =SUM(A1:A10)
would calculate the total of the values in cells A1 through A10.

Dynamic Range Reference: Excel also offers support for

dynamic range references through the use of functions such as
OFFSET, INDEX, and INDIRECT. These functions enable you
to effortlessly create range references that automatically adjust
according to any changes made to your data.

List of Excel reference styles

Microsoft Excel uses various reference styles to refer to cells, ranges, and
worksheets within formulas. Here are some of the most commonly used
reference styles:

A1 Style: The A1 Style is the default reference style in Excel.

The cells are identified using a combination of their column
letter and row number, such as A1, B2, and C3.

R1C1 Style: The R1C1 style is different from the A1 style in

that rows are numbered and columns are lettered, creating a
reverse order. As an illustration, R1C1 denotes the cell located
in the first row and first column, while R2C3 represents the cell
positioned in the second row and third column.

Relative Reference: The concept of relative references in

formulas is that they automatically adjust their references based
on their position when copied to different cells. As an
illustration, when you have a formula in cell A1 that references
cell B1 (=B1), and you copy that formula to cell A2, it will
automatically adjust to reference cell B2 (=B2).

Absolute Reference: The beauty of absolute references lies in

their unwavering constancy, no matter how many times the
formula is copied. Adding a "$" symbol before the column
letter and/or row number denotes them. As an illustration, the
reference $A$1 is used to denote cell A1, and even when it is
copied to other cells, it will continue to refer to A1.

Mixed Reference: This type of reference, known as a mixed

reference, allows either the row or the column to remain fixed
while the other can change when the formula is copied. As an
illustration, $A1 locks column A while still allowing the row
number to change when copied horizontally. On the other hand,
A$1 locks row 1 while still allowing the column letter to
change when copied vertically.
In Excel, you can easily reference worksheets and workbooks by using
syntax such as 'Sheet1'!Referring to cell A1 in Sheet 1 can be done by using
A1 notation. Similarly, you can refer to cell A1 in another workbook by
specifying the workbook name and sheet name, like '[Workbook.
xlsx]Sheet1'!Referring to cell A1 in Sheet1 of Workbook.xlsx.

How to create references in Excel

The process of creating references in Microsoft Excel enables you to
establish connections between cells or ranges within the same worksheet, as
well as cells in different worksheets or workbooks.
Basic Cell Reference:

Click on the cell where you would like to create the reference.

Begin a formula by typing the equal sign "=".

Click on the cell you wish to reference. Excel will insert the
reference automatically.

Remember to press Enter to finish.

Range Reference:

Click and drag to select a range of cells, similar to the basic cell
reference.

After selecting the range, simply let go of the mouse button.

When you use Excel, the range reference will be automatically
inserted into your formula.

Referencing cells in other worksheets:

When referencing a cell in a different worksheet within the

same workbook, you need to include the worksheet name
followed by an exclamation mark (!) before the cell reference.
As an illustration, "=Sheet2!A1" refers to cell A1 in Sheet2.
 Remember to enclose the worksheet name in single quotation
marks if it contains spaces or special characters. As an
illustration, "='Sheet 2'!A1".

Referencing cells in other workbooks:

When referencing a cell in a different workbook, it is important

to include the workbook name, followed by the worksheet
name and cell reference. As an illustration, the formula "=
[WorkbookName.xlsx]Sheet1!A1" refers to cell A1 in Sheet1
of a workbook named "WorkbookName.xlsx".

Remember to enclose the names of workbooks or worksheets in

single quotation marks if they contain spaces or special
characters.

Understanding the Different Types of References

Excel uses relative references as the default setting. This means

that when you copy a formula to another cell, the reference will
automatically adjust accordingly
When you want to ensure that a reference remains unchanged
when copied, simply add a dollar sign ($) before the column
letter, row number, or both. For instance, you can use "$A$1"
or "A$1" or "$A1".
Mixed references combine absolute and relative references,
allowing one part to change while the other remains fixed. As
an illustration, you can use "$A1" or "A$1".

How to change Excel cell reference in a formula

There are multiple ways to modify cell references in an Excel formula
based on your requirements. Here are various techniques for modifying cell
references:
Editing directly in the formula bar

Choose the cell that contains the formula you wish to modify.

Simply click on the formula bar located at the top of the Excel
window.

You can directly edit the cell references in the formula bar. For
instance, if you have a formula like =A1+B1 and you need to
modify it to =A2+B2, you can easily make the change by
replacing A1 with A2 and B1 with B2.

Hit Enter to apply the changes.

Using the Find and Replace function

Choose the specific cells where you would like to modify the
cell references.

To open the Find and Replace dialog box, simply press Ctrl+H.

Enter the cell reference you want to change in the Find what
field, such as A1.

Enter the new cell reference (e.g., A2) in the Replace with
field.

Click the Replace All option to modify all instances within the
selected range.

Using Relative and Absolute References

The cell references in Excel can be either relative, such as A1, or absolute,
like $A$1. Having a clear understanding of this distinction can be beneficial
when you need to copy or move formulas.

Relative References: When you copy a formula to another

cell, the relative references will change accordingly. As an
 illustration, when you copy the formula =A1+B1 from cell C1
to cell C2, it will automatically adjust to =A2+B2.

Absolute References: When you copy a formula, absolute

references remain unchanged. Use the $ symbol to ensure the
reference is absolute. As an illustration, the value in cell $A$1
will stay the same no matter where you paste the formula.

Mixed References: Both the row and the column can be fixed
by using Mixed References. As an illustration, when copied,
$A1 will fix the column but change the row, while A$1 will fix
the row but change the column.

Using Excel's Offset Function

You can also use the OFFSET function to dynamically change cell
references.
The syntax for the OFFSET function is as follows: =OFFSET(reference,
rows, cols, [height], [width])

Reference: The starting point.

Rows: Move a specified number of rows from the starting
point.
Cols: Specifies the number of columns to move from the
starting point.
Optional parameters [height] and [width] can be used to specify
the size of the range.
For instance, the formula =OFFSET(A1, 1, 1) would point to cell B2.

Using Named Ranges

How to Define a Named Range:

Choose the cell or range that you wish to name.

Navigate to the Formulas tab and select Define Name.

 Provide a name for the range and click OK.

Use Named Range in Formulas:

Consider using named ranges in your formulas instead of cell references.
For instance, if you designate A1 as the StartCell, you can incorporate
=StartCell + B1 into your formula.

To modify the reference of the named range:

To access the Name Manager, navigate to the Formulas tab and

click on it.

Choose the named range and click Edit.

Update the reference and click OK.

How to cross-reference in Excel

When cross-referencing in Microsoft Excel, you can easily compare and
analyze data from various cells, ranges, or even different worksheets or
workbooks. Here are a few popular techniques for cross-referencing data in
Excel:
Using the VLOOKUP function
The VLOOKUP function is commonly used to search for a value in the first
column of a range and retrieve a value in the same row from a specified
column.
Here is the syntax:
The formula VLOOKUP is used to search for a value in a table and return a
corresponding value from a different column. It requires the lookup value,
the table array, the column index number, and an optional range lookup
parameter.
For this scenario, let's say you have a list of employee IDs in column A of
Sheet 1 and their corresponding names in column B. Now, if you're looking
to find the name that is associated with a specific ID in Sheet2, here's what
you can do:
The formula VLOOKUP(A2, Sheet1!A: B, 2, FALSE) can be used to
retrieve data from Sheet1 based on the value in cell A2.

Using INDEX and MATCH Functions

The INDEX and MATCH functions provide a robust alternative to
VLOOKUP. It offers greater flexibility, particularly when the lookup
column is positioned elsewhere within the range.
Here is the syntax:
The formula you can use is =INDEX(return_range,
MATCH(lookup_value, lookup_range, [match_type])).
For a different scenario, let's say you have a list of employee IDs and
names, and now you need to find the name associated with a specific ID:
The formula you provided retrieves the value from column B in Sheet 1 that
corresponds to the first occurrence of the value in cell A2 in column A of
Sheet 1.

Using the HLOOKUP function

Just like VLOOKUP, HLOOKUP allows you to find a value in the first row
of a range and retrieve a value from the same column in a specific row.
Here is the syntax:
The HLOOKUP function is used to search for a value in the top row of a
table and return a corresponding value from a specified row. It is a useful
tool for data analysis and lookup purposes.

Using the IF function to simplify cross-referencing

The IF function is useful for performing simple cross-references,
particularly for conditional compariso- ns.
Here is the syntax:
The formula you provided is the IF function, which is used to perform a
logical test and return different values based on the result.
Example: To verify a value in Sheet1!There is a discrepancy between A2
and a value in Sheet2!B2:
=IF(Sheet1!A2 > Sheet2!B2, "Greater", "Not Greater")

Using Conditional Formatting

Conditional formatting is a useful tool for visually cross-referencing data. It
is possible to emphasize cells in a specific range that either match or do not
match values in another range.
Example:

Choose the specific range you wish to format, such as

Sheet1!A2:A10.

To access the Home tab, simply navigate to Conditional

Formatting and select New Rule.

Select the option to use a formula for determining the cells to

format.

Provide a formula in the following format:

The formula <code>=COUNTIF(Sheet2!A: A, A2) > 0</code> can be used
to check if the value in cell A2 exists in column A of Sheet2.

Adjust the format to emphasize cells that match.

Using Pivot Tables for Cross-Referencing Data

Pivot Tables are incredibly effective at summarizing and cross-referencing
large data sets. Creating a Pivot Table from your data range allows for the
analysis of relationships between different fields.
A Practical Example: Cross-Referencing Between Two Worksheets
Imagine you have two worksheets, Sheet1 and Sheet2, containing the
following data:

Column A in Sheet 1 contains the Employee IDs, while column

B contains the corresponding Names.
Column A in Sheet 2 contains Employee IDs, while column B
contains the corresponding Salaries.
It would be helpful to add a new column in Sheet 1 that displays the salary
of each employee, using their unique Employee ID.
Here are the steps:

Navigate to Sheet 1.

Enter the following formula in cell C2:

The formula <code>VLOOKUP(A2, Sheet2!A: B, 2, FALSE)</code> can
be used to retrieve data from Sheet2 based on the value in cell A2. It
searches for a match in the first column of Sheet 2 and returns the
corresponding value from the second column.

Extend the fill handle downwards to effortlessly apply the

formula to additional cells in column C.
Column C in Sheet 1 will be populated with the salaries from Sheet 2.

How to reference another sheet in Excel

Being able to reference another sheet in Microsoft Excel can greatly
enhance your ability to consolidate data, perform calculations, and organize
information across multiple sheets.
Basic Cell Referencing
Referring to a specific cell:

When referencing a cell from another sheet, you need to

include the sheet name, followed by an exclamation mark (!),
and then the cell reference.

To reference cell A1 in a sheet named Sheet2, you would use

the following:
Sheet2!A1
How to reference a sheet with a name that contains spaces or special
characters:

Remember to enclose the sheet name in single quotes if it

contains spaces or special characters.

To reference cell A1 in a sheet named Sales Data, you would

use the following:
'Sales Data'!A1

Range Reference
Using a Range of Cells as a Reference:

You can similarly reference a range of cells.

To reference the range A1:B10 in Sheet2, you would use the

following:
The text is on Sheet 2.The range A1 to B10

Using References in Formulas

References to another sheet can be used within formulas.

To sum a range from another sheet, you would use the

following formula:
The formula =SUM(Sheet2!A1:A10) calculates the sum of the values in
cells A1 to A10 on Sheet2.

Merging References:

References from different sheets can be combined in a single

formula.

To illustrate, summing ranges from two different sheets:

The formula =SUM(Sheet1!A1:A10, Sheet2!A1:A10) will calculate the
sum of the values in cells A1 to A10 in both Sheet1 and Sheet2.

Creating Dynamic References

Using Named Ranges:

Named ranges can be created to refer to specific cells or ranges

across sheets. The clarity and organization of the formulas are
enhanced.

To name a range, select the range, navigate to the Formulas

tab, and click on Define Name.

Using INDIRECT for Dynamic References:

The INDIRECT function is capable of creating dynamic

references based on cell values.

To reference cell B1 from the sheet named Sheet2, you can use
the formula =Sheet2!B1.
The formula INDIRECT(A1 & "!B1") is used to reference a cell in another
sheet based on the value in cell A1.

Transferring References Between Sheets

Formula replication with references:

When copying a formula that references another sheet, Excel

will automatically update the references to ensure the correct
linkage is maintained.

Make sure to add dollar signs ($) to the references if you want
to keep the reference to the original sheet. As an illustration:
Sheet2!$A$1
An Illustrative Example
Imagine you have two sheets: one for January and one for February.
Calculate the sum of the values in cell B2 from both sheets and show the
result in a third sheet named Summary.

Enter the necessary information in the Summary sheet:

Happy January! February is here! B2 + February!B2

Types of Cell Referencing in Excel

Cell referencing is a crucial concept in Microsoft Excel, as it allows
formulas and functions to access the contents of cells. Three main types of
cell referencing exist:

Relative Referencing
Absolute Referencing
Mixed Referencing

Relative Referencing
Excel uses relative references as the default type of reference. When you
copy a formula that contains relative references from one cell to another, the
references will automatically adjust based on their relative position. As an
illustration, when you have a formula in cell A1 that refers to cell B1 (=B1)
and you duplicate it to cell A2, the formula in A2 will automatically adjust
to refer to cell B2 (=B2).

Absolute Referencing
When you copy the formula to another cell, the absolute references remain
unchanged. There is a dollar sign ($) placed before the column letter and
the row number to denote them. As an illustration, $A$1 is considered an
absolute reference. When a formula with an absolute reference is copied
from one cell to another, the reference stays constant. As an example, when
you have =$A$1 + B1 in cell C1 and you copy it to C2, the formula in C2
will become =$A$1 + B2.

Mixed Referencing
The references in question have one part that remains relative, while the
other part is absolute. This feature is particularly handy when you need to
keep one part of the reference fixed while allowing the other part to be
flexible. There are two different types of mixed references:

When you copy a reference like $A1 across columns, the

column stays the same (A), but the row adjusts relative to the
new position.

When you copy a reference like A$1 across rows, the row will
always remain 1, but the column will change based on its new
position.
Here are some examples of mixed referencing:

$A1: When copied across columns, the column reference

remains the same, while the row reference changes.

A$1: When copied across rows, the row reference remains

constant while the column reference changes.
Application in Formulas
Here's a practical application for using these references:

Relative: =A1 + B1 (both references will change when copied)

Absolute: =$A$1 + $B$1 (both references remain constant
when copied)
Mixed: =$A1 + B$1 (only the specified part of the reference
remains constant)

How to change between different reference types

Efficient formula creation and management in Microsoft Excel relies
heavily on the ability to seamlessly switch between different reference
types, such as absolute, relative, and mixed.
Exploring Reference Types
 The relative reference (A1) will change when the formula is
copied to another cell.
The Absolute Reference ($A$1) remains constant regardless of
where the formula is copied.
Mixed Reference:
When using the $A1 notation, the column remains constant while the row
varies. When using the A$1 reference style, the column will vary while the
row remains constant.

How to Change Reference Types

Using the F4 Key:

Click on the cell that contains the formula you wish to edit.

Choose the cell reference in the formula that you wish to

modify.

Remember to press the F4 key to cycle through the various

reference types.
For this example, if you have the formula =A1+B1, you can click on A1 in
the formula bar and press F4.

For the first press, use the absolute reference =$A$1.

Second press: =A$1 (using a mixed reference)
Third press: =$A1 (mixed reference)
Fourth press: =A1 (using a relative reference)

Manually Editing the Formula

Click on the cell that contains the formula you wish to edit.

To modify the reference type, navigate to the formula bar and

manually add or delete the $ signs. Change A1 to $A$1 for
absolute reference.
Using Named Ranges:

To ensure consistent references, it is possible to create named

ranges that are always absolute.

Choose the cell or range of cells that you wish to name.

Head over to the Formulas tab and locate the Define Name
option.

Simply enter a name and click OK.

Instead of using the cell reference, you can utilize this name in
your formulas.

When duplicating a formula across cells:

Adjust cell references based on the position using relative

references.

Remember to use absolute references when you want to keep

certain cell references constant.

When you need one part of the reference to remain constant

while the other part adjusts, it is helpful to use mixed
references.
 CHAPTER EIGHT
Working with Excel Cells
How to understand Excel cells
The cell is the designated location for inputting data onto the worksheet.
The worksheet is populated with numerous rectangles that serve as cells.
On the contrary, the cell is formed by the convergence of a row and a
column. The columns are labeled with letters of the alphabet (A, B, C, etc.),
while the rows are labeled with numerical values (1, 2, 3, etc.).
When using Excel, you will input data or content into cells. Cells serve as
the fundamental units that make up a worksheet. To effectively work with
data in Excel, it is essential to have a solid understanding of cells and cell
content. This knowledge will enable you to perform calculations, analyze
information, and efficiently organize your data.
The structure of a worksheet consists of numerous rectangles known as
cells. A cell is where a row and a column intersect. Columns are labeled
with letters (A, B, C), and rows are labeled with numbers (1, 2, 3).
Each cell on the worksheet is labeled with its name or cell address, which is
conveniently displayed at the top left corner of the worksheet.
A group of cells is referred to as a cell range. A cell range is formed by
combining more than two cells. For instance, the cell range is typically
written in the following format: A1: A5 or B1: B5
To choose a range of cells

Simply click, hold, and drag the mouse to select the cells you
want to highlight.
Let go of the mouse and choose the desired cell range.

How to insert content in a cell

To input content, whether it be letters or numbers, simply follow these
steps:

Select the desired cell to begin typing.

Enter the content into the cell and press Enter. The content will
then be displayed in both the cell and the formula bar.

Note that you have the option to remove the content of the cell. Selecting
the content of the cell for deletion. To delete, simply press the Delete or
Backspace key.

How to delete a cell

To remove a cell from the worksheet:

Choose the cell you would like to delete.

Choose the Delete command from the Home tab on the
Ribbon and select the option that best fits your needs.
Once this is done, the cell will automatically move up on its
own.

How to copy and paste cell content

Follow the steps below:
 Choose the content in the cell that you would like to copy.
Access the Copy command located on the Home tab.
Alternatively, you can use the keyboard shortcut Ctrl+C to
perform this operation.
After choosing the desired location, simply click on the Paste
option located on the Home tab to transfer the content. It's
important to note that the copied cell will be easily identifiable
with a dashed box around it (you can also use ctrl+ V to paste).

How to drag and drop the cell

For a more efficient way to handle cell manipulation, you can use the
convenient drag-and-drop method to easily move your content between
cells. It's quite simple, all you have to do is:

Choose the cells you want to move.

Hover the mouse over the edges of the selected cells, then
release the mouse button to transfer their contents.
Simply click, hold, and effortlessly drag the cells to your
preferred location.
Let go of the mouse and the cells will be relocated to the
desired position.

How to use the fill handle in a cell

The fill handle in Excel is a useful tool for easily copying content from one
cell to multiple cells in a worksheet. The fill handle allows for efficient
copying and pasting of cell content to neighboring cells in the same row or
column.
When using the fill handle:

To duplicate the content of a cell, simply select the desired cell.

The fill handle will then appear as a small square in the bottom
right corner of the selected cell.
Simply click, hold, and drag the fill handle to duplicate the
desired cells.
Release the mouse to populate the chosen cell.
You can also use the fill handle to extend a series, whether it be numbers (1,
2, 3) or days of the week (Monday, Tuesday, Wednesday). Typically,
selecting multiple cells is necessary for the fill handle to function properly.
 CHAPTER NINE

Formatting Excel Cells

How to change the font
The process of changing the font in Microsoft Excel is simple and can be
applied to an entire worksheet, a specific cell, or a range of cells.
Here's a clear and concise step-by-step guide:
Change the Font for Selected Cells

First, you'll need to open your Excel workbook. Access the

Excel file in which you wish to modify the font.

Use the click and drag function to easily select the cells you
wish to modify. To change the font for an entire row or column,
simply click on the corresponding row number or column letter.

To access the Font Menu, navigate to the Home tab on the

Ribbon. Options for font type, size, and other formatting can be
found in the Font group.

Open the dropdown menu for the font type and pick the font
you prefer. Additionally, you have the option to modify the font
size, style (bold, italic), color, and other formatting elements
within the same group.

Change the Font for the Entire Worksheet

To select all the cells in the worksheet, you can either click on
the Select All button located at the top-left corner of the
worksheet or use the shortcut Ctrl + A.

To access the Font Menu, navigate to the Home tab on the

Ribbon.
 In the Font group, you'll find various options for font type, size,
and other formatting.

Open the dropdown menu for the font type and pick the font
you prefer. Additionally, you have the option to modify the font
size, style (bold, italic), color, and other formatting options
within the same group.

Changing the Default Font for New Workbooks

To begin, locate the File option in the top left corner. Choose
Options from the menu.

Navigate to the General category in the Excel Options dialog

box.

In the section on creating new workbooks, you have the option

to customize the font and font size to your liking. Remember to
click OK to save the changes.

Applying Font Changes to Headers or Specific Elements

To make changes, simply click on the headers or elements you

want to modify.

Navigate to the Home tab on the Ribbon.

Choose the font settings you prefer from the options in the Font
grou

How to change the font size and color

The process of changing the font size and color in Microsoft Excel is quite
simple. Here's a clear and concise step-by-step guide:

Adjusting the Font Size

 Click on the cell or highlight the range of cells where you want
to adjust the font size.

Navigate to the Home tab on the ribbon.

The Font group contains a box that displays a number,

indicating the current font size. Click the drop-down arrow
located next to this box.

Choose the font size you want from the available options. As
an alternative, you have the option to enter a specific number in
the box and then press Enter.

Changing Font Color

Click on the cell or highlight the range of cells where you want
to modify the font color.

Navigate to the Home tab on the ribbon.

Within the Font group, locate the icon featuring an "A" and a
color bar beneath it (this icon is known as the Font Color
button).

Click the drop-down arrow next to this icon.

A color palette will be displayed. Choose your desired color

from the palette. To access a wider range of color options,
simply click on "More Colors..." located at the bottom of the
palette. This will open a dialog box where you can select from
a variety of additional colors or even create your custom colors.

Extra Pointers
Shortcut Keys:
 To easily adjust the font size, you can utilize the keyboard
shortcuts Ctrl + Shift + > to increase it and Ctrl + Shift + < to
decrease it.

Format Cells dialog:

To modify the font size and color, simply right-click on the

selected cell(s) and select Format Cells. In the Format Cells
dialog, navigate to the Font tab to customize the font size,
color, and other font attributes.
Consistency:

If you want to maintain consistent formatting across multiple

cells, you might want to try using Excel's Cell Styles located in
the Home tab. This feature enables you to easily apply pre-
defined formatting styles.

How to apply background colors using the fill color

Microsoft Excel allows users to enhance their spreadsheets by adding
background colors to cells through the Fill Color feature.
Here is a step-by-step guide:

To apply the background color, start by selecting the cells you

want to modify.

There are several methods to access the Fill Color menu: The
location of the option you're looking for is in the "Home" tab
on the ribbon, specifically in the "Font" group. Find the paint
bucket icon labeled "Fill Color." Another option is to right-
click on the cells you want to format, select "Format Cells,"
and navigate to the "Fill" tab in the resulting dialog box.

When you click on the Fill Color icon or tab, a color palette
will appear. Simply click on any color from the palette to make
your selection. To access a broader selection of colors, simply
 click on "More Colors" if you don't find the one you're looking
for.

After choosing the color you want, simply click on it and Excel
will automatically apply it as the background for the selected
cells.

To remove the background color, you have two options. You

can select "No Fill" from the Fill Color menu, or you can click
the "Clear" button in the Format Cells dialog.

How to add borders to the cells

It's easy to add borders to cells in Microsoft Excel. Here is a clear and
concise step-by-step guide:

To add borders to specific cells, simply click and drag to select

them. In addition, you have the option to select an entire row or
column simply by clicking on the corresponding header.

After selecting the cells, navigate to the "Home" tab located on

the Excel ribbon.

To select a border style, navigate to the "Borders" dropdown

menu located in the "Font" section of the "Home" tab. To open
the menu, simply click on the arrow next to it.

You have the option to select different border styles, including

outline borders, inside borders, or outside borders, from the
dropdown menu. Additionally, you have the option to select a
specific border style from the "More Borders" menu.

Just select the border style you want and click on it. The
borders will then be applied to the cells you have selected.

To further customize the borders, simply click on "Borders"

and then select "More Borders." At your disposal are options
 to select the line style, color, and sides of the cells to which you
want to apply the border.

To get rid of borders in cells, you have two options. You can
either select the cells and choose "No Border" from the
borders menu, or you can choose "Borders" > "No Border" to
remove borders from all selected cells.

How to change the text alignment of your border

Unfortunately, it is not possible to directly modify the text alignment of a
cell border in Microsoft Excel. The alignment of the text within the cell is
not affected by cell borders, as they are purely visual elements. However,
you have the flexibility to adjust the alignment of the text within the cell,
regardless of the borders.
Here is a simple guide on aligning text within a cell in Excel:

Choose the cell or range of cells that you want to modify the
text alignment.

Head over to the "Home" tab located on the Excel ribbon.

Within the "Alignment" group, you will discover a variety of

alignment options.

Choose the alignment option that suits your needs:

➢ Left Align: The text can be aligned to the left within the
cell.

➢ Center Align: The text can be centered horizontally

within the cell using the Center Align option.

➢ Right Align: The text can be aligned to the right within

the cell.

➢ Justify: The text is stretched to fill the width of the cell,

except for the last line, which is aligned to the left.
 ➢ Top Align: The text can be aligned to the top of the cell.

➢ Middle Align: The text is centered vertically within the

cell.

➢ Bottom Align: The text can be aligned to the bottom of

the cell.
Select the alignment option that suits your preference, and the text within
the cell(s) will align accordingly. Keep in mind that modifying this setting
will only impact the text inside the cells, not the actual borders or
alignment.
How to change the columns, rows, and cells in a worksheet
If you want to modify columns, rows, and cells in Microsoft Excel, you can
easily do so by following these steps:
Choosing Columns, Rows, or Cells:

Click on the header letter of the column to select it, such as

"A", "B", "C", etc.

Click on the row number on the left side of the sheet to select a
row. The row numbers are labeled as "1", "2", "3", etc.

Click on the cell you want to change to select it.

Inserting or Deleting Columns, Rows, or Cells:

When you need to insert or delete columns, rows, or cells,

simply right-click on the column or row heading where you
want to make the change. From the context menu, select
"Insert" to add a new column or row. Another option is to
navigate to the "Home" tab on the ribbon, locate the "Insert"
button in the Cells group, and select either "Insert Sheet
Columns" or "Insert Sheet Rows".

Deleting a column or row is a simple process. Just right-click

on the column or row heading and select "Delete" from the
 context menu. In addition, you have the option to navigate to
the "Home" tab on the ribbon, locate the "Delete" button in the
Cells group, and select either "Delete Sheet Columns" or
"Delete Sheet Rows".

Adjusting Column or Row Sizes:

Hover your mouse over the right border of a column header

until the cursor changes to a double-headed arrow. Click and
drag to adjust the width of the column. Similarly, hover your
mouse over the bottom border of a row header until the cursor
changes to a double-headed arrow. Click and drag to adjust the
height of the row.

Editing Cell Content:

To edit a cell, simply double-click on it or select the cell and

start typing to replace the current content.

Moving Columns or Rows:

To move a column, simply select the entire column by clicking

on its header, and then drag it to the desired location.

To move a row, simply select the entire row by clicking on its

number, and then effortlessly drag it to the desired location.

How to change the column width

Adjusting the column width in Microsoft Excel is a straightforward process.
Here is a step-by-step guide:
Manual Adjustment:

Hover your cursor over the right border of the column header
you want to adjust. You will notice a change in your cursor as it
 transforms into a double-headed arrow.

Adjust the width of the column by simply clicking and

dragging the border to the left or right.

Using Ribbon Options:

Choose the column(s) that you would like to adjust the width
of.

Access the "Home" tab located on the Excel ribbon.

You can locate the "Format" dropdown menu in the "Cells"

group.

Go to the "Format" tab and choose "Column Width".

Specify the desired width in the dialog box that appears and
click "OK".

Keyboard Shortcut:

Choose the column(s) you wish to resize.

Hold down the "Alt" key on your keyboard.

Pressing "Alt" followed by "H", "O", and "C" will activate the
Home tab, then Format, and finally Column Width.

Simply enter the width you desire in the dialog box and press
"Enter".

How to change the height of the row

Here are the steps to change the height of a row in Microsoft Excel:
 Choose the row(s) that you wish to adjust the height of. To
select a single row in Excel, simply click on the row number
located on the left side of the window. If you want to select
multiple rows, just click and drag across the row numbers.

After selecting the row(s), simply right-click on any of the

chosen row numbers. A context menu will be opened.

Hover your mouse over the "Row Height" option in the

context menu. A new menu will appear on the right side.

Click on "Row Height". You can specify the height you want
for the row(s) by opening a dialog box.

Enter the height you want in the "Row height" field.

Click the "OK" button to apply the new height.

In addition, you have the option to manually adjust the row height by
dragging the boundary line between row numbers in the row header area.
When you move your mouse cursor over this boundary line, it will
transform into a double-headed arrow. Adjust the row height to your desired
size by simply clicking and dragging the boundary line up or down.

How to wrap text and merge cells

Microsoft Excel offers helpful formatting options that can enhance the
readability and comprehension of your spreadsheet. Wrapping text and
merging cells are two such features that can be particularly useful.
Here's a step-by-step guide:
Wrapping Text
Text wrapping is a useful feature that enables you to show lengthy text
entries in a cell without changing the cell's width.
Here is a step-by-step guide:

Simply click on the cell or click and drag to select multiple

cells.
Here is where you can find the formatting options.

Click on the button labeled "Wrap Text". The shape resembles

a rectangle with a symbol indicating a line break. By clicking
this button, you can activate text wrapping for the selected
cell(s).

To wrap text in Excel, you have the option to right-click on the

selected cell(s), select "Format Cells", navigate to the
"Alignment" tab, and then enable the "Wrap text" feature.

Merging Cells
Merging cells allows you to create a larger cell by merging multiple
adjacent cells. Here is a step-by-step guide on how to merge cells:

Simply click and drag to select the cells you wish to merge.
These cells need to be next to each other.

Go to the "Home" tab.

Next, select the "Merge & Center" button. This button

combines the chosen cells and aligns the text in the middle of
the merged cell.

To merge the cells without centering the text, you can click the
arrow next to the "Merge & Center" button and choose
between "Merge Across" or "Merge Cells" based on your
preference.
Note that when merging cells, only the content of the upper-left cell will
remain in the merged cell. Any content from other cells will be deleted. It is
important to exercise caution when using merged cells, particularly in large
spreadsheets or when sharing data with others. Merged cells can
occasionally lead to problems with sorting and other functions.
 CHAPTER TEN
Excel Formulas
Formulas are expressions that are used to calculate the value of a cell in a
spreadsheet. When it comes to addressing difficulties in mathematics and
engineering, as well as doing calculations involving numbers, Microsoft
Excel is an extremely strong and valuable option. In the blink of an eye,
you can instantly sum up or average a column of data thanks to this tool,
which makes fast calculations easier to do. In addition to that, you can
calculate several other computing functions, like a weighted average and
compound interest, amongst others. A formula can be entered into a cell to
do all of these things.
A limited number of modifications have been made to the formula bar in
Excel. Formulas such as CONCAT, IFS, MAXIFS, MINIFS, SWITCH, and
TEXTJOIN have been changed to reflect the changes.

List of Excel Basic Functions

The list of functions that are not only easy to use but also very beneficial is
provided below. These functions are crucial for your proficiency in Excel.

SUM
We are going to begin by introducing you to the first Excel function. In
addition, it is the one that is responsible for carrying out the fundamental
arithmetic operation.
To properly calculate the sum, your Excel formula has to have at least one
number that is linked to a cell or a range of cells. Just one example:
You can sum up the numbers in cells A2 through C6 by using the formula
=SUM(A2:C6).
Using the formula =SUM(A2, C6), the values in cells A2 and C6 are
equalized.
When you find yourself in a situation where you need to execute several
tasks, like as addition and division, you may do this by using a single
formula. For instance, you might add up the values in cells A2 through C6,
and then divide the total by 5.
= THE SUM OF (A2:C6)/5
Furthermore, you can sum using conditions. The SUMIF function may be
used to perform a sum with conditions:

You are required to provide the range of cells that are to be

validated against the criteria in the first parameter (A1:A4) at
this point.
Enter the criterion itself (C2) in the second argument for the
argument.
It is necessary to enter the cells to sum (B1:B4) in the last
argument.
The sum of the sums of A1:A4, C2, and B1:B4
Note that the simplest and quickest method for adding up a row or column
of numbers is to choose a cell that is right below the final value in the
column, which is the cell that is immediately after the numbers you wish to
add up, and then click the AutoSum button that is located on the Home tab,
inside the Formats group. The SUM formula will be automatically inserted
for you by Excel, and the values that correspond to it will be added
together.

AVERAGE
The average is the second function that we are going to review in this
section. The AVERAGE function in Excel does just what its name suggests,
which is to determine the average, often known as the arithmetic mean, of a
set of values.
Taking a closer look at the formula from the previous example
(=SUM(A2:C6)/5), what this does is that it adds up the values in cells A2
through C6, and then it divides the result by 3. This is what is known as the
average, which is the process of dividing the total by the frequency.
You need just enter the following formula into a cell to execute an average
calculation:
= AVERAGE (A2:C6)
Make use of the AVERAGEIF formula to compute the average cells
depending on the condition, where A2:C6 represents the range of criteria,
D3 represents the criterion, and E2:E6 represents the cells to average:
=AVERAGEIF(A2:C6, D3, E2:E6)

MAXIMUM & MINIMUM

Excel formulae known as MAX and MIN find the maximum and lowest
values in a collection of numbers, respectively, when applied to a set of
numbers. Formulas for maximum and minimum are as simple as the
following:

=MAX(A2:C6) – Determine the highest possible value from

cell A2 all the way to cell C6.
To get the least value from cell A2 to cell C6, use the formula
=MIN(A2:C6).

COUNT & COUNTA

Excel also has a function called COUNT & COUNTA, which is an essential
statistical tool. If you want information about the number of cells inside a
certain range that includes numeric data (such as numbers or dates), you do
not have to squander time manually counting them since the COUNT
function in Excel will do the task for you.
While the COUNT function only works with cells that include numbers, the
COUNTA function counts all cells that are not blank, regardless of whether
they contain numbers, dates, text, times, or errors. The COUNT function is
used to count just those cells that contain numbers.
For instance, you can use the following formula to get the number of cells
in column C that have numerical values:
(C: C) = COUNT
Use the following formula to count all the cells in column A that are not
empty:
= COUNTA(C and C)
In a nutshell, COUNT is only capable of processing numerical values, while
COUNTA generates the total number of cells in columns that are not blank,
which includes the text value that is shown in the column header.

IF
This seems to be the function that is used the most often in Excel. A basic
explanation of how an IF formula works is that it allows you to ask Excel to
check a certain condition and return one value or carry out one calculation
if the condition is satisfied and to return a different value or carry out a
different calculation if the condition is not satisfied. IF(logical_test,
[value_if_true], [value_if_false]) is an example of possible expressions.
This will allow us to determine whether or not the data that was entered is
legitimate.

TRIM
There are times when your Excel formulae that are proper return a large
number of errors. When this occurs, one of the first things to check is
whether or not there are any unnecessary spaces in the cells that are
referenced. It is necessary to shorten these unused passageways. Getting rid
of unneeded spaces in Excel may be accomplished in several different
methods, with the TRIM function being the most straightforward of these:
As an example, to eliminate any unnecessary spaces in column B, you may
write the formula that is shown below in cell B1, and then copy it along the
column: =TRIM(B1). As a result, all of the empty spaces in the cells will be
removed.

LEN
The LEN function is used to ascertain the total number of characters
contained inside a certain cell.
As an example, if you want to know the number of characters that are
included in cell C2, you may simply write the formula that is provided
below into another cell:
= LEN on C2
It is important to note that the LEN function in Excel counts every
character, including spaces.

AND & OR
Two of the most common logical functions for verifying numerous
requirements are the two methods listed below.

When all the requirements are satisfied AND yields TRUE;

otherwise, it returns FALSE.
If any of the conditions are satisfied OR returns TRUE;
otherwise, it returns FALSE.

CONCATENATE
When you want to merge the values from two or more cells into a single
cell, you may use this function to do so. To combine the numbers from cells
A3 and B3, for instance, all you need to do is insert the following formula
into a whole other cell:
= CONCATENATE (A3, B3)

TODAY & NOW

Using these functions, you can ensure that the date and time shown on your
worksheet are always accurate.
If you want to avoid having to manually update your worksheet daily, you
can use any of the following options to display the current date and time
whenever you view it:
 To update a cell with the current date, use the =TODAY()
function.
To enter the current date and time into a cell, use the =NOW()
function.

How to delete formulas and keep the calculated value

When you use the Delete key to erase a formula, the value that was
calculated along with that formula is also removed. On the other hand, you
may remove simply the formula while retaining the value that is determined
by the formula in the cell. To do this, you will need to complete the
following steps:

Choose all the cells that contain your formulae.

Copy the cells that you have chosen by pressing Ctrl + C.
Choose the selection with the right mouse button, and then pick
Paste Values > Values to paste the computed values into the
cells that were chosen. You can also use the shortcut for Paste
Special, which is Shift+F10 followed by V.

Other Excel Formulas

Following an overview of the fundamental formulae in Excel, we will now
proceed to examine the formulas that have been recently introduced to
Excel. As was said before, the following are the new formulas:

CONCAT
IFS
MAXIFS
MINIFS
SWITCH
TEXT JOIN
An examination of each of these formulae will be carried out in sequential
order, with each one being followed by an explanation of how to use it.

CONCAT FORMULA
The CONCAT formula joins together a list without using a delimiter to
separate the items. In other words, the CONCAT function is a built-in Excel
function that falls under the category of a String/Text Function. It gives you
the ability to combine two or more strings. It was developed to take the role
of the CONCATENATE function that was included in earlier versions of
Excel. An explanation of the formula for the CONCAT format is provided
below:
Using the CONCAT function, combine the first text with the second text,
and so on.
If you want to combine a range of cells or text, the CONCAT Formula in
Excel will do this task for you simply and efficiently. To create a single text,
it will simply join the words that you provide together. The following steps
need to be taken to make use of the CONCAT function:

A blank cell should be used to enter the CONCAT function:

Select the range of cells that you want to combine, for example,
and then use the CONCAT function. =CONCAT(A2:C4)
To apply the same formula to the other cells, drag the bottom
right corner downwards. This will do the same operation.
To finish the command, press the enter command.
IFS FORMULA
An extensive number of tests are executed by the Excel IFS function, which
then produces a value that corresponds to the initial TRUE result. To
evaluate numerous conditions without the need for many nested IF
statements, the IFS function may be used. The IFS allows for formulae that
are shorter and simpler to read. In a nutshell, the IFS functions do numerous
checks on the conditions and then return the result of the first TRUE
condition that they find.
The following is a summary of the formula: =IFS(first condition to check,
value to return, [succeeding conditions to check],...)
Instead of building Nested IF Formulas, we may utilize Excel's IFS
Formula if you have several logical conditions that need to be verified since
it is more efficient. It gives us the ability to provide many requirements that
need to be checked, and at that point, the IFS Formula will search for the
first condition that is met.
MAXIFS FORMULA
The MAXIFS function in Excel is responsible for returning the highest
possible numeric value that satisfies one or more criteria within a given
range of values. There are a variety of conditions that can be used with
MAXIFS, including dates, text, numbers, and other circumstances. If you
need to extract the maximum value while simultaneously filtering the data,
the MAXIFS Formula in Excel will be able to do this task for you.
Specifically, the formula is expressed as follows: = MAXIFS(cells that
contain the values, first set of cells to base the filtering on, filtering
condition of the first set of cells,...)
Take the following actions to make use of this function:

Simply type the MAXIFS function into an empty cell:

=MAXIFS( Input the MAXIFS specifications as follows: max
range
It is necessary to choose the cells that contain the sales data
from which you want to get the highest possible value. for
example, =MAXIFS(C3:D8)Make sure to choose the cells that
include the names of the salespeople: =MAXIFS(C3:D8,
C9:C12)
For the filtering criteria, type them in. This is an example:
=MAXIFS(C3:D8, C9:C12, "Maxwell")
Hit the enter key.

MINIFS FORMULA
In the same way, as the MINIFS formula does, this algorithm determines
the minimal value by analyzing the cells that meet the conditions. If you
want the MINIFS Formula to execute filtering while simultaneously
obtaining the minimal value, it will accomplish this task for you
effortlessly.
The following is a breakdown of the formula used:
(cells that contain the values, the first set of cells to base the filtering on, the
filtering condition of the first set of cells,...) =MINIFS(cells that contain the
values)
To make use of the MINIFS function, you must first supply the data from
which you want to get the MIN value, and then you must define one or
more criteria that constitute the filtering process. At the next level, we are
going to try to get the bare minimum of sales for Maxwell. Take the
following actions:

Enter the MINIFS function in a blank field: =MINIFS( Input

the MINIFS specifications as follows: min range
Determine which cells include the sales figures from which you
want to get the minimum amount, and then select those cells: A
=MINIFS(B9:C13, Make sure to choose the cells that include
the names of the salespeople:
For example, if B9:B13, and C9:C13, enter the filtering
criterion; the Maxwell filtering criteria is the one we will use
here. MINIFS(B9:B13, C9:C13, "Maxwell") is the expression
to use.

SWITCH FORMULA
After comparing a single value to a set of values, the SWITCH function
gives a result that is comparable to the value that was found to be the first
match. The function just compares many values and returns the first value
that is a match for some of the values.
Let's break down the formula in the following way:
Expression, value1, result1, [value2 / default, result2], and so on are all
examples of switches. This means that you should use the following syntax:
=SWITCH (value to check, value to match against, result to return,
[succeeding values to match or the default value if nothing gets
matched],........)
The following is a list of the stages involved in using the SWITCH
function:

A blank cell should be used to enter the SWITCH function: //

SWITCH (Provide the following SWITCH arguments:
Choose the cell that contains the rating that you wish to be
translated into the appropriate description before proceeding.
 SWITCH (G7, G8)
The first number in the rating table is where we will begin.
Whenever the value is 1, the description is considered to be
"Bad." =SWITCH(G7, 1, "Bad") =In the rating table, check the
second number that is shown. If the answer is 2, the description
will be "Average" = SWITCH(G7, 1, "Bad", 2, "Average")
Verify that the third value in the rating table is correct. On the
other hand, if the number is 3, the description will be "Great."
The expression "=SWITCH(G7, 1, "Bad", 2, "Average", 3,
"Great")"

TEXT JOIN FORMULA

You can join two or more strings together using the TEXTJOIN function in
Microsoft Excel. Each value can be separated by a delimiter before joining
the strings together. A list is concatenated using a delimiter that is given by
the user.
Let's break down the formula in the following way:
=TEXTJOIN(text1, in addition to delimiter and ignore_empty)
In other words, the formula =TEXTJOIN (the delimiter, disregard empty
cells when combining text, initial text/range to combine, etc.) is followed.
Following these steps will allow you to make advantage of the TEXTJOIN
function:

Simply type the TEXTJOIN function into an empty cell:

=TEXTJOIN( Enter the parameters for the TEXTJOIN
function: equals TEXTJOIN(", to ignore the empty cells while
putting them together, let us set the value to TRUE:
=TEXTJOIN(", TRUE, "), Make a selection of the cells that
you wish to join together and choose them there.
=TEXTJOIN("", "TRUE, C3:E8) as string.
To apply the same formula to the other cells, drag the bottom
right corner downwards. This will do the same operation. This
is the combined text that you now have.
 CHAPTER ELEVEN
Using Excel Formulas and Functions
How to insert a formula in Excel
Inserting a formula in Microsoft Excel is quite simple. Here is a detailed
guide to assist you:

Using the Formula Bar

Begin by opening Microsoft Excel and selecting the desired

cell for the formula.

To begin editing the cell directly, you can either click on the
formula bar (the bar above the spreadsheet where it says "fx")
or press F2.

Start the formula by typing the equals sign (=).

Provide the formula. As an illustration, to calculate the sum of

the values in cells A1 and B1, you can simply enter =A1+B1.

Hit Enter to finalize the formula. The result will be displayed

in the chosen cell.

Using Excel Functions

Choose the cell where you would like to input the formula.

Simply click on the formula bar or press F2.

Use the equals sign (=) to begin the formula.

 Provide a function. For instance, if you want to calculate the
sum of a range of cells, you can simply type =SUM(.

Specify the range of cells within the parentheses. For instance,

to sum cells from A1 to A10, you can use the formula
=SUM(A1:A10).

Hit Enter to finalize the formula.

Using the Insert Function Dialog Box.

Choose the cell where you would like to input the formula.

To open the Insert Function dialog box, simply click the fx

button on the formula bar.

Explore our wide range of functions by searching or selecting a

category.

Choose the function you want and then click OK.

Provide the necessary arguments in the Function Arguments

dialog box.

Click OK to insert the function into the selected cell.

Here are some example formulas:

Addition: =A1+B1
Subtraction: =A1-B1
Multiplication: =A1*B1
Division: =A1/B1
Sum of a Range: =SUM(A1:A10)
Average of a Range: =AVERAGE(A1:A10)
IF Statement: =IF(A1>10, "Greater", "Less or Equal")
Tips

Cell References: Use cell references in your formulas to

ensure that the results are automatically updated whenever the
data changes.

Absolute References: To maintain the reference when copying

the formula to other cells, simply use the $ symbol before the
column letter and row number (e.g., $A$1).

AutoFill: To copy the formula to adjacent cells, simply drag

the fill handle located at the bottom-right corner of the selected
cell.

How to insert a function in Excel

Adding a function in Microsoft Excel is quite simple. Here's a detailed
guide:

Option 1: Using the Function Insert Tool

Simply click on the cell where you would like the function's
result to be displayed.

To access the Excel Ribbon, navigate to the Formulas tab and

locate the Insert Function (fx) button. Additionally, you have
the option to click on the fx button located next to the formula
bar, directly in the cell.

A dialog box will appear allowing you to choose the function

you require: Search for the function by typing its name or a
description. Choose a category from the drop-down menu to
filter the list of functions.

Once you've chosen the function, a new dialog box will pop up,
allowing you to input the required arguments for the function.
Arguments can include cell references, numbers, or other
functions.
 Click the OK button once you have entered the arguments.
Once you've chosen the cell, the function will be inserted and
the result will be displayed for you to see.

Option 2: Entering the Function Manually

Click on the cell where you would like the function's result to
be displayed.

Provide the function. Begin by typing an equals sign (=). Start

by typing the function name. Excel provides helpful
suggestions for functions as you type. To use the SUM
function, simply enter =SUM(.

Provide the arguments: Remember to include the arguments for

the function within the parentheses. An example of this is when
you use the =SUM(A1:A10) formula to add up the values in
cells A1 through A10.

Press the Enter key: Pressing the Enter key will finalize the
function and show the result in the chosen cell.

Here's an example:
Suppose you need to calculate the sum of values in cells A1 through A10
using the SUM function:

Select the cell where you would like the result to be displayed,
such as B1.

To access the SUM function, navigate to the Formulas tab and

click on Insert Function.

Enter A1:A10 in the function arguments dialog box.

Click OK. The formula =SUM(A1:A10) will be displayed in

cell B1, and the sum of the values in A1 through A10 will be
 shown.
Alternatively, you can enter your text directly into cell B1:

Select cell B1.

Enter the formula =SUM(A1:A10) and hit Enter.

Using Excel formulas and functions

How to use the SUM function
The SUM function in Microsoft Excel is a simple yet effective method for
totaling a range of numbers. Here's a helpful guide on how to use it
effectively:
Basic Syntax
Here is the syntax for the SUM function:
The formula =SUM(number1, [number2], ...) calculates the sum of the
specified numbers.
number1, number2, ...: These numbers or ranges of numbers are the ones
you want to add together. The maximum number of arguments you can
include is 255.

Using the SUM Function

Click on the cell where you would like the result to be

displayed.

Input the function: Begin the function with =SUM(. Choose the
range of cells you want to sum. This can be done by clicking
and dragging your mouse over the cells or manually entering
the cell references. Make sure to close the parenthesis properly.

Use the Enter key to finish the function and show the sum.
Here are some examples:
To calculate the sum of the numbers in cells A1 through A5, follow these
steps:

The formula =SUM(A1:A5) calculates the sum of the values in

cells A1 to A5. The sum of the numbers in cells A1, A2, A3,
A4, and A5 is calculated.
To calculate the sum of the numbers in cells A1 through A5 and B1 through
B5, follow these steps:

The formula =SUM(A1:A5, B1:B5) calculates the sum of the

values in cells A1 to A5 and B1 to B5. This function calculates
the sum of all the numbers within the specified ranges.
Sum Individual Numbers and Ranges: To calculate the sum of the numbers
in cells A1 through A5, as well as the number 10 and the number in cell B1:
The formula =SUM(A1:A5, 10, B1) can be used to calculate the sum of the
values in cells A1 to A5, along with the value 10 and the value in cell B1.
The numbers in the range A1 to A5, the number 10, and the value in cell B1
are all added together.

How to use the AVERAGE function

The AVERAGE function in Microsoft Excel is utilized to calculate the
average (arithmetic mean) of a group of numbers. Here is a detailed guide
that will walk you through the process of using the AVERAGE function:
Using the AVERAGE Function

Navigate to the Excel file where you want to calculate the

average.

Choose the cell where you would like the average to be

displayed by clicking on it.

To use the AVERAGE function, simply enter "=AVERAGE("

into the desired cell.
 There are two options available: Click and drag to select the
range of cells you wish to average and enter the range of cells
manually. For instance, if you wish to calculate the average of
cells from A1 to A10, type A1:A10.
Here's an example of how your formula could be written:
=AVERAGE(A1:A10)

Close the parenthesis by typing ) to complete the formula.

Simply hit the Enter key to calculate and display the average in
the selected cell.
Here's an example:
Suppose you have a series of numbers in cells A1 through A5 and you need
to determine their average.

Input the numbers into cells A1 through A5:

➢ A1: 10
➢ A2: 20
➢ A3: 30
➢ A4: 40
➢ A5: 50

Choose the cell where you would like the average to be

displayed, such as B1.

Enter the formula =AVERAGE(A1:A5) in cell B1.

Press Enter and the average of the numbers in cells A1 through

A5 will be displayed in cell B1. Based on the information
provided, the answer is 30.
Extra Pointers
 The AVERAGE function is designed to only consider numeric
values, automatically excluding empty cells, as well as cells
containing text or logical values.

To average multiple ranges, simply separate them with

commas. As an illustration, the average of the numbers in both
ranges can be calculated using the formula
=AVERAGE(A1:A5, B1:B5).

If you'd rather not manually input the function, you have the
option to use the Function Library:

➢ Head to the Formulas tab on the Ribbon.

➢ Click on More Functions.
➢ Choose the Statistical option and click on AVERAGE.
➢ Enter the range of cells you want to average in the Function
Arguments dialog box.

Handling Errors

#DIV/0! Error: There seems to be an error with the

calculation. Check the formula and try again. An error message
is displayed when attempting to calculate the average of a
range that does not contain any numeric values. Make sure that
the range contains numerical values.

#VALUE! Error: There seems to be an error in the value

provided. Oops! It seems like there might be some invalid
arguments in the formula, which could be causing this error to
appear. Please make sure to carefully review the cell references
and confirm their accuracy.

How to use the MAX function

The MAX function in Microsoft Excel is used to find the largest number in
a set of values. Follow this step-by-step guide to effectively use the MAX
function:
Syntax
The MAX function is used to find the highest value among a set of
numbers.

number1 (required): The value, cell reference, or range you're

looking to find the maximum value for.

number2, ... (optional): You can also include any additional

numbers, cell references, or ranges to determine the maximum
value. It is possible to include up to 255 additional arguments.
Using the MAX Function

To begin, launch Microsoft Excel and locate the workbook you

wish to work with.

Simply click on the cell where you would like the MAX
function result to appear.

Input the function:

Using the Formula Bar:
➢ Enter =MAX( in the formula bar.
➢ To evaluate a range of cells, simply select the desired range. For
instance, if you wish to find the maximum value in the range
A1:A10, you can use the formula =MAX(A1:A10).
➢ Press Enter to complete the formula and show the result in the
selected cell.
Using the Function Dialog:
➢ Head to the Formulas tab located on the Ribbon.
➢ Click on the Insert Function (fx button).
➢ In the Insert Function dialog box, enter MAX in the Search for a
function box and click Go.
➢ Choose the maximum value from the list and then click OK.
➢ Enter the range of cells you want to evaluate in the Number1 box
in the Function Arguments dialog box. If necessary, you can include
 extra ranges or numbers in the Number2, Number3, etc., boxes.
➢ Click OK to finish the formula and show the result.
Here is an example:
Consider an example where there is a set of numbers in cells A1 through
A5:
The sequence of numbers is: 1 10 2 15 3 8 4 22 5 6
To determine the highest value within this range:

To get the desired result, simply click on cell B1 or any other

cell of your choice.
Input the formula =MAX(A1:A5).
Press Enter.
The value in cell B1 will be 22, which is the highest value in the range
A1:A5.

Using the MAX function with multiple ranges

Another option is to use the MAX function with multiple non-contiguous
ranges. As an illustration, the code to locate the highest value within the
ranges A1:A5 and B1:B5 is:
The formula =MAX(A1:A5, B1:B5) will return the maximum value
between the ranges A1:A5 and B1:B5.

Dealing with Text and Logical Values

The MAX function only considers numerical values in the

ranges you specify, disregarding any text or logical values.
When all arguments are non-numeric, such as text, the MAX
function will return 0.
Here are some observations:

Make sure to double-check the range you are evaluating to

avoid any errors that could cause the MAX function to return
an error.
 The MAX function only takes into account numeric values and
disregards empty cells.

How to use the MIN function

The MIN function in Microsoft Excel is used to identify the smallest
number within a given range of values. Here is a step-by-step guide on how
to use the MIN function:
The MIN function returns the smallest value among the given numbers.

number1, number2, ...: These are the values you want to find
the minimum value from. The maximum number of arguments
you can input is 255.
Using the MIN Function

Open your Excel workbook and make sure that the data you
wish to analyze is already present in your spreadsheet.

Choose the cell where you would like the result to appear.
Simply click on the cell where you want the minimum value to
be displayed.

Input the MIN function:

Using the formula bar:
➢ Use the MIN function to find the minimum value in a set of
numbers.
➢ Choose the specific range of cells you wish to include, such as
A1:A10.
➢ Close the parenthesis with a closing bracket.
➢ Press Enter.
➢ The formula will be written as =MIN(A1:A10).
Selecting ranges using the mouse:
➢ Use the MIN function to find the minimum value in a range.
➢ Use your mouse to click and drag or manually type the range of
cells you want to select.
 ➢ Close the parenthesis with a closing bracket.
➢ Press Enter.

The cell will show the smallest value within the range you've
selected.
Here are some examples:
Discovering the smallest value within a given range:
Assume that there are values in cells B2:B8. To determine the minimum
value, please input:
The formula used is MIN(B2:B8).
Discovering the smallest value within various ranges:
To find the minimum value across two different ranges, such as C2:C5 and
D2:D5, you can simply enter:
The formula used is MIN(C2:C5, D2:D5).
Discovering the smallest value within a given list of numbers:
You have the option to input numbers directly into the function.
The minimum value among 10, 20, 5, and 15 is calculated using the MIN
function.

How to use the TEXTJOIN function

The TEXTJOIN function in Microsoft Excel is a versatile tool for
combining text strings from different ranges or arrays, using a designated
delimiter to separate each value.
Syntax
The TEXTJOIN function allows you to combine multiple text strings into
one, using a specified delimiter and ignoring any empty cells. It is a useful
function for joining text values together.

delimiter: A string, such as a space or a comma, to be inserted

between each text value.
 The ignore_empty parameter is a boolean value (TRUE or
FALSE) that determines whether empty cells should be
ignored.

The items to join are text1, text2, and so on. You have the
option to specify multiple text items, whether they are cell
ranges or arrays.

Using TEXTJOIN

Start Microsoft Excel and open the worksheet where you would
like to utilize the TEXTJOIN function.

Simply click on the cell where you would like the combined
result to be displayed.

Input the formula:

➢ Start by entering =TEXTJOIN(.

➢ Make sure to input the delimiter as the first argument, surrounded

by double quotes. For instance, if you wish to separate the text
using a comma and a space, enter ", ".

➢ When entering the ignore_empty argument, simply type a comma

followed by either TRUE to ignore empty cells or FALSE to
include them.

➢ After typing another comma, select the range of cells you want to
join. Alternatively, you have the option to manually enter cell
references or use arrays.

How to use the LEN function

The LEN function in Microsoft Excel is a useful tool for determining the
length of a text string, including spaces, punctuation, and special characters.
To use the LEN function effectively, follow these steps:
Syntax
Here is the syntax for the LEN function:
The length of the text is determined by the LEN function.
This is the string or cell reference that you want to find the length of.

Using the LEN Function

Open Microsoft Excel on your computer.

Enter the text you would like to analyze in a cell. As an

illustration, input "Hello, World!" into cell A1.

Simply click on the cell where you would like the text length to
be shown.

Use the LEN function by typing it in the desired cell. As an

illustration:
The formula =LEN(A1) calculates the length of the text in cell A1.
Excel can easily calculate the number of characters in cell A1 using this
formula.

Simply press the Enter key. When a cell is selected in Excel,

the program will show the length of the text contained within
that cell. The result for "Hello, World!" will be 13.
Here are some examples:
Here are a few additional examples that demonstrate the functionality of the
LEN function:

Example 1: Simple Text If cell B2 contains the text "Excel

2024", the formula =LEN(B2) will return 10 as "Excel 2024"
has a total of 10 characters, including the space.

Example 2: Empty Cell In the case where cell C3 is empty, the

formula =LEN(C3) will return 0 since there are no characters
present in the cell.
 Example 3: Using LEN with other functions is also possible.
For instance, if you're looking to determine the length of the
resulting text after concatenation:
The length of the string "OpenAI GPT-4" can be calculated using the
formula LEN(CONCATENATE("OpenAI", " GPT-4")).
The result of CONCATENATE("OpenAI", " GPT-4") is "OpenAI GPT-4",
which will return 11.
Practical Applications

Trimmed Text Length: Sometimes, it is necessary to

determine the length of a string after removing any spaces at
the beginning or end. Use the TRIM function in conjunction
with LEN:
The formula used is =LEN(TRIM(D1)).
After removing any leading or trailing spaces, the length of the text in cell
D1 will be returned.

Data Validation: Use the LEN function in data validation to

ensure that text entries adhere to specific length requirements.

Conditional Formatting: Use conditional formatting with the

LEN function to highlight cells based on the length of the text
they contain.

How to use the COUNT function

The process of using the COUNT function in Microsoft Excel is very
simple. This function is designed to calculate the quantity of cells that
contain numerical values within a specified range.
Follow the steps below:

Choose a cell where you would like the result to be displayed.

Enter "=COUNT(" in that cell.

 Choose the specific cells you want to include in the count. You
have the option to specify a range of cells by using commas to
separate them, such as A1:A10. Alternatively, you can
manually select the cells by using your mouse.

Make sure to close the parentheses and then press Enter. Make
sure to use the formula "=COUNT(A1:A10)" in your
calculation.

The cell will now show the count of numeric values in the
selected range.
For instance, if you have numbers in cells A1 through A10 and you want to
determine the count of populated cells, you can use the formula
"=COUNT(A1:A10)".
If you're looking to count cells based on specific criteria, you may want to
consider using the COUNTIF or COUNTIFS functions instead. However,
for simple numeric counting, the COUNT function is a great choice.

How to use the TRIM function

The TRIM function in Microsoft Excel is a useful tool for eliminating
unnecessary spaces in text strings. It effectively removes leading and
trailing spaces, as well as condenses multiple spaces between words into a
single space. This function is incredibly valuable for tidying up text data
that may have been imported from other applications or entered manually.
Here is a detailed guide that will walk you through the process of using the
TRIM function:
Syntax
Here is the syntax for the TRIM function:
The TRIM function removes any leading or trailing spaces from a given
text.
This text string needs to have the extra spaces removed.
Here's an example:
Imagine you have the text "Hello World" in cell A1. If you want to clean
this text, you can use the TRIM function.

Choose the cell where you would like the cleaned text to be
displayed. For example, let's assume you prefer it to be in cell
B1.

To use the TRIM function, follow these steps: Start by selecting

cell B1 and entering the formula below:
The formula TRIM(A1) can be used to remove any leading or trailing
spaces from the text in cell A1.

Once you have typed the formula, simply press the Enter key.
The cleaned text, "Hello World", will now be displayed in Cell
B1.

Another Example
Imagine you have a list of names in column A, ranging from A1 to A5, and
they all contain unnecessary spaces:
1. John Smith 2. Jane Doe 3. Bob Johnson 4. Alice Brown 5. Charlie White
To tidy up this list:

Choose the initial cell for displaying the cleaned data. For
example, you can begin from cell B1.

To enter the TRIM function for the first cell, click on cell B1
and type the following formula:
The formula TRIM(A1) will remove any leading or trailing spaces from the
text in cell A1.

Once you have typed the formula, simply press the Enter key.
Cell B1 will display the text "John Smith" after it has been
cleaned.
 Use the fill handle (located at the bottom-right corner of cell
B1) and drag it down to cell B5. This will effectively apply the
TRIM function to the entire range.
Once this step is completed, column B will contain the names that have
been properly cleaned.
B - 1. John Smith 2. Jane Doe 3. Bob Johnson 4. Alice Brown 5. Charlie
White

Tips

The TRIM function solely eliminates the ASCII space

character (code 32) from the text. Non-breaking spaces (code
160) used in some web pages are not removed by this tool. To
remove non-breaking spaces, you can utilize the SUBSTITUTE
function along with TRIM.

Here's an example of how to remove non-breaking spaces:

The formula <code>=TRIM(SUBSTITUTE(A1, CHAR(160), " "))</code>
can be used to remove non-breaking spaces from the text in cell A1.
The TRIM function is a valuable tool for maintaining clean and concise
data, enhancing its usability, and facilitating analysis.

How to use the XLOOKUP function

The XLOOKUP function in Microsoft Excel is a highly effective tool for
searching and retrieving data in a table or range based on row criteria. This
version can be considered as an enhanced and adaptable alternative to the
previous VLOOKUP and HLOOKUP functions.
The XLOOKUP function allows you to search for a value in a range and
return a corresponding value from another range. It is a versatile and
powerful tool that can be used in various scenarios.

Arguments

lookup_value: The value you wish to search for.

 lookup_array: The array or range where you would like to
search for the lookup_value.

Return_array refers to the array or range that you wish to

retrieve a value from.

if_not_found (optional): The value to return in case the

lookup_value is not found. An error will be returned if
XLOOKUP is omitted.

optional parameter: match_mode.

0: Exact match (default).
-1: Match the exact item or the next smaller one.
1: Match the item exactly or choose the next larger option.
2: Use wildcard matching by using *, ?, or ~.

Optional search mode:

1: Search from first to last (default).
-1: Reverse the search order, starting from the most recent.
2: Binary search in ascending order.
-2: Implementing binary search in descending order.

How to use the SORT function

The SORT function in Microsoft Excel is a highly effective tool for
organizing and arranging data within a range or array. The data can be
sorted by one or more columns, either in ascending or descending order.
Presenting a comprehensive guide on effectively utilizing the SORT
function:
The SORT function allows you to arrange the elements in an array based on
specified criteria. You can specify the sort index, sort order, and whether to
sort by column.
Specifications:
 Array: The range or array you want to sort is: This argument is
the only one that is necessary.

Sort_index: (Optional) Specify the index of the row or column

to sort by. The default value is set to 1.

Sort_order: (Optional) Specify the desired order of sorting: 1

for ascending or -1 for descending. The default order is set to
ascending.

By_col: (Optional) Indicates the direction of sorting. By

default, the sorting is done by row, but if you set the value to
TRUE, the sorting will be done by column.

Here are some examples:

Example 1: Basic Sort
Arrange the data in a range in ascending order, using the values in the first
column as the basis for sorting.
The formula SORT(A1:B10) can be used to sort the data in cells A1 to B10.
The range A1:B10 can be sorted in ascending order based on the values in
the first column (A).
Example 2: Sorting by a Specific Column
Arrange a range of data in descending order based on the values in the
second column.
Sort the range A1:B10 in descending order based on the values in the
second column.
The range A1:B10 can be sorted in descending order based on the second
column (B).
Example 3: Sorting by Multiple Criteria
Arrange a range of data based on multiple criteria.
If you want to sort data in a specific way, you can achieve this by nesting
the SORT function.
The formula <code>SORT(SORT(A1:B10, 1, -1), 2, 1)</code> can be used
to sort the range A1:B10 in descending order based on the values in the first
column, and then in ascending order based on the values in the second
column.
The range A1:B10 is sorted by the second column (B) in ascending order
and then by the first column (A) in descending order.
Exploring Advanced Usage
Example 4: Sorting a Table
To sort the "SalesData" table by the "Revenue" column in descending
order, follow these steps:
Sort the SalesData in descending order based on the values in the third
column.
If "Revenue" is the third column in the table, then we can proceed with the
assumption.
Example 5: Sorting a Dynamic Array
Exploring the dynamic use of the SORT function in combination with other
functions. For instance, you can combine SORT with FILTER to arrange
the filtered results.
Use the SORT function to arrange the data in columns A and B. Apply a
filter to only include rows where the value in column B is greater than 100.
Sort the filtered data in descending order based on the values in column 2.
This code snippet filters a specific range of cells and sorts the filtered
results based on a condition. It focuses on the values in column B,
specifically those that are greater than 100. The filtered results are then
sorted in descending order based on the values in column B.

How to use the UNIQUE function

The UNIQUE function in Microsoft Excel is used to retrieve a list of
distinct values from a range or array. This function is incredibly handy
when you need to eliminate duplicates from a dataset.
The UNIQUE function allows you to remove duplicate values from an
array. You can choose whether to remove duplicates by column or by row,
and you can also specify whether you want to keep only the values that
appear exactly once.

array (required): The range or array from which to extract

distinct values.

by_col (optional): A boolean value that determines how values

are compared:
FALSE: By default, values are compared across rows.
TRUE: Compares values across columns.

exactly_once (optional): A boolean value that determines

whether to include values that occur only once:
FALSE (default): Returns all distinct values.
TRUE: Only values that appear exactly once are returned.
Here are some examples:

Here's a simple way to extract the unique names from a list of

names in cells A1:A10.
The formula UNIQUE(A1:A10) will return a list of unique values from the
range A1 to A10.

If your data is organized in columns and you need to extract

unique values across columns, you can use the by_col
parameter.
The formula UNIQUE(A1:D1, TRUE) is used to return a list of unique
values from the range A1 to D1.

To obtain the values that occur only once in the range A1:A10,
you can follow these steps:
The formula UNIQUE(A1:A10, FALSE, TRUE) can be used to obtain a list
of unique values from the range A1:A10.
 CHAPTER TWELVE

Working with Charts

How to insert a chart
Adding a chart in Microsoft Excel can greatly enhance the visual
representation of your data, facilitating comprehension and analysis.
Regardless of the purpose, whether it's for business, academics, or personal
projects, charts can offer valuable insights and improve the quality of your
presentations.
Follow this detailed guide to easily add and personalize charts in Excel:

Before creating a chart, it is important to make sure that your

data is properly organized. It is generally recommended to
present data in a table format, with clear labels for each
column.

To highlight the data range, simply click and drag your mouse
over the desired data that you want to include in the chart.
Make sure to include the column and row labels if they are
relevant to your data.

Make sure your data range is continuous, with no empty rows

or columns. Gaps in the data can cause problems when creating
charts.

To access the Insert Tab in Excel, simply click on the tab

labeled "Insert" located at the top of the program. This tab
offers a range of options for effortlessly incorporating various
elements into your spreadsheet, such as charts.

When selecting a chart type, you'll find a variety of options in

the Charts group. These include icons for Column, Line, Pie,
Bar, Area, Scatter, and more. Select the chart type that most
 accurately represents your data. Let's use a Column Chart for
this example.

➢ To create a column chart, simply click on the "Insert Column

Chart" button and choose the desired style from the options
available, such as clustered column or stacked column.
➢ To create a line chart, simply click on the "Insert Line Chart"
button and choose the style that suits your needs.
➢ To create a pie chart, simply click on the "Insert Pie Chart"
button and choose the style you prefer.

After you've inserted your chart, it will be visible on your

worksheet. Feel free to personalize it to better suit your
requirements.

➢ Chart Title: Edit the chart title by clicking on it. Consider using a
more descriptive title such as "Monthly Sales Data" to enhance
clarity.

➢ Axis Titles: To add axis titles to your chart, simply click on the
chart and navigate to the "Chart Tools" menu. In the "Design" or
"Format" tabs, there are options available to include axis titles.
Ensure that your axes are properly labeled, with the x-axis
representing "Months" and the y-axis representing "Sales".

➢ Legend: The legend is a useful tool for identifying the various

data series in your chart. Feel free to move, format, or remove it if
it's not necessary.

➢ Data Labels: Adding data labels can enhance the readability of

your chart by displaying the precise values for each data point. To
add data labels, simply click on your chart and navigate to the
"Chart Tools" menu.

Formatting the Chart

Excel provides a wide range of formatting options to enhance the visual
appeal of your chart.

To change the colors of your chart, simply select it and

navigate to the "Chart Tools" menu. From there, you can make
use of the "Format" tab to customize the colors to your liking.
There is a range of color schemes available for you to choose
from, or you can even create your custom scheme.

Make adjustments to the Chart Style by navigating to the

"Design" tab. From there, you have the option to choose from a
variety of chart styles that incorporate different formatting
choices.

Formatting the axes can enhance readability. To modify the

settings such as number format, scale, and more, simply select
the desired axis and access the "Format Axis" pane.

Gridlines can enhance the readability of your chart. To add or

remove gridlines, simply click on the chart and navigate to the
"Chart Tools" menu. From there, select "Add Chart Element"
and then choose "Gridlines".

Trendlines can be a valuable tool for recognizing patterns in

your data. To add a trendline, simply click on a data series in
your chart. Next, navigate to the "Chart Tools" menu and
choose "Add Chart Element" followed by "Trendline".

Adjusting the Size and Position of the Chart

To resize the chart, simply click on it to select it, and then drag
the corners to adjust its size. Remember to hold down the Shift
key while resizing to keep the aspect ratio consistent.

Move the chart by clicking and dragging it to a new location on

your worksheet. In addition, you have the option to easily move
 the chart to a different worksheet or even a separate Excel file
by using the cut-and-paste function.

Once you've finished customizing your chart, it's important to

save your workbook so that you don't lose any of your hard
work. To save your file, go to the "File" menu and select "Save
As". Then, choose where you want to save it and select the
desired file format.

Advanced Chart Customizations

Combining Charts: At times, it can be useful to merge various

chart types to effectively represent intricate data. As an
illustration, the combination of a column chart and a line chart.
Create a chart and then right-click on a data series to select
"Change Series Chart Type". Select the combination that best
fits your data.

Secondary Axes: If you have different data ranges, you can use
a secondary axis. To add the data series to the secondary axis,
simply right-click on it, go to "Format Data Series", and then
choose the option for the secondary axis.

Interactive Charts: Excel offers support for interactive

elements such as slicers and pivot charts, enhancing the
functionality of your charts. Pivot charts provide a dynamic
way to analyze and delve into extensive data sets. To create a
pivot chart, navigate to the "Insert" tab and select
"PivotChart". Then, simply follow the steps provided to set it
up.

Dynamic Charts: Charts can be made dynamic by linking

them to formulas or tables, ensuring that they update
automatically whenever the data changes. You can achieve this
by using named ranges and the OFFSET function in your data
source.
Tips for Creating Clear and Concise Charts

Select the right chart type: Choosing the appropriate chart

type is crucial as it relies on the nature of the data and the
specific message you want to convey. Line charts are perfect
for tracking trends over time. Pie charts are ideal for
representing parts of a whole. Bar or column charts are the
most effective for comparing quantities.

Keep It Simple: Avoid overwhelming your chart with

excessive information. Ensure that your main message is the
central focus.

Ensure Consistent Formatting: Use a uniform color scheme

and style to prevent any potential confusion among your
audience. Emphasize crucial data points by using contrasting
colors.

Label Clearly: Make sure to label everything clearly, including

axes, data series, and important points. The audience can easily
comprehend the data without any further explanation.

Consider your Audience: Take into account your audience

when creating your chart. Make sure to adjust the complexity
and style of the chart to match the level of expertise of your
audience. Make the content more accessible to a wide range of
readers, while providing additional information for those with
advanced knowledge.

Exploring Different Chart Types and Their Applications

Column Chart: Perfect for comparing data across different

categories. Take, for instance, the comparison of monthly sales
figures.
 Line Chart: Ideal for illustrating trends over some time. For
instance, keeping tabs on stock prices or monitoring
temperature fluctuations.

Pie Chart: A helpful tool for illustrating the distribution of a

whole. As an illustration, let's consider market share
distribution.

Bar Chart: A bar chart is similar to column charts, but it uses

horizontal bars instead. Provides a helpful way to compare
quantities across different categories.

Area Chart: An area chart is similar to a line chart, but it

includes filled areas below the lines. Great for displaying data
trends over some time.

Scatter Plot: A scatter plot is a great way to visually represent

the relationship between two variables. As an illustration, let's
consider the relationship between age and income.

Dealing with Chart Problems

Ensure Correct Data Range: To ensure your chart appears

accurate, verify the data range selected. Make sure it contains
all the necessary information.

Empty Cells: The presence of empty cells can lead to various

problems. Fill in or remove any empty cells that are not
necessary.

Overlapping Data: If data points overlap, consider adjusting

the chart type or axis scale to enhance visibility.

Unreadable Axis Labels: Consider rotating or adjusting the

axis labels if they appear too crowded or difficult to read.
How to add a title to a chart in Excel
It's easy to add a title to a chart in Excel. Here's a step-by-step guide:

Simply click on the chart you want to add a title to. Activating
the Chart Tools on the Ribbon is necessary.

Click on the Chart Elements button, which is located next to

the top-right corner of the chart. Check the Chart Title box in
the drop-down menu. Adding a default title to your chart is a
useful feature.

Click on the title text that appears on the chart. Enter the title
you would like to use. In the Home tab, you have various
options to format the text according to your preferences.
Another approach:

Simply click on the chart that you would like to add a title to.

Go to the Chart Tools Design tab (or simply the Design tab in
certain versions) on the Ribbon. Click on the option labeled
"Add Chart Element" located in the Chart Layouts group.

Choose "Chart Title" from the drop-down menu. Decide on

the placement of the title: either above the chart or as a
centered overlay.

Select the title text that is displayed on the chart. Then enter
your preferred title and format it accordingly.

How to add a title to a chart in Excel

Switching the chart type in Excel is a simple and easy process. Here is a
detailed guide to assist you:

Access your Excel workbook and locate the specific worksheet

that houses the chart you wish to modify.
 Select the chart by clicking on it. Activating the Chart Tools in
the Ribbon is necessary.

Go to the Ribbon at the top of the screen after selecting the

chart.

To access the "Chart Design" tab, simply click on the

designated tab in Excel, which may also be labeled as the
"Design" tab depending on your software version.

To change the chart type, go to the "Chart Design" tab and

locate the "Change Chart Type" button. Opening the Change
Chart Type dialog box is a necessary step.

On the left side of the dialog box, you'll find a list of various
chart types. Choose the chart type category you'd like to switch
to, such as Column, Line, Pie, Bar, and more.

There are various styles available for the selected chart type on
the right side. Select your preferred style by clicking on it.

After choosing the desired chart type and style, simply click the
"OK" button located at the bottom of the dialog box. Your
worksheet's chart will automatically update to the new type you
selected.

Once you've switched the chart type, there are additional ways
to personalize it to your liking. For instance, you can modify
the colors, fine-tune the legend, or even include data labels.

Use the various options found in the "Chart Design" and

"Format" tabs to make any necessary additional adjustments.

Here is a more specific example to better illustrate the process:

Suppose you have a column chart displaying sales data and you
wish to convert it into a line chart.
 Select the column chart by clicking on it.

Head over to the "Chart Design" tab located in the Ribbon.

Click on the option labeled "Change Chart Type."

Click on "Line" in the Change Chart Type dialog box.

On the right side, you can select a line chart style, such as Line
with Markers.

Click "OK" to apply the change. The chart has been converted
from a column chart to a line chart.

How to change chart style

The chart style in Excel can be modified to enhance the visual appeal and
effectiveness of your data visualization. Follow these simple steps to easily
change the chart style in Excel:
Method 1: Using the Chart Styles Gallery

Click on the chart you wish to modify. Activating the Chart

Tools on the ribbon is necessary.

After selecting the chart, the "Chart Tools" will appear at the
top of the Excel window. Navigate to the "Design" tab located
under "Chart Tools."

Under the "Design" tab, there is a group labeled "Chart

Styles."

Choose the style that suits your preferences from the gallery. To
access additional style options, simply click on the drop-down
arrow located at the right end of the gallery.

Method 2: Manual Customization of Chart Elements

 Make sure to click on the chart to select it.

Customize various elements of the chart by using the "Design"

and "Format" tabs found under "Chart Tools".

Design Tab:

Chart Layouts: Select from a variety of pre-designed layouts

for your chart.

Add Chart Elements: Easily customize your chart by adding

or removing elements such as titles, labels, and legends.

Quick Layout: Select from a range of pre-designed layout

options.

Modify Colors: Adjust the color scheme of your chart.

Format Tabs:

Shape Styles: Customize the appearance of the shapes in your

chart.

WordArt Styles: Add variety to your chart text with different

text styles.

Rearrange: Make adjustments to the arrangement of elements

in the chart.

Current Selection: Refine the formatting of selected chart

elements.

Method 3: Right-Click Menu

Make sure to click on the chart to select it.

 To format the chart, simply right-click on the chart area or the
specific chart element you wish to modify.

Select "Format Chart Area" or "Format [specific element]"

(e.g., Format Data Series, Format Legend, etc.) from the
context menu.

Use the formatting pane on the right to make precise

customizations.

Changing chart layout in Excel

The visual appeal and clarity of data presentation can be greatly enhanced
by changing the layout of a chart in Excel. Here's a simple guide that will
walk you through the process of changing the chart layout:

Open the workbook in Excel that contains the chart you wish to
modify.

Click on the chart you would like to modify. Activating the

Chart Tools in the Ribbon is necessary.

Once you've chosen the chart, you'll notice that the Ribbon
displays additional tabs: "Chart Design" and "Format".

Go to the "Chart Design" tab in the Ribbon.

Locate the "Quick Layout" group in the "Chart Design" tab.

Click on the "Quick Layout" button. Here is a collection of
pre-designed layouts for you to browse through.

Choose the layout that suits your needs by clicking on it from

the gallery. The chart will be updated immediately to reflect the
selected layout.

Customizing the Chart Layout

Customize Chart Elements:
 In the "Chart Design" tab, you have the option to include or
exclude various chart elements, such as titles, labels, legends,
and gridlines.

To add or remove chart elements, simply click on the "Add

Chart Element" button and select the desired element. Then,
make any necessary adjustments.

Change Chart Style:

In the "Chart Design" tab, you have the option to modify the
chart style by choosing a different style from the "Chart
Styles" group. Explore the styles by hovering over them to get
a sneak peek, and simply click to apply your desired style.

Modify Data Labels:

Click on the chart and then select the "+" button (Chart
Elements) that appears next to the chart to add or customize
data labels.

Make sure to select the "Data Labels" option and then click on
the arrow next to it. This will allow you to access additional
options for positioning and formatting.

Organize and structure chart elements:

Click on any chart element, like the legend, axis titles, or data
series, to select it.

To open the Format pane and make adjustments to colors,

styles, and other properties, simply right-click the selected
element and choose "Format [Element]".

Modify the chart type:

 If you're looking to use a different type of chart, simply head
over to the "Chart Design" tab. From there, click on "Change
Chart Type" and you'll be presented with a range of chart
types to choose from.

Guidelines for Creating Charts Design

Simplify: Avoid clutter by refraining from overloading the

chart with excessive elements.

Consistent Colors: Ensure that the colors used are visually

pleasing and effectively distinguish between different data
series.

Clear Labels: Ensure all axes, titles, and data labels are clear
and readable.

Use Legends Wisely: Consider including a legend only if it

enhances comprehension and provides valuable information for
understanding the chart.

How to move a chart

It's easy to move a chart in Excel. Here's a step-by-step guide:
Option 1: Drag and Drop

Simply click on the chart to make your selection. There will be

a border around the chart to indicate its selection.

Hover your cursor over the edge of the chart. A four-sided

arrow will appear when the cursor is moved.

To move the chart, simply click and hold the left mouse button,
and then drag it to the desired location on the worksheet.

After positioning the chart where you want it, simply release
the mouse button.
Option 2: Using Cut and Paste

Simply click on the chart to make your selection.

You can cut the chart. To perform the action, press Ctrl + X on
your keyboard. To move the chart, simply right-click on it and
choose the Cut option.
To paste the chart, simply click on the cell where you would
like the top-left corner of the chart to be placed. To paste, use
the keyboard shortcut Ctrl + V.
To complete the action, simply right-click and choose the Paste
option.

Option 3: Using the Chart Tools Ribbon

Simply click on the chart to make your selection.

Locate the Chart Tools tab at the top of the screen (you may
need to choose the Format tab under Chart Tools).

Use the Move Chart button located in the Location group. The
Move Chart Dialog allows you to easily select whether you
want to move the chart to a new sheet or an existing sheet, and
then specify the desired location.

How to resize a chart on your worksheet in Excel

Adjusting the size of a chart in Excel is a simple and easy task. Here's a
step-by-step guide:
Using the Mouse

Click on the chart you want to adjust the size of. Activating the
chart will cause sizing handles to appear around it.

Place your cursor over one of the corner handles until it

transforms into a diagonal double arrow, then drag it. Resize
 the chart by clicking and dragging it. Position your cursor over
one of the side handles (top, bottom, left, or right) until it
transforms into a horizontal or vertical double arrow. Resize the
chart by clicking and dragging in either the width or height
dimension.

Using the Ribbon

Remember to click on the chart to select it.

To access the formatting options for the chart, select the chart
and navigate to the "Format" tab located under "Chart Tools"
in the Ribbon.

In the "Size" group, you have the option to input precise values
for the width and height of the chart. Precise resizing is
enabled.

Input the desired height in the designated "Height" box and the
width in the corresponding "Width" box.

Using the Context Menu

Open the context menu by right-clicking on the chart.

Choose "Format Chart Area" from the context menu.

In the Format Chart Area pane, navigate to the "Size &

Properties" section.

Then provide the dimensions for the chart.

Shortcut Keys for Efficiency

While Excel does not offer dedicated keyboard shortcuts for resizing charts,
you can still utilize keyboard shortcuts to navigate and efficiently use the
Ribbon. As an illustration:
Remember to press Alt to activate the Ribbon shortcuts.

Use the appropriate keys to easily navigate to the "Format" tab

and then to the "Size" group.
 CHAPTER THIRTEEN

Data
How to import data text file
Importing data from a text file into Excel is a simple and easy process. Here
is a detailed guide with clear instructions:

Start up Microsoft Excel on your computer.

Begin by opening a new or existing workbook. You have the

option to create a new workbook or open an existing one where
you would like to import the data.

Ensure that your text file is properly organized, with your data
separated by delimiters such as commas, tabs, or semicolons.
The text file is structured so that each line corresponds to a row
in Excel, with the elements on each line separated by a chosen
delimiter representing columns.

To locate and select the "Data" tab in Excel, navigate to the

ribbon at the top of the window.

Choose the option "From Text/CSV". Find the "Get Data" or

"From Text/CSV" option in the "Data" tab. Go ahead and click
on it.

A file explorer window will appear. Find the location on your

computer where the text file is saved, and choose it. Next, click
on the "Import" button.

Excel will prompt you with the "Text Import Wizard" to

assist you in importing your data. Select the appropriate file
type if it is not detected automatically, and then click "Next".
 Choose the delimiter that separates your data in the text file.
Excel provides a helpful preview of how the data will be split
based on your selection. Ensure that the preview appears
accurate before proceeding to the next step.

When working with Excel, you can specify the data format for
each column. This allows you to customize how your data is
displayed and organized. There are various options available
for you to choose from, including General, Text, Date, and
more. Please make your selections and click "Finish".

Determine the location in your Excel worksheet where you

would like the imported data to be placed. You have the option
to place it in a new worksheet or an existing one. Please click
"OK" to import the data.

After importing the data, carefully examine it to ensure

accuracy and correctness. If necessary, adjustments can be
made such as formatting cells and adjusting column widths.

Remember to save your workbook once you've imported and

reviewed the data to preserve the changes you've made.

How to sort and filter

Sorting and filtering in Excel are crucial for effectively organizing and
analyzing data. Here's a guide on how to do both:
Data Sorting:

Click on any cell within the range you wish to sort.

Head over to the "Data" tab located on the ribbon.

Click on "Sort A to Z" to arrange in ascending order (from

smallest to largest).
 Click on the "Sort Z to A" option to arrange the items in
descending order, from largest to smallest.

Additionally, you have the option to click on "Sort" which will

open the Sort dialog box. This will provide you with more
advanced sorting options, including the ability to sort by
multiple columns.

Click "OK" to apply the sorting.

Data Filtering:

Click on any cell within the range you want to filter.

To access the Filter Option, navigate to the "Data" tab located

on the ribbon.

Apply the Filter by clicking on the "Filter" button. Small drop-

down arrows will appear in the header row of each column.

Simply click on the drop-down arrow in the column you wish

to filter.

Select the criteria you wish to filter by. You have the option to
choose specific values, arrange them in a desired order, or
apply personalized filters.

After selecting your filter criteria, simply click "OK" or

"Apply" to apply the filter.

To clear a filter, simply click on the filter icon in the column

header and choose the option "Clear Filter."
Keep in mind that by combining sorting and filtering, you can achieve more
precise results. For example, you can sort your data alphabetically within a
specific range that you have filtered. Excel's sorting and filtering
capabilities are incredibly useful for efficiently managing and analyzing
data.
How to use auto filter
The auto filter feature in Excel enables you to efficiently filter data
according to specific criteria. Here is a guide on how to use it:

Select any cell within your dataset. By specifying the data

range, Excel will be able to accurately apply the filter.

To enable AutoFilter, navigate to the "Data" tab located on the

Excel ribbon. The "Filter" button can be found in the "Sort &
Filter" group. Go ahead and click on it. In addition, you have
the option to use the keyboard shortcut Ctrl + Shift + L to
easily toggle the auto filter.

After enabling the filter, small dropdown arrows will appear in

the header row of each column. To filter the column, simply
click on the arrow.

By clicking on the dropdown arrow, a list of distinct values in

that column will be displayed. There are two options for
filtering: selecting specific values or using the search box to
find what you need.

Once you have chosen the criteria for filtering, simply click
"OK" or "Apply" to implement the filter. Excel can hide rows
that do not meet your specified criteria, resulting in only the
rows that meet the criteria being displayed.

To clear the filter and display all rows again, simply go back to
the column header where you applied the filter. Click the
dropdown arrow and select "Clear Filter From [Column
Name]".

To disable the auto filter, you can go back to the "Data" tab and
click on the "Filter" button once more, or you can use the
keyboard shortcut Ctrl + Shift + L.
How to use an advanced filter
Excel's advanced filtering feature enables users to effortlessly apply
intricate criteria for data filtering in a range or table. Here is a detailed
guide on how to use advanced filtering:

Ensure that your data is properly organized in a tabular format,

complete with clear column headers. Each column must
represent a different attribute or field.

Create a criteria range to specify the conditions for filtering.

You have the option to place this in either the same worksheet
or a separate one. Create a layout in this range that mirrors your
data, with each column representing a field to filter and each
row representing a different criterion. Ensure that the criteria
range has identical headers to your data range.

Begin the Advanced Filter process by selecting the desired data

range to be filtered. Next, navigate to the "Data" tab located on
the Excel ribbon.

To access the Advanced Filter, simply click on the "Advanced"

button located in the "Sort & Filter" group. Opening the
Advanced Filter dialog box is a necessary step. The Advanced
Filter dialog box is divided into three main sections: "Action,"
"List Range," and "Criteria Range."

Excel will automatically detect the range of your data in the

"List Range" field. If it doesn't, you have the option to
manually input the range. Choose the range in the "Criteria
Range" field that corresponds to your filtering criteria.

Decide whether you prefer to filter the data in its current

location or if you would rather copy the filtered results to a
different location. When copying, make sure to indicate the
range where you want the filtered data to be placed.
 Click OK to apply the advanced filter. After you've specified
the criteria, Excel will filter the data accordingly.

Once the filter has been applied, take the time to carefully
review the filtered data to ensure that it aligns with your
expectations. If you're not happy with the results, feel free to
modify your criteria and apply the filter again as necessary.

How to validate data

Ensuring data accuracy and consistency, validating data in Excel involves
checking if the entered data meets specific criteria or rules. Here is a step-
by-step guide:

To apply data validation, start by selecting the cells where you

want to apply it.

To access the "Data" tab in Excel, simply click on it in the

ribbon located at the top of the window.

To access "Data Validation", navigate to the Data Tools group

and click on it.

The Data Validation dialog box offers a range of criteria that

can be selected from the drop-down menu in the "Allow" box.
As an illustration:

➢ Only whole numbers are allowed.

➢ Decimal numbers are supported.
➢ List: Provides the option to input a list of acceptable values.
➢ Date: Only accepts date values.
➢ Time: Only accepts time values.
➢ Specify the desired length for text entries by setting a minimum
and/or maximum limit.
➢ Custom: Provides the flexibility to define validation rules using a
formula of your choice.
 Once you have chosen the validation criteria, adjust the settings
to meet your specific needs. For instance, if you select "List",
you have the option to input the acceptable values in the
"Source" field. When selecting the "Whole Number" option,
you can define both a minimum and maximum value.

Users have the option to include an input message to provide

instructions or guidance when selecting the validated cell. This
is not required.

Users can be notified if they enter data that doesn't meet the
validation criteria. This notification can include a warning
message and/or a style, such as a stop sign.

Click OK after configuring the validation settings to apply the

validation rules to the selected cells.

How to remove duplicates

Eliminating duplicates in Excel is a frequent task that can easily be
accomplished using the "Remove Duplicates" feature. Here is a detailed
guide:

First, you need to open your Excel spreadsheet. Open Microsoft

Excel and locate the spreadsheet that contains the data you
wish to remove duplicates from.

Simply click and drag to select the range of cells that contain
the data you wish to work with. Ensure that you include all
columns and rows where duplicates may be present.

Located at the top of the Excel window are a variety of tabs.

Navigate to the "Data" tab.

In the "Data" tab, there is a button called "Remove

Duplicates." Go ahead and click on it.
A dialog box will pop up displaying a comprehensive list of
columns within your chosen data range. All columns will be
selected by default. Deselect any columns that you do not want
Excel to include duplicates from. To ensure that Excel takes
duplicates into account throughout the selected range, it is
recommended to keep all columns selected.

After selecting the columns, simply click the "OK" button to

confirm and remove any duplicates. After selecting the data
range, Excel will analyze it and eliminate any duplicate rows.
You will receive a confirmation message indicating the number
of duplicate values that have been removed.

Once you've removed any duplicates, it's important to carefully

go through your data and double-check that the correct rows
have been deleted. Occasionally, Excel may fail to delete rows
if the entire row does not match exactly. It is important to
thoroughly review your data for accuracy.

Remember to save your Excel file after making any desired

changes to preserve your work.
 CHAPTER FOURTEEN

Tips and Tricks in Excel

It is a strong tool for data analysis, administration, and visualization, and
Microsoft Excel is one such tool. The ability to learn keyboard shortcuts
may help you save time and optimize your workflow, allowing you to use it
more effectively. The following is a comprehensive list of Excel shortcuts,
organized according to the functions they perform.

List of available Excel shortcuts

To create a new worksheet, press the Ctrl key and the N key
simultaneously.
Ctrl + O will open a worksheet that is already open.
Your current worksheet can be saved by pressing Ctrl and S
simultaneously.
For the Save As dialog box, use the F12 key.
Start the Print menu by pressing Ctrl and P.
Start the Find dialog box by pressing Ctrl and F.
Start the Replace dialog box by pressing Ctrl and H.
Undo the most recent action by pressing Ctrl and Z.
Pressing Ctrl and Y will allow you to redo the previous action.
Copy the cells that are chosen by pressing Ctrl and C.
Ctrl + X will cut the cells that are chosen.
To paste the cells that have been copied or sliced, use the Ctrl
and V keys simultaneously.
Select all of the cells in the worksheet by pressing Ctrl and A.
Applying or removing bold formatting may be done by using
Ctrl and B.
You can apply or delete italic formatting by pressing Ctrl and I.
Apply or delete the underlining formatting by pressing Ctrl and
U.
Show or hide the ribbon by pressing Ctrl and F1.
 The formula bar can be expanded or collapsed by pressing Ctrl
+ Shift + U.
You can go on to the next worksheet by pressing Ctrl and Tab
simultaneously.
Use the shortcuts Ctrl + Shift + Tab to go to the previous
worksheet.

Navigation Shortcuts

You can move one cell up, down, left, or right by using the
arrow keys.
To move one cell to the right, use the tab key.
Tab and Shift together will move one cell to the left.
To go to the edge of the current data area, use the Ctrl key and
the arrow key simultaneously.
Move to the beginning of the row to reach your home.
The shortcut for moving to the beginning of the worksheet is
Ctrl + Home.
Move to the last cell in the worksheet that contains data by
pressing the Ctrl key and the End key simultaneously.
To go up and down one screen, use the up and page down
function.
To move one screen to the left, use the Alt key and then the
Page Up button.
Pressing Alt and Page Down will move you to the right by one
screen.
Entering F5 will open the Go To dialog box.
The Go-To dialog box can be opened by pressing Ctrl and G
simultaneously.
Use the shortcut Ctrl + F6 to go to the next workbook window.
Switching to the previous workbook window may be
accomplished by pressing Ctrl, Shift, and F6 simultaneously.

Data Entry Shortcuts

Enter - Finish entering data into the cell, and then go to the next
option.
 Shift + Enter allows you to finish entering data into the cell
and move up in the selection.
Complete the cell input and then go to the right in the selection
by using the tab key.
To finish entering data into a cell and move to the left in the
selection, press the Shift key and the Tab key simultaneously.
Fill the chosen cells with the current entry by pressing Ctrl and
Enter.
Begin a new line in the same cell by pressing Alt and Enter.
Enter the current date by pressing Ctrl plus;
Insert a hyperlink by pressing Ctrl and K.
Ctrl + Shift + "+" is the shortcut for adding a new cell, row, or
column.
To delete the cell, row, or column that is now selected, use the
Ctrl key and the "-" key simultaneously.

How to use Ideas

Take advantage of Ideas as a source of inspiration if you are seeking
recommendations on how to show your Excel data most effectively. To
accomplish this;

Simply click on any part of the table.

Navigate to the Home menu, and then choose Ideas.
Any graph that is appropriate for displaying your data will
appear in the suggestions bar; then, click the insert button.

How to remove blanks from a worksheet

When you want to get rid of a large number of empty cells in a data
collection, you can do so by following the steps below:

The whole list of data should be highlighted.

Navigate to the Find and Select option from the Home menu.
Use the "Go to special" button.
Click on Blank, and then hit the OK button. This blank cell will
be highlighted on the next page, which is located here.
 Right-click on any of the empty cells, and then pick the Delete
option from the context menu.
When you pick the Shift cell left from the Delete menu, the
blank cell will be removed from the list.

How to get rid of duplicate data

If you have a list of data in Excel and you want to get rid of the duplicates,
the following steps should be taken:

Make the data more visible.

Choose the option to remove duplicates from the Data tab.
The selections that appear will be determined by the one that
you desire, and then you will click the OK button.
The modification is implemented in this section of the
worksheet.

How to transpose on your worksheet

You can change the rows and columns on your table via the use of
transposing, which enables you to do this.

To copy the table, right-click on it, and then pick the copy
option.
To paste special, right-click on the new spot, and then pick the
Paste Special option.
After making your selection, click the OK button.
The modifications will be implemented at this point.

How to add text to columns

By following the procedures that are provided below, you will be able to
copy sentences from a separate source and paste them into your column.

Transfer the information into a spreadsheet in Excel.

Navigate to the Data menu and choose Text to Column.
When you are in the pop-up window, pick the Comma option,
check the preview that is located in the bottom portion of the
pop-up window, and then click the Finish button.
 It is here on this page that the modification has been
implemented.

How to insert a screenshot into your workbook

To include pictures from other applications into an Excel worksheet.

Use the Insert tab to choose Screenshot, and then click on it.
Make your selection from the available picture by using the
Screenshot option.
In this section, the picture that you have chosen will be
presented on the Excel spreadsheet.

How to insert multiple rows

You can save time by entering three to four rows at once in your worksheet
rather than putting rows one at a time. This allows you to do more in less
time. Adding many rows at once, follow the steps below:

Right-click on any number of rows that you wish to choose.

Adding additional rows is as simple as clicking the insert

button.

It is important to note that the procedures described above may

be used when adding numerous columns to a spreadsheet.

How to Create People Graph

An example of a graph that can be made using a simple table with two
columns is called a people's graph. It is possible to do this by going to the
Insert tab and selecting the Transform col data into a nice image option.
This will result in the display of a default people graph.
You should move to choose your data when the default graph makes its
appearance. Click on the Create button once you have highlighted your data
in the simple table with two columns.

How to highlight text and numbers

To display just numerical values on a worksheet, all the data in the table
should be selected.

Access the Special menu by using the F5 key, which will bring
up a window.
Choose Constants, then check the box next to Numbers, and
then click the OK button.
Select the fill color that you want to use for the numbers that
have been highlighted.
All of the data in the table should be selected.

Highlighting formula cells

To draw attention to or choose the cells that contain formulas:

Press the Ctrl key and the g key on the table that includes all of
the cells.
At this same moment, a window will appear; choose Special
from the menu.
In the subsequent box that appears, choose Formula, and then
hit the OK button.
Specifically, the cell that contains formulae will be highlighted
here on this page.

Excel Tips and Tricks

Excel tips to optimize your use
Take a look at the following suggestions that can assist you in making more
effective and efficient use of Excel:

Use format painter

Excel's "format painter" tool enables you to swiftly apply a format or
group of formats to several cells at the same time while saving time. When
you want particular cells to include information that is italicized as well as
color-coded, this allows you to do so. Once you have selected the cell that
has the formatting that you want to replicate, enter the main menu and
choose "Format Painter." This will allow you to use the format painter.
After that, all you need to do is choose the cell in which you want to paste
the formatting. The icon will remain active until you deselect it if you
double-click on it, which is the case for numerous cells.

Choose to look at the whole spreadsheet

There is a possibility that you may be required to make modifications to a
complete spreadsheet. Pressing the "Ctrl" and "A" keys simultaneously will
select every cell. Instead of manually scrolling through the spreadsheet
using the mouse on your computer, you will be able to pick the full
spreadsheet using this feature.

Import the data

You may need to import the data rather than copy and paste it if you are
dealing with a more complicated collection of information. This is
something that may be accomplished in Excel by first choosing the "Data"
tab, and then selecting the option to get data from external sources. After
that, you can collect the data from your external source and import it into
your sheet by following the directions that appear on your screen
throughout the process.

Copy and paste in several different cells

If you want to copy and paste the same data into many cells, you have the
option of selecting the cells into which you wish to paste the data and then
adding the information that you want to copy into the cell that you have
selected most recently. Following that, you may copy and paste the data
from the previous cell by pressing the "Ctrl" and "Enter" keys
simultaneously. Then, it will fill all of the cells that you have chosen.
 View all of the formulas
You may be employed in a job that requires you to often share your
spreadsheets with other people. If you are given a spreadsheet and you are
interested in learning which formulae were used by the person who created
it, you should go to the "Formulas" tab. Once you have done that, you can
examine the formulae that other people have used by clicking the "Show
formulas" button.

Freeze rows and columns

The more information you enter into some spreadsheets, the more difficult
it may become to navigate such spreadsheets. You may find it helpful to
freeze certain rows and columns on your spreadsheet to locate the
information you want more expediently. Once you have located the row or
column on the spreadsheet that you want to freeze, go to the next step.
Following that, go to the "View" tab and choose the "Freeze panes" option
from the drop-down menu that appears.

Recreate the same patterns

Use Excel to swiftly repeat a pattern by entering the first few numbers of
the pattern into the appropriate row or column in separate cells. This will
allow Excel to reproduce the pattern more quickly. Following that, choose
the little square that is located at the bottom right of the last cell. To pick the
cells in which you wish to reproduce the pattern, drag that square to the
desired location. When you open Excel, the cells will immediately be filled
with the appropriate figure.

Keep the rows and columns hidden

You can decide to hide certain columns that you are not presently working
on while you are working on a spreadsheet to make more room for your
work. To hide rows or columns, pick the header of the row or column you
want to freeze, and then go to the "format" option from the main menu.
This will ensure that the row or column is hidden. Once you have made
your selection, you will have the choice to pick "hide columns" or "hide &
unhide" before selecting "hide columns."

Copy and paste items from one spreadsheet to another

You may be required to replicate the same information or computation
across numerous spreadsheets, particularly if your employment requires you
to do so. This may be accomplished by opening both worksheets, hitting the
"Ctrl" key on your keyboard, and clicking on the tab of the worksheet that
contains the information that you want to copy it from. The next step is to
hit the "F2" key, and then the "Enter" key after that.

Manage line breaks

Excel can be used to work with both letters and numbers; however, writing
phrases, sentences, or even whole paragraphs might take up more space
than the cell of the spreadsheet allows for. This can be avoided by using line
breaks and wrapping your content to make it simpler for the reader to
comprehend what you have written. To begin, go to the "home" tab, then
choose the cell that contains the text that you like to wrap, and finally, click
on the "wrap text" button.

Use paste special

Using the "paste special" option makes it simple to convert decimal values
into percentages and vice versa. When you have an empty cell, type "100"
into it. Then, duplicate that cell and pick the cells that you want to reformat.
The next step is to pick "divide" after clicking "paste special."

Use flash fill

Excel can identify certain patterns in formatting and automatically repeat
them thanks to a feature called Flash Fill. For instance, if you are inputting
a sequence of phone numbers and you want the numbers to have
parenthesis around the area code and a dash between the third and fourth
number, Excel will automatically reproduce the pattern after the second
time you go through the process of entering it. By clicking on the "flash
fill" button that is located under the "data" tab, you may activate the "flash
fill" function if it does not already exist.

Change the text into columns

When inputting names or other multi-word data into Excel, using the "text
to columns" option will help you save time. In situations when you want
each word to display in its cell, but you are copying and pasting a big data
collection, you should pick the data. The next step is to pick "text to
columns" from the "data" menu, and then make any necessary adjustments
to the formatting.

Transpose with a special paste

You can encounter a scenario in which you first believe that you required
certain data to be shown in rows, but later learn that you want columns.
Transferring data from rows to columns or vice versa may be accomplished
in a short amount of time by simply copying the data that you want to
move. After that, choose "paste special" make sure the "transpose box"
option is checked, and then click through to "OK."

Incorporate visuals
For better illustrating your data, you may include pictures in your
spreadsheet. To do this, choose the cell or element with which you would
want your graphic to be shown. The next step is to right-click and choose
"series." After that, you may choose the image or texture fill effect, and
then you can insert the graphic or photo that you have saved onto the sheet
that you want to use.

Save charts as templates

To illustrate your data, Excel provides you with a variety of graphs and
charts that can be customized. If you make any modifications, they will not
be saved automatically for use in subsequent spreadsheets. Right-clicking
on the completed chart, selecting "save as a template," and saving the file
as a CRTX file are the steps you need to do to preserve templates of a
custom spreadsheet that you have created if you are aware that you may use
it again in the future.

Manage cells across many sheets

If you have numerous workbook sheets from which you need to extract
information, there is a formula that you can use to identify the data in a
short amount of time. Make sure that the relevant information is provided in
the same cell on each page of each worksheet. This will ensure that the
worksheets function correctly. To get started, open a new worksheet. The
formula that you need should be entered into the cell exactly where you
wish to view the computation. Using the formula, for instance, you may add
the information from the cell that you have chosen to the worksheet that
you have also selected. This will allow you to add many cells across several
worksheets.= total This is a worksheet with the following name: [name of
worksheet]'! [cell number]

Hide your worksheet

You may be working on a spreadsheet that has many worksheets. You can
make it simpler to go around by hiding the worksheets that you are not
currently working on by right-clicking on the tab of the worksheet that you
want to hide and choosing "hide." This will make it easier for you to move
around. The sheet or sheets that you wish to see again can be accessed by
going to the "view" tab from the main menu, selecting "unhide," and then
selecting the sheet that you want to see again.

Use Pivot Tables

Through the use of pivot tables, you can explore and analyze certain
portions of a massive data collection. First, choose all of the columns and
rows that you want the pivot table to analyze. This will allow you to create
a pivot table. Next, pick "pivot table" from the "insert" menu when you
have navigated to it. After making the necessary adjustments, you can then
use the table to identify patterns or trends in the data you have.

Visualize data using conditional formatting

The use of conditional formatting may facilitate the visualization of your
data by drawing attention to certain values or by making it simpler to
discover specific cells. One example of how it can be used is to color-code
the highest numbers on a chart so that they are easier to locate and observe.
Use it by selecting the "conditional formatting" icon from the home tab,
and then adjusting the settings and preferences that you want to use.

Create a drop-down menu

When you are working on a spreadsheet, you may save time by creating a
drop-down menu that will allow you to fill certain rows or columns more
quickly. You can create a drop-down menu by first selecting the "data" tab,
and then clicking "data validation." This will allow you to highlight the
cell, row, or column that you want to include in the drop-down menu. After
that, choose the "list" option, click the "Allow" button, and then enter your
list item selections under the "source" heading.

Add a screenshot
You can simply include screenshots in your spreadsheet by selecting the
"insert" option and then selecting "screenshot." Next, you will be able to
choose the picture that you want to upload. Once you have added your
picture, you will be able to format it inside your sheet.

Prevent shifting cells

When you copy and paste a formula from another location into your
spreadsheet, the new cell becomes the reference point for the formula rather
than the cell that was used with the formula in the first place. You can
prevent the reference from moving into a new cell by putting a "$" sign
before the information that you want to maintain in the parent cell. This will
prevent the reference from moving. You will then be able to copy and paste
it.

Make use of the shortcut keys

Whenever you are working on a spreadsheet, Excel provides you with a
variety of built-in shortcuts that may help you save time. For the majority of
these shortcuts, you will need to press the "Ctrl" key, then a number. If you
wish to hide a row, for instance, you may do so by selecting it and
simultaneously pressing the "Ctrl" and "9" buttons on your keyboard.

Add without using formulas

The sum of several cells can be shown in a straightforward manner without
the need to input a sum formula. By holding down the "Ctrl" key while
selecting the first cell you wish to add, you may then choose any other cells
that you want to add altogether. To get the sum of those cells, look at the
status bar at the bottom of the page.

Highlight any duplicate values

It is possible that highlighting duplicate values in your sheet would be
helpful when you are dealing with a large amount of data. This can be
accomplished by first choosing the range of values that you want to
highlight, then selecting "styles" from the home tab, and then selecting the
"highlight cells rule" option from the drop-down menu that appears. The
next step is to choose the "duplicate values" checkbox.
 CHAPTER FIFTEEN

Troubleshooting issues
Excel not responding, freezing, or crashing
Update Excel and Windows:

Check to see that this version of Excel and Windows is the

most recent. The majority of the time, updates contain patches
for problems that might be causing computer crashes

Disable Add-Ins:

Access the Add-Ins by going to File > Options > Add-Ins

Once you have selected COM Add-ins in the Manage box,

click the Go button

Delete all of the add-ins and then click the OK button. Do a

restart of Excel and check to see whether the issue still exists

Repair Office:

Navigate to the Control Panel, then choose Programs, and

finally select Programs and Features.

Click the Change button after selecting your own Microsoft

Office installation.

Following the on-screen instructions, choose Repair.

Make sure there are no applications that are incompatible with Excel:

Excel may become incompatible with other programs. To

determine if Excel works more smoothly, you might try
 temporarily removing antivirus software or shutting other apps
completely.

Clear conditional formatting rules:

Excel might become more sluggish if it has an excessive

amount of or a sophisticated amount of conditional formatting.

By navigating to Home > Conditional Formatting > Clear

Rules, you can eliminate any formatting that is not required.

Modify the default printer associated with Excel:

Excel may become unresponsive sometimes owing to problems

with the printer. Make the default printer something
straightforward, such as "Microsoft XPS Document Writer."
Rename the default printer.

Start Excel in Safe Mode:

By pressing the Ctrl, Alt, and Shift keys simultaneously when

beginning Excel, you can start Excel in Safe Mode. Using this
method will open Excel in Safe Mode, which may help
determine whether or not a certain add-in or extension is the
source of the problem.

How to Solve Formula Errors

The errors include the following:
#DIV/0!:

This error happens when a formula attempts to divide by zero

with the result being zero.

Make sure that the denominator is not zero. This is the solution.
To manage this, you may use the IFERROR function, such as
=IFERROR(A1/B1, "Error: Division by Zero").
#NAME?:

This error displays itself when Excel is unable to detect text

included inside a calculation.

Verify that the names of the functions or the range references

do not include any mistakes. Check that every listed range is
present.

#VALUE!:

This error occurs when the incorrect type of argument or

operand is utilized during the execution of the program.

The solution is to make sure that all the cells that are referred to
have the typical data types. Whenever it is required, you may
convert text to numbers by using methods such as VALUE.

#REF!:

To solve the problem, modify the formula such that it refers to

a valid range.

Verify that the formula is not referring to any rows or columns

that have been removed from the table.

#N/A:

The error code #N/A describes a situation in which a function

or formula does not have access to a certain value.

To handle data that is not accessible, the solution is to use

IFNA or IFERROR. For example, you can use
=IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found").

How to Solve Data Formatting Issues

Numbers that are saved as text:

Choose the cells that include cell numbers that are stored as
text.

There is a possibility that an exclamation mark may appear;

choose Convert to Number by clicking on it.

Alternatively, you can convert by using the VALUE function,

such as by using =VALUE(A1).

Dates that are not considered to be dates:

Make sure that the dates are formatted in a way that Excel can
understand (for example, MM/DD/YYYY).

You can convert text to dates by using the DATEVALUE

function, such as by using =DATEVALUE("12/31/2023").

Data Formats That Are Not Consistent:

To standardize the forms of the data, use the Text to Columns

tool. Navigate to Data > Text to Columns, choose Delimited,
and then follow the on-screen instructions.

Apply consistent number formats by going to the Home menu

and selecting the Number group.

Incorrect Data Type:

Convert data types by using functions such as TEXT, VALUE,

DATE, or TIME, depending on the format that is needed.

Applying Conditional Formatting Correctly:

 To correctly use conditional formatting, you must first make
sure that the rules are applied to the appropriate range. Editing
or removing rules that aren't essential may be done by going to
Home > Conditional Formatting > Manage Rules.

Solving Printing Problems in Excel

Incorrect settings, difficulties with the printer, or formatting issues are some
of the potential causes of printing troubles with Excel. Other potential
causes include formatting issues. To resolve these concerns, here is how to
do it:
Taking the Following Steps to Fix Printing Issues:
Verify the Printer Settings:

Navigate to the File menu and click Print. Ensure that the
appropriate printer is chosen.

Using the Devices and Printers section of the Control Panel

(Windows) or the Printers & Scanners section (Mac), you
should check the current condition of the printer.

Modify the configuration of the page:

Navigate to the Page Layout menu and examine the options,

which include the margins, size, and orientation of the page.

You can examine how the document will appear when it is

printed by using the Print Preview feature. Adapt the
parameters to suit your needs.

Set Print Area:

Set the print area by highlighting the range of cells that you
wish to output.

Select Page Layout > Print Area > Set Print Area from the
menu bar.
Scaling Options:

If you need to fit the sheet into one page, use the scaling
options that are available in the File > Print menu (for
example, fitting the sheet onto one page).

Check for Hidden Rows and Columns:

First, check to see if there are any hidden rows or columns that
might be creating problems with the printing process. Go to the
Home menu, then choose Format, and then select Hide &
Unhide.

Update Printer Drivers:

Ensure that your printer drivers are up to date by following the

steps outlined in the sixth command.

Clear Print Spooler:

If the print jobs are blocked, clear the print spooler. The Print
Spooler service should be stopped, files should be deleted from
the C:\Windows\System32\spool\PRINTERS folder, and then
the service should be restarted.

Finding Solutions to Errors in Excel's Data Validation

Data validation errors may occur if the data that is being entered does not
satisfy the requirements that have been established. An explanation of how
to diagnose and fix these problems is as follows:
Steps to Take to Fix Errors in Data Validation:
Verify the Rules for Validating Data:
 Choose the cell or range that contains the problem.

To examine the settings, go to the Data > Data Validation

menu option. Check that the requirements are implemented
appropriately (for example, using full numbers, dates, and
lists).

Clear Data Validation Rules:

To get rid of validation, go to Data > Data Validation and then

click the Clear All button.

Input Message and Error Alerts:

If you are using the Data Validation dialog box, check that the
Input Message and Error Alert tabs are set up to show useful
messages.

Accurate Data Entry:

After entering the data into the cells, check to see whether it
satisfies the requirements that have been established by the data
validation rules.

Make Use of Consistent Data Types:

Make sure that the data types are consistent (for example, if the
validation rule demands dates, make sure that all of the data
entries are in date format).

Solving File Corruption in Excel

The loss of data and the inability to access it may be caused by corrupted
files. To recover and avoid file corruption, the following measures should
be taken:
Methods to Fix the Problem of File Corruption:
Open and Repair:

To open and repair, choose File > Open from the menu bar.

After selecting the file that is corrupted, click the arrow that is
located next to Open, and then choose Open and Repair.

To fix the file, you will need to follow the directions.

Recover from Autosave/Backup:

Determine if there are any autosaved versions by clicking on

File > Info > Manage Workbook. This will allow you to
recover from any backups or autosaved versions.

Investigate if there are any backup files or prior versions that

can be restored.

Save the file in a different format:

There are occasions when saving the file in a different format

(for example, saving it from.xlsx to.xls) might assist in the
recovery of data.

Copy Data to a New Workbook:

To transfer data to a new workbook, you should first open a

new Excel workbook and then attempt to copy the data from
the damaged file to the new workbook. However, you should
not copy the full sheet.
Prevent future corruption:

Be sure to save your work regularly and make backups. This

will help you avoid future corruption.

While you are working on Excel files, you should avoid

unexpected power outages or shutdowns.

Ensure that your Excel and OS software are always up to date.

If you are experiencing difficulty accessing certain tools inside Excel, the
following actions and suggestions will assist you in identifying them:

Use the Search Bar on the Ribbon

There is a search option that is incorporated into the Ribbon of Excel,
which enables you to locate commands and tools in a short amount of time.

Select the search bar located at the very top of the Ribbon
(which is often labeled with the phrase "Tell me what you
want to do" or an icon that looks like a magnifying glass).

To find the tool or feature you are searching for, type its name
into the search bar.

From the list of search results, choose the tool that you want to
use.
Explore Ribbon Tabs
Excel's Ribbon is organized into a variety of tabs that include various tools.
Make sure you are familiar with these tabs to discover the tools:

Home: The Home screen includes tools for basic formatting,

clipboard, and editing.
Insert: Insert is a set of tools that allows you to insert objects
such as tables, charts, shapes, and more.
Page Layout: The page layout includes choices for sheet
layout, page configuration, and themes.
Formulas: Functions, specified names, and auditing of
formulas are components of formulas.
Data: Data tools, data connectivity, and data analysis.
Review: Spelling, comments, and protection are all subject to
review.
View: Workbook views, window settings, and macros are all
included under "View."

Customize the Ribbon

If you use certain tools regularly, you can change the Ribbon to make them
more accessible:

To customize the Ribbon, right-click on it and select the

"Customize the Ribbon" option.

Within the "Excel Options" dialog box, you can create new
tabs or groups, as well as add the tools that you want to these
individualized tabs or groups.

Open the Quick Access Toolbar

The Quick Access Toolbar is a toolbar that can be customized to allow
rapid access to commands that are often used.

To access the Quick Access Toolbar, click the arrow that is

located on the right side of the toolbar.
 To add tools to the toolbar, choose the "More Commands"
option.

Use the Help Function

If you are still unable to locate a tool, the Help function might be of great
assistance to you:

To access the question mark symbol located in the upper right

corner of the Excel window, use the F1 key or click on the icon.

Enter your inquiry into the search box and then go through the
available assistance topics.

Contextual Tabs
The visibility of various tools is contingent upon the selection of certain
items. Just one example:

Table Tools: When a table is selected, the Table Tools menu

item will appear.
Chart Tools: While you are selecting a chart, the Chart Tools
menu will appear.
Drawing Tools: When you pick a shape, the Drawing Tools
interface will appear.

Changing the Customization Settings

You can restore the Ribbon and Quick Access Toolbar to their original
configurations if these modifications are creating confusion:

Navigate to the File menu and choose Options.

Choose either the Quick Access Toolbar or the Customize

Ribbon option.

Select the "Reset" button from the menu.

 CONCLUSION
Excel formulas and functions are the foundation of spreadsheet skills. They
enable users to carry out a wide variety of activities, ranging from basic
arithmetic to intricate data analysis. These tools are vital in both
professional and personal settings because of their capacity to considerably
improve productivity, accuracy, and ability to comprehend data.
Understanding and successfully employing these tools may significantly
boost these aspects.
In its most rudimentary form, Excel offers users a wide range of
fundamental formulae that make it possible for them to perform
fundamental arithmetic operations such as addition, subtraction,
multiplication, and division. The foundation upon which more complex
analyses are constructed is comprised of these activities. For instance, the
use of formulae such as =SUM(A1:A10) enables the speedy summing of
values within a certain range, while the utilization of =AVERAGE(B1:B10)
enables the calculation of the mean of a dataset, which provides
instantaneous insights into the trends of the data.
Excel's vast library of functions caters to more particular demands than just
basic arithmetic. These functions are grouped into statistical, logical, text,
date and time, lookup and reference, and financial functions. Excel's library
of functions is enormous. Certain analytical needs are addressed by each
group. A more in-depth understanding of the distribution and variability of
the data may be gained by the use of statistical functions such as
=MEDIAN(C1:C10) and =STDEV.P(D1:D10), helping to facilitate
thorough data analysis. Logical functions such as =IF(E1>100, "High",
"Low") make it possible to make conditional decisions, which simplifies the
process of classifying and interpreting data depending on certain criteria.
The manipulation and management of text data are essential for the
organization and presentation of information. Text functions such as
=CONCATENATE(F1, " ", G1) and =LEFT(H1, 3) are examples of such
functions. Functions that deal with dates and times, such as =TODAY() and
=DATEDIF(I1, I2, "Y"), simplify the process of processing date-related
computations, which is vital for time-sensitive analysis. The use of lookup
and reference functions, such as =VLOOKUP(J1, K1:L10, 2, FALSE) and
=INDEX(M1:M10, MATCH(N1, O1:O10, 0)), makes it easier to retrieve
certain data points from big datasets, which in turn improves the
effectiveness of data management.
When it comes to financial planning and analysis, financial functions such
as =PMT(interest_rate, number_of_periods, present_value) are helpful
since they provide rapid calculations for loan payments, investment growth,
and other financial measures. These features are especially helpful for
professionals working in accounting, finance, and other similar disciplines,
where accurate financial analysis is one of the most important aspects of
their work.
Excel formulas and functions are not the only things that need to be
mastered; comprehending error management and troubleshooting is critical.
If they are not handled, common mistakes such as #DIV/0!, #VALUE!, and
#N/A might cause disruptions to analysis. It is possible to control these
faults with the use of functions such as =IFERROR(P1/Q1, "Error"), which
ensures that spreadsheets continue to work properly and reliably even when
problems occur.
Additionally, Excel's power and versatility may be dramatically increased
by combining numerous functions in nested formulae or array formulas.
This capability can be found in Excel. An example of this would be the use
of the =SUMPRODUCT() function in conjunction with logical conditions
to carry out sophisticated computations using numerous criteria. Array
formulas allow for powerful operations to be performed concurrently on
several sets of data.