© Copyright 2025 - All rights reserved.

This document is intended to provide accurate and reliable information regarding the topic and issue addressed.

From a Statement of Principles accepted and equally approved by a Committee of the American Bar Association and by a Committee of Editors and Associations.

It is not legal to reproduce, duplicate or transmit any part of this document in any way whether by electronic means or in printed form. All rights reserved.

The information provided in this document is declared to be true and consistent, as any liability, in terms of carelessness or otherwise, arising from any use or abuse of any policy, process or instruction contained within is the sole and total responsibility of the recipient reader . Under no circumstances will the publisher be subjected to any legal responsibility or liability for any repairs, damages or monetary losses resulting from the information contained herein, directly or indirectly.

The respective authors own all copyrights not held by the publisher.

The information contained herein is offered for informational purposes only and as such is universal. The presentation of the information is without contract or any type of guarantee.

All trademarks and brands in this book are for clarification purposes only and are the property of their owners, who are not affiliated with this document.

TABLE OF CONTENTES

TABLE OF CONTENTES

INTRODUCTION

CHAPTER 1: WHAT IS EXCEL? NOTIONS AND HISTORY

The history of Excel

The purpose of Microsoft Excel

Where you can download it from

CHAPTER 2: GETTING STARTED USING EXCEL

Rows in Microsoft Excel

Columns in Microsoft Excel

CHAPTER 3: ENTRY, MODIFICATION AND MANAGEMENT OF DATA

Cell concept

Entering Data into a Cell

Modification of Data

Deletion of Data

Data Movement

Automatic Filling

Automatic data filling

Using AutoFill

Rows and Columns

Changing the Size of Columns and Rows

Hide or Show Rows and Columns

Inserting or Deleting Rows and Columns

CHAPTER 4: WORKING WITH WORKSHEETS

What is a worksheet?

The cell in the worksheet

Difference between Workbook and Worksheet

Worksheet tab in Excel

How to add a new Worksheet?

How to delete a Worksheet?

How to copy a worksheet

How to change the color of the Worksheet?

Benefits of Grouping Worksheets

How to separate worksheets?

How to identify grouped worksheets

CHAPTER 5: SORTING AND FILTERING DATA

Data sorting

Sort data on a sheet

Sort a range of cells

Sort data in a column

Sort data into multiple columns

Custom sorting

Sort data in a row

Sort by conditional formatting

Data filtering

How to Add Filters

Numeric Filter Options

CHAPTER 6: DATA VALIDATION

How to validate and give in Excel

How to create a validation rule in Excel

List of data validations in Excel (drop-down menu)

Copy a data validation rule to other cells

How to remove data validation

CHAPTER 7: FORMATTING CELLS

Change the font

How to change the data font

Change the font size of the data

Change the font color

Cell alignment

Add borders and fill colors to a cell

Formatting texts and numbers

CHAPTER 8: FORMULAS AND FUNCTIONS

Formula

Functions

CHAPTER 9: WORKING WITH EXCEL TABLES

What are Excel Tables?

Why use Excel Tables Excel tables offer numerous benefits, including:

Structured references in Excel Tables One of the advantages of tables is the ability to use structured names instead of cell references. You can use table and field names to create formulas without having to remember cell numbers.

Uses of Pivot Tables PivotTables can solve many business issues, such as:

CHAPTER 10: CREATING CHARTS

What are Excel Charts?

Types of Charts

Create a Chart in Excel

How to edit charts in Excel

How to Create a Control Chart in Excel

Types of charts and uses

Creating charts in Excel

Chart customization

CHAPTER 11: THE SECRETS OF EXCEL

Paste Special

How to add multiple rows

Conditional Formatting

How to spot duplicate data

Use Keyboard Shortcuts

Copy-Paste Formulas without Changing References

Debugging Formulas

Delete All Comments

Custom Sorting

Insert Bullet Lists into Excel

Multiple Level Sorting

Scrollable List in Excel

CHAPTER 12: USING EXCEL ON A SMARTPHONE

CHAPTER 13: THREE-LEVEL EXCEL: THE BEGINNER

Add your most used tasks to the toolbar for easier access

Data filtering

Incorporate dynamic headers and footers

Define the print area

Special paste options

Group and ungroup columns

Document security

Search for dependent and previous cells

Data validation in drop-down menus

Formatting text in columns

Creating charts

CHAPTER 14: EXCEL AT THREE LEVELS: THE INTERMEDIATE USER

Advanced data analysis

Handling large datasets

Advanced formulas and custom functions

Creating dynamic charts

"What-If" analysis and scenario management

Collaboration and data sharing

Statistical analysis

Import and connection with external sources

Excel keyboard shortcuts

Tricks for advanced Excel use at intermediate level

Creation and management of worksheets

Automation of repetitive tasks

Data management and cleansing

Import, export and connections

CHAPTER 15: EXCEL AT THREE LEVELS: THE ADVANCED USER

Advanced formulas and functions in Excel

CHAPTER 16: EXCEL DATABASE

Number counter

Counting values

CHAPTER 17: THE MUST HAVE FEATURES OF 2025

The MOD function and how to use it

The ROUND function and how to use it

The SORT function and how to use it

The SUM function and how to use it

The ROUNDUP function and how to use it

The RANDBETWEEN function and how to use it

Mathematical functions in Excel

The ROUNDDOWN function and how to use it

The SUMIF function and how to use it

The SUMIF function and how to use it

CHAPTER 18: TIPS AND TRICKS FOR 2025

CHAPTER 19: EXCEL IN EVERYDAY LIFE

Manage costs with Excel

Consolidate data from different sources with Excel

Access data via the Internet with Excel

Data security in Excel

CHAPTER 20: EXCEL AND ARTIFICIAL INTELLIGENCE

Understand the fundamentals of artificial intelligence

Overview of Excel's AI features

Data analysis and visualization using Excel and AI

Creating interactive dashboards with Excel and AI

AI-powered forecasts in Excel

Evaluate and improve forecast accuracy

Incorporating external factors into forecasting models

Leverage artificial intelligence for better data visualization

Create dynamic, interactive data visualizations

Integrating machine learning into data visualization

Introduction to AI-based analytics

Ethical implications of artificial intelligence in Excel

CONCLUSION

INTRODUCTION

The book Excel 2025 is a comprehensive and updated guide that thoroughly explores the functionalities and potential of Microsoft Excel, one of the most widely used spreadsheet management software in the world. The primary goal of this text is to provide a detailed overview of how to best utilize this tool, both for beginners and experienced professionals.

The work starts with a historical description of Excel’s development, tracing its evolution from the first versions to the most recent ones, highlighting the key innovations introduced with each release. It emphasizes Excel’s importance not just as a calculation software, but as a versatile platform that enables complex data management, statistical analysis, the creation of custom reports, and graphical representation.

One of the strengths of this book is its practical approach: each chapter is designed to guide readers step by step through various functionalities, from data entry and management to creating complex formulas and automation through macros. The text also covers advanced aspects such as using pivot tables for data analysis, sorting and filtering, and customizing the interface to improve productivity.

Additionally, it explores Excel’s ability to facilitate team collaboration, with tips on how to use shared files and advanced features for data analysis and protection. There is also a guide on how to install and configure the latest versions, including details on how to obtain various available licenses.

With clear language and numerous practical examples, Excel 2025 stands as an indispensable resource for anyone seeking to master Excel, from managing simple spreadsheets to building complex analytical tools.

CHAPTER 1: WHAT IS EXCEL? NOTIONS AND HISTORY

Microsoft Excel is an advanced spreadsheet management software created by Microsoft Corporation. It is an integral part of the Microsoft Office productivity suite and is widely used in professional, academic and personal contexts for managing data, performing complex calculations and creating graphical representations. Excel offers an intuitive and flexible environment that allows users to organize, analyze and interpret data efficiently. The fundamental concept of Excel is the worksheet, a grid made up of columns and rows that create cells into which data can be inserted. These cells are the basic element for entering numbers, text or formulas. One of the most notable features of Excel is the ability to automatically perform calculations through the use of formulas. Formulas allow you to carry out mathematical, statistical or logical operations on data present in different cells, greatly facilitating the management and analysis of information.

In addition, Excel provides numerous predefined functions that cover a wide range of operations, from calculating the sum of a series of numbers to finding the maximum value in a given range. These features help simplify the data processing process, allowing users to save time and minimize errors in calculations. Another key feature of Excel is the ability to graphically represent data through the use of graphs and tables. Users can easily generate bar, line, pie and more charts to clearly visualize trends and relationships between data, facilitating in-depth presentations and analysis.

In addition to being a tool for numerical calculations, Excel is also a powerful way to organize and manage information. Sorting and filtering options allow you to structure large amounts of data in an orderly manner, making it easier to find specific information. Furthermore, the pivot table functionality offers greater flexibility in data analysis, allowing you to extract significant information from large sets of data. Excel also stands out for the high degree of customization it offers: users can adapt the appearance and functionality of the spreadsheet to their needs. The ability to create macros to automate repetitive tasks helps increase efficiency and reduce human errors. This software is used in many disciplines, including finance, accounting, engineering, social sciences, and more. Thanks to its versatility, Excel proves to be an essential tool for professionals and students who need to manage complex data and obtain precise results. Ultimately, Microsoft Excel is much more than just a spreadsheet: it is a powerful and indispensable tool for anyone who needs to work with data efficiently and accurately.

The history of Excel

Twenty-seven years ago, the first version of Microsoft Excel for Macintosh systems was launched, while the version for Windows was introduced in 1987. Listed below are the versions of Excel for Windows and their features released over the years.

●      Excel 1.0 was released in 1985, and is the only version developed exclusively for the Macintosh.

●      Excel 2.0 marked the arrival of Excel on Windows in 1987, making it the first version available for this operating system.

●      Excel 3.0 saw the light of day in 1990, with the inclusion of new features such as the toolbar, drawing capabilities and the outlining function.

●      Excel 4.0 was released in 1992, bringing with it numerous updates and features.

●      Excel 5.0 was introduced in 1993 and was integrated into Microsoft Office 4.0; Key features of this release included support for multi-sheet workbooks and the introduction of VBA (Visual Basic for Applications).

●      Excel 7.0, launched in 1995 and an integral part of Microsoft Office 95, did not bring many new features, but it was significantly faster and more stable than the previous version, Excel 5.0.

●      Excel 8.0 arrived in 1997, included with Microsoft Office 1997; the main innovations introduced in this version were the guided assistance and the validation function.

●      Excel 9.0, released in 2000 and part of Microsoft Office 2000, included a document self-healing feature.

●      Excel 10.0 was launched in 2002 as part of Microsoft Office XP; Key features of this version included identifying errors in formulas and the ability to recover worksheets in the event of a program crash.

●      Excel 11.0, also known as Excel 2003, was released in 2003 and included in Microsoft Office 2003; among the most important innovations stands out the better support for the XML format.

●      Excel 2007, introduced in 2007, represented a turning point with the introduction of the tabbed system (Ribbon), which improved the user interface.

●      Excel 14.0, released in 2010, had several significant updates. Major improvements included new graphical designs and refinements in pivot table management.

●      Excel 15.0, also known as Excel 2015, was launched in 2015 and introduced over 50 new features.

●      Excel 2016, released in 2016, included the histogram as a major new feature, along with many other improvements.

●      Excel 16.0 or Excel 2019 was released in 2018 with the addition of new chart types.

●      Finally, Excel 2020, with further updates, was released in 2020.

The purpose of Microsoft Excel

The purpose of using Microsoft Excel goes far beyond simply managing numerical data. One of the main objectives of this software is to support more informed decision making through detailed data analysis. With Excel, users can explore complex relationships, spot trends, and recognize patterns using specific formulas and functions. Furthermore, the ability to display data in different formats, such as graphs and tables, allows you to communicate complex information clearly and effectively, making it a valuable tool for interpreting and presenting results. In the professional field, Microsoft Excel is often used for creating reports, financial forecasts, budgets and analyzing company performance. It provides the resources you need to evaluate business results, monitor finances and make strategic decisions based on accurate, up-to-date data. Thanks to its versatility, Excel is a fundamental tool for those who require an analytical approach in their work, adapting to a wide range of sectors.

Another essential purpose of Excel is to simplify the management of large volumes of data. Sorting, filtering and organizing features allow you to quickly access relevant information, reducing the need for time-consuming and complex searches. Additionally, Excel's search function makes it even easier to find specific data, improving the efficiency and accuracy of your analysis. A key aspect of using Microsoft Excel is automation, made possible through macros. These allow users to record and repeat a series of commands, automating repetitive tasks and minimizing the risk of human error. Automation is particularly useful when it is necessary to frequently manage large quantities of data, allowing significant savings in time and resources.

Another significant advantage of Excel is its ability to foster collaboration. The software allows multiple users to work simultaneously on shared spreadsheets, a valuable feature in contexts where teamwork is crucial to the success of a project. Thanks to the ability to make changes in real time, all collaborators can have an updated and common view of the data.

In addition to professional uses, Excel can also be used to manage personal information. Whether creating to-do lists or tracking personal expenses, Excel offers a flexible, customizable platform for tracking different types of data. With its intuitive interface and powerful features, it is accessible even to less experienced users, allowing them to reap the benefits of data management even in their daily lives.

Where you can download it from

The latest version of Microsoft Excel is available on the official Microsoft website and can be downloaded directly from there. The download link is as follows: https://www.microsoft.com/en-ww/microsoft-365/excel. You can purchase the software or try it for free by registering on the site. There are different plans and licenses for this program, both for home use and business use. As for the home license, there are three types of options: one for personal use, one for family (2 to 6 people) and one for students, which can only be used on a single computer or laptop. Each option has a different price, with an annual subscription.

For business use, there are four types of plans available: Basic, Standard, Premium, and Apps for Business. Each plan offers different features with varying costs on an annual basis. Businesses can combine these plans to get a variety of advanced and customized features based on their needs, thus improving the performance and operational capabilities of the software.

CHAPTER 2: GETTING STARTED USING EXCEL

After downloading Excel, launch the program. From the main window, you can search for it in the search bar and click on it, or double-click on the Excel icon. A window will open with the Excel 2021 home screen. From here, you can create a new file, select a template, or access recent files. To start a new worksheet, select "Blank Workbook." Excel's interface includes the "Ribbon," a strip of buttons located at the top of the program window.

The Ribbon is divided into tabs, each of which contains groups of commands. For example, the "Home" tab, the "Font" group, and the "Bold" button are used to apply bold text to selected text. This chapter describes the Excel interface, with particular attention to the Ribbon and the available commands. Each tab contains multiple groups, and each group has several commands, which can be accessed by clicking the arrows in the lower right corner of the groups, called "dialog launchers", which show additional related commands.

What is the Ribbon?

In the Microsoft Excel Ribbon, you'll find a row of tabs and icons that make up the Excel Control Panel. Like a quick toolbar, it allows you to navigate and manage commands. The icons are grouped into nine tabs, each with groups of icons and commands. The Ribbon is designed to help you perform common tasks in Excel.

Ribbon tabs

The Excel Ribbon includes nine main tabs: File, Home, Insert, Page Layout, Formulas, Data, Review, View, and Help. You can also customize the Ribbon by adding new tabs with your favorite commands.

●      File: This tab gives access to “Backstage view,” which includes options for managing Excel files, such as saving and file settings. Here you can update and manage your file data.

●      Home: Contains the most used commands, such as copy-paste, search-replace, sorting, filtering and data formatting.

●      Enter: Allows you to add elements such as images, graphs, tables, equations, hyperlinks, icons, shapes, headers and footers.

●      Page layout: It is mainly used for page setup and printing. Manages worksheet layout, margins, alignment and print area.

●      Formula: Here you can insert functions, name variables and manage calculation parameters. Also check the calculation options.

●      Data: Contains commands for managing sheet data and connecting to external data, with options for sorting, filtering, and manipulating data.

●      Revision: Offers tools for checking spelling, tracking changes, adding notes and comments, and protecting worksheets.

●      View: This tab allows you to manage the display of the worksheet, such as organizing the windows and freezing the panes.

●      Help: Available only on Microsoft Excel 2019 and 365, allows you to access the help panel to contact Microsoft support, give feedback and view training videos.

An additional tab, not visible by default, is the Development tab. This can be enabled by going to the File tab, choosing "Options", selecting "Customize Ribbon", and enabling the "Develop" option.

How to customize the Ribbon

You can change the appearance of the Ribbon. To do this, click on the arrow located at the top right, next to the button to minimize the program. A drop-down menu will open with three options:

Hide automatically

Show cards only

Show tabs and commands

Choose an option. Click the third option, “Show Tabs and Commands,” to return the Ribbon to its default view. If you choose to show tabs only, commands will only appear when you click on a tab.
By selecting "Show tabs and commands", the Ribbon will always be visible with all tabs and commands. Another way to minimize the ribbon is to right-click on it and select the "Collapse Ribbon" option. By doing so, the bar will be hidden. To restore the display of commands and the ribbon, click on the tab row. This will show the commands and buttons available for each tab. Next, right-click again and uncheck the “Collapse Ribbon” option.

Another option, visible above the Ribbon Collapse option, is "Customize Ribbon." By selecting it, a window will open with several options. In the "Main tabs" drop-down menu, you can choose your favorite options. You can add new tabs or remove existing ones according to your need. By adding a new tab, you can insert the commands you use most frequently, making your work easier and faster.

In the "Main Tabs" drop-down menu, click on the "New Tab" option. Rename it, then click OK. Now select the desired commands and add them to your group. Confirm the changes by clicking OK. To restore your Ribbon customization, click "Reset All Customizations" at the bottom of the customization window. Excel will ask you to confirm that you want to delete all changes; confirm by clicking OK.

Rows in Microsoft Excel

In Microsoft Excel, a row represents a horizontal line within a spreadsheet. Each worksheet contains 1,048,576 numbered rows. You can add or delete a row by clicking on the "Insert" tab. Another way to do this is to right-click on the desired row and select the insert or delete options.

Columns in Microsoft Excel

The columns, however, are vertical lines and each Excel sheet contains 16,384 of them, identified by letters. You can insert or delete a column by right-clicking on the selected column, which opens a menu of options for inserting or deleting an entire column.

A cell is defined as the intersection of a row and a column, represented by a rectangle. Each cell can contain only one piece of data at a time, which can be text, number, formula, date, etc. Cells are identified by an address that combines the column letter and row number (for example, A1). A range of cells (or "cell range") is a set of selected cells. This range can be symmetrical (square) or composed of single cells. Cell ranges can be used in formulas and are defined by referencing the top left and bottom right cells of the range. For example, (A1) represents the range from A1 to C6.

In Excel, a range reference is a combination of the addresses of the upper-left and lower-right cells, separated by a colon. For example, A1 represents the range of cells from A1 to C2. A cell reference, also known as a cell address, can be used in formulas to indicate specific cells or ranges of cells.

The formula bar in Excel is positioned below the Ribbon and above the worksheet. It includes three main buttons: Insert, Cancel and Insert Function (fx). This bar has three main components: the name box, the formula bar buttons, and the cell contents box. It is used to create new formulas or modify existing ones. The formula bar becomes active when you type an equal sign in a cell or click inside it. It can be expanded vertically or horizontally, depending on your preference, by clicking on the edges with the double arrow cursor. The bar can be hidden or made visible by clicking on the "View" tab and selecting or deselecting the "Formula Bar" option.

To enter data into a cell via the formula bar, simply select the cell and enter the data into the bar's input box. Once the value has been entered, you can confirm with the Enter key or cancel the operation with the "Esc" key. The fx button allows you to insert functions: clicking on it opens a dialog box in which you can search for and insert predefined formulas.

Select a cell and click the option to insert a function. Choose the function you want to add, then press "OK" to open a dialog where you can define the function's arguments. In Excel's help menu, you can click "Help for this function" to learn more and view practical examples. Next, select the “OK” option to confirm.

Insert Function

To search for a feature, type a brief description of what you want to do, then click "Go."

You can also select a category, such as "Math & Trigonometry", to choose a specific function. For example, the function ABS returns the absolute value of a number, that is, an unsigned number. Further details about this feature can be found by clicking on "Help for this feature".

Other Commands in the Excel Interface

The Quick Access Toolbar is located above the row of Excel ribbon tabs. You can add or remove commands from this toolbar. By default, it includes three commands: "File", "Undo" and "Redo". Next to these, you can click on the drop-down menu and select additional commands of your choice, such as creating a new document by selecting "New".

Customize the Quick Access Toolbar

Some predefined commands include: New, Open, Save, Quick Print, Print Preview, Spell Check, Undo, Redo, Sort Ascending, Sort Descending, and Touch/Mouse Mode. You can also show the toolbar below the Ribbon if you prefer.

Name of the Active Cell

The name box shows the address of the active cell. By entering the address of another cell in the name box and pressing Enter, Excel will take you directly to that cell.

Active Cell

The active cell is the one currently highlighted. It's the cell you moved the cursor to or pressed on. It is displayed prominently on the screen.

File button

Clicking the "File" button opens a drop-down menu with options such as opening, saving and printing the document.

Horizontal Scroll Bar

The horizontal scroll bar lets you scroll the contents of the worksheet horizontally.

Vertical Scroll Bar

The vertical scroll bar allows you to scroll the contents of the sheet vertically.

Zoom Bar

To zoom in or out of a worksheet in Excel, you use the zoom bar, which also shows the current zoom percentage.

Window Controls

Window controls manage the default Excel window. There are three buttons: one to minimize, one to maximize or restore the window, and one to close it, like in any other Windows application.

Status bar

The status bar contains specific information, such as the sum, count, and average of the selected areas colored by default.

Worksheet tabs

The worksheet tabs allow you to minimize, maximize, restore or close the sheet. At the bottom left of the screen, you can see other open worksheets, and you can access other existing files on your computer. A single Excel file (workbook) can contain multiple worksheets, numbered as "sheet1", "sheet2", and "sheet3" by default. When you open a workbook, the worksheet opens immediately.

CHAPTER 3: ENTRY, MODIFICATION AND MANAGEMENT OF DATA

This chapter explains how to work with cells, insert data, edit or delete data, and how to use the autofill feature for rows and columns. These skills are critical in the beginning stages of creating a spreadsheet.

Cell concept

As already mentioned, a cell is the rectangle that is formed at the intersection of a row and a column in an Excel spreadsheet. Each cell is identified by the row number and column letter. For example, in the following figure, the highlighted cell is positioned in A1. The name box shows the address.

Entering Data into a Cell

To enter data in a cell, click on it or use the arrow keys on your keyboard. The selected cell will be surrounded by a border, and the row and column headings will be highlighted. Until you select another cell, the current one will remain active. Enter your data in the form of text, symbols, dates or numbers. After entering the data, press the Enter key to confirm. The contents of the cell will be visible both in the cell itself and in the formula bar. You can also insert and edit the contents of a cell directly from the formula bar.

Sometimes you may need to select a larger group of cells, called a "cell range". To do this, click on a cell, hold down and drag the mouse to highlight all the contiguous cells you want to select. Release the mouse button to confirm the range selection.

Example:

In the Sheet1 sheet, click on cell A15.

Type the abbreviation "Tot." and press Enter.

Select cell A15 again.

Move the cursor to the formula bar. The pointer will turn into a cursor.

Left-click at the end of the word "Tot" and type "al" to complete the word "Total."

Click the checkbox next to the formula bar to confirm the change in the cell.

Double-click cell A15 and add the word "Purchase" after "Total", followed by a space. Press Enter.

Modification of Data

To edit the data in a cell, double-click the cell that contains the data you want to edit. The cell will enter edit mode and the cursor will position inside it, while the formula bar will show the contents of the cell. Alternatively, you can click directly in the formula bar to place the cursor where you want to make changes.

Deletion of Data

To delete data from a cell or range of cells, select the cell or range and press the "Backspace" or "Delete" key on your keyboard. You can delete the contents of multiple cells at once by pressing "Del", while the "Backspace" key deletes the contents of a single cell at a time. There are several methods for removing data from a spreadsheet, and you can use the "Undo" command if you accidentally delete.

Data Movement

After you enter data into a worksheet, you can move it to other areas of the sheet. The following steps show how to move data from one zone to another:

Select the cell or range of cells you want to move.

Move the mouse pointer to the left edge of the cell. The pointer will change to a four-headed arrow.

Click and hold the left mouse button, then drag the data to the new desired location.

Automatic Filling

To quickly copy data to other cells, you can use "AutoFill". Place the cursor over the lower right corner of the selected cell, where a small "+" symbol will appear. Drag the symbol up or down to fill adjacent cells with the same data or a sequence.

Example of Data Deletion

Place the cursor on a cell, for example B2, and select it by clicking with the left mouse button.

Press the "Delete" key on your keyboard to remove the data.

To delete a range of cells, select the desired range, for example B2 to C14.

Place your cursor over the lower right corner of the range, where the white "+" symbol will be replaced by a black symbol.

Drag up to cell C3 and release the mouse to clear the contents of the range.

Automatic data filling

When manually entering data into a worksheet, the AutoFill feature is very useful. This feature saves time in various contexts, especially when you need to enter data in a specific order, such as numbers (e.g. 1, 3, 5, 7) or qualitative data such as days of the week or months of the year.

Select a cell to enter data into; for example, enter "January" in cell A3.

Press Enter.

Reactivate cell A3.

Place the pointer in the lower right corner of cell A3. Here you will notice a small square called the "fill handle". As the cursor approaches the handle, the white "+" symbol will change to a darker sign.

After releasing the left mouse button, the range of cells will automatically display the twelve months of the year. The "AutoFill Options" will also appear.

Using AutoFill

When you select this option, you have several ways to enter data into a set of cells:

●      Copy Cells: By choosing this option, the months in the selected range will be changed to "January".

●      Series Filling: Allows you to restore the months of the year in the selected interval.

●      Filling Without Formatting: Fills the cells keeping only the data, without applying formatting.

Rows and Columns

You can insert new columns or rows, delete them, move them to other locations on the worksheet, or hide them. After working on a workbook for a long time, you may want to adjust the size of the columns and rows to fit the data you enter.

Changing the Size of Columns and Rows

To change the width of a column or the height of a row, place the mouse pointer between column A and column B on the worksheet. The white "+" symbol will turn into a double-headed arrow. Drag the column to the right to increase the width and see the full text (for example, "October"). As you drag, a box will appear indicating the number of characters the column can hold, based on the default Calibri font size 11. Release the mouse button to confirm the new size.

Another way to change the width of a column or the height of a row is to right-click the row or column you want to change. A drop-down menu will appear: select the "Row Height" or "Column Width" option and adjust them to your liking.

Hide or Show Rows and Columns

In addition to editing them, you can hide rows and columns to make the data tidier. Right-click on a row or column and select the "Hide" option. To show hidden rows or columns again, select the area they were in, right-click, and choose "Show."

Inserting or Deleting Rows and Columns

You can easily add or delete rows or columns in your Excel worksheet:

Insert a Column

Select the cell next to which you want to insert a column.

Left-click.

From the drop-down menu, select "Insert".

Choose the "Insert entire column" option and press OK. A blank column will be inserted next to the selected cell.

Insert a Row

Select a cell next to which you want to insert a row.

Left-click.

From the drop-down menu, select "Insert".

Choose the "Insert entire row" option and press OK. A blank row will be inserted above the selected cell.

Delete a Column or Row

Select the cell in the column or row to delete.

Choose the "Delete" option and confirm deletion of the entire column or row.

Alternatively, use the "Home" tab and click the "Delete" button arrow. Select the "Delete Sheet Columns" or "Delete Sheet Rows" option to complete the operation.

Moving Columns or Rows

To move a column or row, follow these steps:

Select the column or row by its header (e.g. A, B, C).

Click "Cut" on the "Home" tab.

Choose the column or row heading you want to move the data to.

From the "Insert" drop-down menu, select "Insert Cut Cells" to move the column or row.

Exchange of Rows and Columns

You can quickly swap rows or columns without having to recreate them. Hold down the "Shift" key on your keyboard, select the row or column you want to move, place the cursor on the edge of the row or column, and drag the row or column to the new position.

Using these functions, you can easily manage inserting, deleting, autofilling, and moving data in your Excel worksheet.

CHAPTER 4: WORKING WITH WORKSHEETS

What is a worksheet?

A spreadsheet is a Microsoft Excel file used to organize, calculate, and sort data. You can insert numbers, text, formulas, references, and functions into a spreadsheet. Cells are organized into rows and columns within the worksheet. The rows are numbered from 1 to 1,048,576, from top to bottom, while the columns are labeled with letters ranging from A to XFD, proceeding from left to right. The first 26 columns are labeled A through Z, after which we move on to combinations such as AA, AB through ZZ, continuing with AAA until the 16,384th column.

The cell in the worksheet

A cell is formed by combining a column and a row, and its address is determined by the column letter and the row number. The active cell is the one where you can insert or modify new data or formulas. It is easily recognizable thanks to a dark green border surrounding it. Additionally, dark gray highlights the corresponding row and column, and the cell location is indicated in the Name box.

Difference between Workbook and Worksheet

The main difference between a workbook and a worksheet is that a worksheet is a single Excel page on which you can write, edit, and organize data, while a workbook is a collection of multiple worksheets . Here are some additional differences:

●      Linking two worksheets is much easier than linking two workbooks, and linking folders can cause data security issues.

●      Only worksheets can be manipulated and analyzed directly; the workbook simply represents a collection of these sheets.

●      It's easy to add several worksheets to a folder, but it's more complex to add a workbook inside another.

●      A worksheet is a section of your workbook.

Worksheet tab in Excel

At the bottom of the worksheet are the sheet tabs. By clicking on them, you can directly access the desired page. You can add, delete, rename sheets or change the color of tabs. By default, Excel offers three sheets called "Sheet1", "Sheet2", and "Sheet3". You can only view one active sheet at a time, but you can add or delete multiple sheets as needed.

How to add a new Worksheet?

You can add new sheets at any time to the tab list. The quickest way is to click the "+" sign in the right corner of the worksheet window. There are also two other methods:

●      Using the ribbon command: select the Home menu, go to the Cells command group and click "Insert", then "Insert Sheet".

●      Right-click method: Right-click the sheet tab, select "Insert," and then click "OK."

How to delete a Worksheet?

To delete a sheet, there are two main methods:

●      Right-click method: Right-click the sheet tab, select "Delete" from the drop-down menu and confirm.

●      Use the Format option: click on the sheet you want to delete, go to the Cells group in the Home menu and select "Delete Sheet". Once you delete a sheet, you cannot recover it.

How to copy a worksheet

There are three ways to copy a worksheet:

●      Drag method: Select the sheet, hold down the Ctrl key and drag it to another location.

●      Right-click method: Right-click the sheet tab, select "Move or Copy", enable the "Make a copy" option, and click "OK".

●      Use the Format menu: from the Cells group in the Home menu, select "Move or Copy Sheet", choose the position and confirm the creation of the copy.

How to change the color of the Worksheet?

To distinguish worksheets, you can change the color of the tabs. To do this:

Click the sheet tab.

In the Cells group of the Home menu, select "Format" and choose "Tab Color."

Grouping and ungrouping worksheets

You can group sheets to work on multiple similar sheets at the same time. For example, you can create a sheet and copy it onto two other sheets, or group multiple blank sheets and insert or format data on them all at once. This is useful for managing large amounts of information. To group sheets, select the first sheet, hold down the Shift key and click on the last sheet you want to include in the group.

Benefits of Grouping Worksheets

The main advantages of grouping sheets include:

●      Enter the same data into the same cells on multiple sheets at the same time.

●      Update existing data on multiple sheets simultaneously.

●      Apply the same formulas and formatting to multiple sheets at once.

How to separate worksheets?

To separate grouped sheets, you can click on any sheet that is not part of the group or right-click on a sheet in the group and select "Ungroup Sheets".

How to identify grouped worksheets

Grouped sheets are distinguished by the white color of their tabs, while the tabs of non-grouped sheets are grey. Additionally, the word "Group" appears in the Excel title bar next to the workbook name.

CHAPTER 5: SORTING AND FILTERING DATA

Sorting data can help you quickly restructure a worksheet. For example, a list of contact information might be organized by last name. This chapter discusses various ways to sort and filter data alphabetically, numerically, and in other possible ways.

Data sorting

Before sorting your data, you need to decide whether to sort a range of cells or the entire worksheet. One column organizes all the data in the sheet, and when you apply sorting, related information from each row stays together. For example, in the "Contact Name" column, names can be sorted alphabetically. In a sheet with multiple tables, sorting a range of cells keeps the other tables intact.

Sort data on a sheet

In the example, we will sort a t-shirt order form alphabetically by last name (column C).

Example:

Homeroom #

Christiana Chen

Derek MacDonald

Esther Yaron

Melissa White

Nathan Albee

Sidney Kelly

Select a cell in the column you want to sort, such as C2.

In the "Data" tab, click "Sort A to Z" or "Z to A."

The sheet will be sorted by the selected column, such as last name.

Sort a range of cells

Select the range of cells you want to sort.

On the "Data" tab, click the "Sort" command.

A sorting dialog box will appear. Select the column to sort.

Choose ascending or descending order.

Click "OK." The sorting will only affect the selected range.

Sort data in a column

Follow these steps to sort a single column:

Select a cell in the column you want to sort.

On the "Data" tab, click "Sort A to Z" in the "Sort & Filter" group.

Sort data into multiple columns

To sort on multiple columns:

On the "Data" tab, click "Sort."

In the "Sort" dialog box, choose "Last Name" from the "Sort by" drop-down menu.

Click "Add Level" and select another column, such as "Sales".

Click "OK." The data will be sorted first by surname and then by sales.

Custom sorting

If the default options don't meet your needs, Excel lets you create a custom sort order. For example, you can sort by size instead of alphabetically.

Select the data to sort.

Click "Custom Sort" in the "Sort" drop-down menu.

The sorting window will appear, where you can select the column and sorting method.

You can choose a predefined list or create a new one. For example, you could sort by sizes (Small, Medium, Large).

Once you've created your custom list, select it from the menu.

Sort data in a row

Instead of sorting data by column, you can sort it by row.

Select a cell in the row you want to sort.

Press "Ctrl + A" to select the entire range.

In the "Sort" dialog box, choose the "Sort left to right" option.

Click "OK" to complete the sorting.

Sort by conditional formatting

Conditional formatting is an often-used feature that allows you to apply formats to cells based on specific criteria. This feature makes it easy to visually identify differences in cell values. For example, you can use colored icons to highlight true or false entries.

Select the range of cells.

On the "Home" tab, choose "Sort & Filter" and then "Custom Sorting."

In the dialog box, choose the column that contains the conditional icons.

From the "Sort by" menu, select "Conditional Formatting Icon."

Choose a sorting color, such as green, and click "OK."

If necessary, add additional sorting levels, selecting other colors, such as yellow.

Data filtering

Filtering in Excel helps you view only relevant data by temporarily hiding unnecessary items. Data is filtered based on specific criteria, allowing you to focus on the most important aspects of a set of information. For example, you can filter an organization's sales data at the city level to view sales from different locations at any given time.

When working with large amounts of data, filtering becomes an essential tool, as it transforms a complex visualization into a simpler and more understandable one. To apply filters, the dataset must have a header row indicating the name of each column. Filters are useful because they can be customized to meet specific needs: simply check the boxes next to the items you want to see and deselect the ones you don't care about.

Here are the three methods to add filters in Excel:

In the “Home” tab, there is the filter option.

In the "Data" tab, there is another filter option.

Use a shortcut key combination.

Method 1:

On the "Home" tab, under the "Sort and Filter" drop-down menu, select the data and click "Filter."

The filters will be applied to the selected data range. Filters are represented by drop-down arrows inside the red boxes.

To see the names of different cities, click the drop-down arrow in the "City" column.

Select 'Delhi' and deselect all other cities to display only 'Delhi' invoice values.

The data will be filtered, showing only statistics for the city of "Delhi".

Method 2:

On the "Data" tab, under the "Sort and Filter" area, select the "Filter" option.

Method 3:

Keyboard shortcuts are a great way to speed up everyday tasks. Using one of the shortcuts below, you can select the data and apply the filter. Press the "Shift+Ctrl+L" keys at the same time.

How to Add Filters

To filter the numbers, advanced strategies are used. Let's look at some examples to better understand how filters work in Excel.

Numeric Filter Options

Examples:

●      To find values ​​greater than 10,000 in column B (value of invoices).

●      To find numbers between 10,000 and 20,000 in column B, you can use the filter.

Filter for values ​​greater than 10,000:

Click the filter icon in column B (invoice value) to open the filter.

In the "Number Filters" section, select "greater than", as shown in the image.

Custom AutoFilter

Displays lines where the invoice value is greater than 10,000.

In the "Custom AutoFilter" window, enter 10,000 in the box next to "greater than."

Invoice values ​​over 10,000 will be displayed. The filter symbol will appear next to column B, indicating that it has been applied.

Filter for values ​​between 10,000 and 20,000:

Select "greater than" in the "Number Filters" menu.

In the second box of the "Custom AutoFilter" window, select "less than."

Enter 10,000 in the box next to "greater than" and 20,000 in the box next to "less than."

Only invoice values ​​between 10,000 and 20,000 will be displayed.

"Search Box" option:

Example: If you find the product IDs in the first column (city) and you want to filter the details for the product ID "prd 1", follow these steps:

Apply filters to the "Product ID" and "Invoice Value" columns.

Type "prd 1" in the search box to filter just that ID.

The output will only show the filtered values ​​corresponding to the ID "prd 1", as shown in the image.

Text Filters:

Text filters are used when you want to filter a column by a specific word or number. For example, you can filter for cells that start or end with a specific character or text, or by the presence or absence of certain characters or words.

Example:

If you want to apply a filter for a single item:

Select "equals" from the text filter.

A dialog box with “Custom AutoFilter” will appear.

Select the term "fruit" from the drop-down menu and click "OK."

Now you will only see data for the "fruit" category.

Filters by Color:

If your data includes rows or cells with different colors, you can use the "Filter by color" option to organize the data based on the colors present.

CHAPTER 6: DATA VALIDATION

Data validation in Excel is a feature that prevents users from entering non-compliant data into a spreadsheet. In technical terms, it involves defining a validation rule that limits the type of data that can be inserted into a given cell. This function is useful for controlling data input into the worksheet. For example, with data validation, you can limit entry to a selection from a drop-down list or restrict entry of data, such as dates or integers, to a predefined range. Data validation also allows you to check formulas and the data they generate.

You can create custom data validation alerts in Excel that alert users when they reach a limit, guiding them toward correct entry. In this way, data validation helps reduce non-standardized data, errors and unnecessary information in the spreadsheet. This feature is especially useful when working on an Excel sheet shared by multiple people. Many data analysts find Excel data validation useful, especially when they have to manage numerous users or strict input rules. Furthermore, it helps you save time and money through correct data entry. Overall, Excel data validation is a valuable feature, although it has some limitations that reduce its ability to fully assist users.

Data validation methods represent an effective defense against careless data entry and errors that can be made in tedious tasks. For example, you can require that dates entered into a cell fall within a specific time limit. Or, for cells that record text entries, you can select an item from a list instead of typing it manually. Finally, for cells that record numbers, you can require that they fall within a specific range.

Here are some examples of what data validation in Excel can do:

●      Allow only numeric or text values ​​to be entered in a cell.

●      Limit numbers to a certain range.

●      Enforce a specific number of characters for each data entry.

●      Prevent entering dates and times outside a certain range.

●      Limit the entry to a drop-down list of options.

●      Validate data using information in another cell.

●      Display an input message when the user selects a cell.

●      Show a warning when incorrect data is entered.

●       

For example, you can create a rule that limits data entry to four-digit numbers between 1000 and 9999. If the user enters a different value, Excel will display an error message explaining what went wrong.

How to validate and give in Excel

Follow these steps to add data validation in Excel:

Click the "Data Validation" button in the "Data Validation" dialog box.

Select one or more cells to validate, then click "Data Validation" in the "Data Tools" group of the "Data" tab.

Alternatively, you can access the data validation dialog by pressing Alt > D > L, one key at a time.

How to create a validation rule in Excel

Specify the validation criteria in the "Settings" tab according to your needs:

●      Values: Fill in the criteria fields with numbers, as shown in the screenshot.

●      Use cell references to create a rule based on a value or formula from another cell. Formulas allow you to express more complex conditions, like in this example, where you want users to only enter integers between 1000 and 9999.

After configuring the validation rule, click OK to close the window or navigate to another tab to include an input message or error warning.

List of data validations in Excel (drop-down menu)

To add a drop-down list of items to selected cells, follow these steps:

Open the "Data Validation" dialog box in the "Data" tab.

On the "Settings" tab, select "List" in the "Allow" box.

Enter the items from your validation list separated by commas in the "Source" field (for example, "Yes, No, N/A").

Make sure the "Dropdown menu in cell" box is checked to display the arrow next to the cell.

The resulting drop-down menu will look like this: "Yes", "No", "N/A". An advantage of table-based drop-down menus is that they update dynamically as you add or remove items from the table.

Copy a data validation rule to other cells

If a cell is already configured with a validation rule, you can apply the same rule to other cells without having to repeat the configuration from scratch. Here's how to do it:

Select the cell with the validation rule, right-click and select "Copy".

Select the other cells you want to apply the rule to.

If the cells are not adjacent, hold down Ctrl as you select them.

Right-click on them and select "Paste Special."

Choose the "Validation" option and click OK.

How to remove data validation

There are two methods to remove validation in Excel: the traditional one and the mouseless method, designed by Excel experts. Method 1: The traditional method

Select the cells you want to remove validation from.

Go to the "Data" tab and click "Data Validation".

On the "Settings" tab, click "Clear All," then click OK.

Method 2: Remove validation with "Paste Special"

Copy a blank cell without validation rules.

Select the cells you want to remove validation from.

Use the shortcut Ctrl + Alt + V, then N, to access "Paste Special".

Confirm the operation to remove validation.

CHAPTER 7: FORMATTING CELLS

In Excel, you can change the font, size, style, and color of text in cells, add borders, and change the background color of cells. Formatting a cell can be applied before or after data entry, because the formatting affects the cell itself and not the contents.

The most common formatting options are found in the "Font" group of the "Home" tab of the Ribbon. The following shows how to use cell formatting functions in Excel.

Change the font

To improve the appearance of your document, you can change the font in any cell. To do this, select the text to edit, which can be the entire cell or just a part of it. The default font in Excel is Calibri.

How to change the data font

Select the desired cell or cells.

On the "Home" tab of the Ribbon, click the "Font" group drop-down menu and select a new font.

Change the font size of the data

Select the cell or cells.

Click the font size drop-down arrow in the "Font" group on the "Home" tab.

Select the desired font size.

If your size is not listed, you can type it in the size field and press Enter.

You can also use commands to increase or decrease the font size, or use the keyboard to make changes.

Change the font color

Select the cell or cells.

On the "Home" tab, click the drop-down menu next to the "Font Color" feature to choose the color you want.

The text color will change immediately.

To see more color options, click "More Colors" at the bottom of the menu.

The bold, italic and underline features in the "Font" group of the "Home" tab can be used to apply desired styles to selected text.

Cell alignment

By default, text typed into the worksheet is aligned to the bottom left of a cell, while numbers are aligned to the bottom right. Changing the alignment of cell contents allows you to customize the display of text for better readability.

Text alignment refers to how the content positions within the cell. You can change the direction of the text via the "Orientation" button on the "Home" tab of the Ribbon, for example to use vertical or diagonal text in a heading to save horizontal space.

Vertical alignment refers to the positioning of text at the top or bottom of the cell, or its vertical centering. If you want more dramatic alignment, the "Degrees" field allows you to rotate the cell contents up to 90 degrees in either direction.

Horizontal alignment adjusts whether text is aligned to the left, right, or center of the cell.

The text control includes other features, such as “Wrap Text,” “Fit Text,” and “Merge Cells,” that change how Excel handles cell contents.

Finally, the text direction can be changed to start column A on the right side of the screen instead of the left. To make these changes:

Select the text to edit.

Click "Format" and then "Format Cells" on the "Home" tab.

The "Format Cells" dialog box appears.

Add borders and fill colors to a cell

Borders and fill colors can be used to create visible separations within a worksheet. To distinguish header cells, you can add borders and fill colors. To add a border to a cell or range of cells:

Select the cell you want to apply a border to.

Click the "Borders" command in the "Font" group of the "Home" tab.

Choose a new border style from the drop-down menu.

To apply a fill color:

Select the cell.

Click the "Fill Color" command in the "Font" group of the "Home" tab.

Choose the desired color.

The color will be applied to the selected cell.

Formatting texts and numbers

Number formats can be added to cells to reflect the type of data entered. You can display a numeric value as a percentage, currency, date, or time, among other options. Commands for formatting numbers are found in the "Number" group on the "Home" tab. To change the number format, select the cell and click the appropriate icon.

For more control or to select other number formats, you can use the "Format Cells" dialog:

Select the cell to format.

On the "Home" tab, select the "Accounting Number" option from the "Number" group.

To display a number as a currency with a dollar sign and thousands separators, click "Format" and choose the desired style.

To show a number as a percentage, select "Percent Style" from the menu.

To display a value with thousands separators and two decimals, click the "Separator Style" button.

To change the number of decimals:

Select the cell to edit.

In the "Number" group on the "Home" tab, use the buttons to increase or decrease the number of decimals.

CHAPTER 8: FORMULAS AND FUNCTIONS

This chapter deals with formulas and functions in Excel. What are formulas and how are they used with examples? What are functions, what are their types and examples?

Select a cell to work on.

Type an equals sign (=) to tell Excel that you want to insert a formula.

For example, in cell A3, you can find a formula that adds the values ​​of cells A1 and A2. 3. Type the formula A1+A2 as an example. 4. Change the value of cell A1 to 3. 5. Excel will automatically update the value of cell A3.

Formula

A formula in Excel is an operation that works with the values ​​of a single cell or a range of cells. For example, cell A3 contains a formula that adds the value in cell A2 to that in cell A1.

How to insert a formula

Follow the steps to insert a formula:

Select the cell where you want to insert the formula.

Type the equals sign (=), followed by the formula you want to use.

Editing a formula

Excel displays a cell's value or formula in the formula bar when you click it. To edit a formula:

Click on the formula bar and make the necessary changes.

Press the Enter key to confirm the change.

Operator priorities

Excel follows a predefined order to perform calculations. If part of the formula is enclosed in parentheses, it is calculated first. Next, multiplications and divisions are calculated, and finally additions and subtractions. For example:

●      Formula: =A1*A2+A3 Excel multiplies A1 by A2 and then adds the value of A3.

●      Formula: =A1*(A2+A3) In this case, Excel adds A2 and A3 before multiplying the result by A1.

Create a formula via copy/paste

When you copy a formula, Excel automatically updates cell references. For example, if you copy the formula from A4 to B4, the references will change to fit the new location.

The dollar sign ($) in Excel formulas

The dollar sign ($) is used to indicate absolute references in formulas, preventing the references from changing when you drag or copy the formula to other cells. There are three ways to use absolute references:

●      =$A1: Freeze column A, while the row reference changes.

●      =A$1: lock row 1, while the column can change.

●      =$A$1: Lock both column and row.

Add a formula using the "Insert Function" button

You can use the "Insert Function" command to choose other Excel functions. When you click this command, a dialog box appears that allows you to search for the desired function and define the necessary arguments.

How to use a formula

There are three ways to use a formula:

By directly entering the formula in a cell or in the formula bar.

Using the "Insert Function" command to search for a function.

Using the "AutoSum" button on the Home or Formulas tab.

Functions

Functions in Excel are predefined formulas that perform specific calculations. Here are some commonly used functions:

Numeric functions

SUM: The SUM function adds all the numbers in a range of cells.

Syntax: SUM(number1, [number2], ...)

Example: =SUM(A1:A3) sums the values ​​of A1, A2 and A3.

AVERAGE: Calculates the average of the numbers.

Syntax: AVERAGE(number1, [number2], ...)

Example: =AVERAGE(B2:B11) calculates the average of the values ​​between B2 and B11.

SUMIF: Sums numbers in a range that satisfy a condition.

Example: =SUMIF(A2:A6, ">5", B2:B6) sums the values ​​in the range B2 only if the corresponding values ​​in A2 are greater than 5.

ROUNDDOWN: Rounds a number down.

Syntax: ROUNDDOWN(number, digits)

Example: =ROUNDDOWN(234.567, 1) returns 234.5.

ROUNDUP: Rounds a number up.

Syntax: ROUNDUP(number, digits)

Example: =ROUNDUP(234.567, 1) returns 234.6.

SUMPRODUCT: Multiplies the values ​​of two or more intervals and adds the results.

Sintassi: SUMPRODUCT(array1, [array2], ...)

Example: =SUMPRODUCT(A1:B2, C1:D2) returns the sum of the corresponding products of these ranges.

Text functions

T: Returns the text associated with a value.

Syntax: T(value)

Example: =T(A1) returns the text contained in A1.

TEXT: Converts a number to text, formatting it according to the given specifications.

Syntax: TEXT(value, format)

Example: =TEXT(A1, "$#,##0.00") returns a number formatted as currency.

TEXTJOIN: Joins multiple text strings, separated by a delimiter.

Syntax: TEXTJOIN(delimiter, ignore gaps, text1, text2, ...)

Example: =TEXTJOIN(", ", TRUE, A1:B1) returns the text of the cells joined by a comma.

TRIM: Removes unnecessary spaces from a text string.

Syntax: TRIM(text)

Example: =TRIM(A1) returns the text without excess spaces.

JUST: Returns the length of a text string.

Syntax: LEN(text)

Example: =LEN(A1) returns the number of characters in cell A1.

CLEAN: Removes non-printable characters from a string.

Syntax: CLEAN(text)

Example: =CLEAN(A1) returns the text without non-printing characters.

EXACT: Compares two strings and returns TRUE if they are identical.

Syntax: EXACT(text1, text2)

Example: =EXACT(A1, A2) returns TRUE if the contents of A1 and A2 are identical.

PROPER: Converts the first letter of each word to uppercase.

Syntax: PROPER(text)

Example: =PROPER(A1) transforms the text of A1 into a string with uppercase initials.

REPLACE: Replaces a piece of text with another string.

Syntax: REPLACE(text, start, number_of characters, new_text)

Example: =REPLACE(A1, 1, 5, "Beta") replaces the first 5 characters of A1 with "Beta".

SUBSTITUTE: Replaces specific text with another in a string.

Syntax: SUBSTITUTE(text, old_text, new_text, [occurrence_number])

Example: =SUBSTITUTE(A1, "old", "new") replaces all occurrences of "old" with "new" in A1.

CONCATENATE

The CONCATENATE function in Microsoft Excel allows you to join two or more text strings. This is a built-in Excel function, classified as a text function. CONCATENATE can be used within a spreadsheet cell as part of a formula.
Syntax: CONCATENATE (text1, [text2, ... text_n])
Example:
=CONCATENATE (A2, B2, C2, D2)
Result: "EFGH"

UPPER

Microsoft Excel's UPPER function converts all text to uppercase. It is a text function built into Excel.
Syntax: UPPER (text)
Example:
=UPPER (A1)
Result: "ALPHABET SOUP"

LOWER

Microsoft Excel's LOWER function converts all letters in a string to lowercase. If the string contains non-alphabetic characters, they will not be modified.
Syntax: LOWER (text)
Example:
=LOWER (A1)
Result: "alphabet soup"

Logical functions

Here are some common logic functions:

AND
The Microsoft Excel AND function returns TRUE if all conditions are true. If any of the conditions are false, returns FALSE.
Syntax: AND (condition1, [condition2, ...])
Example:
=AND(A1>10, A1<40)
Result: TRUE

IF
The Microsoft Excel IF function returns one value if the condition is true and another value if the condition is false.
Syntax: IF (condition, value_if_true, [value_if_false])
Example:
=IF(B2<10, "Reorder", "")
Result: "Reorder"

NOT
The Microsoft Excel NOT function returns the logical opposite value.
Syntax: NOT (logical_value)
Example:
=NOT(A2="techonthenet.com")
Result: FALSE

OR
The Microsoft Excel OR function returns TRUE if one or more conditions are true. Otherwise, it returns FALSE.
Syntax: OR (condition1, [condition2, ...])
Example:
=OR(A1<10, A1=40)
Result: FALSE

SWITCH
The SWITCH function in Excel compares an expression with a list of values ​​and returns the corresponding result.
Syntax: SWITCH (expression, value1, result1, value2, result2, ... [default_value])
Example:
=SWITCH(A2, "Excel", "TechOnTheNet.com", "Minecraft", "DigMinecraft.com", "Unknown")
Result: "DigMinecraft.com"

TRUE
The Microsoft Excel TRUE function returns the logical value TRUE.
Syntax: TRUE ()
Example:
=TRUE()
Result: TRUE

FALSE
The Microsoft Excel FALSE function returns the logical value FALSE.
Syntax: FALSE ()
Example:
=FALSE()
Result: FALSE

IFERROR
The IFERROR function in Microsoft Excel returns an alternate value if a formula generates an error.
Syntax: IFERROR (formula, alternate_value)
Example:
=IFERROR(A3/B3, 0)
Result: 0

IFNA
The IFNA function in Excel returns an alternate value if a formula generates the #N/A error.
Syntax: IFNA (formula, alternate_value)
Example:
=IFNA(VLOOKUP(E3, $A$3:$C$7, 2, FALSE), 0)
Result: 0

IFS
The Microsoft Excel IFS function allows you to evaluate multiple conditions in a single function.
Syntax: IFS (condition1, result1, [condition2, result2], ...)
Example:
=IFS(A2="Apple", "Fruit", A2="Potato", "Veg", A2="Steak", "Meat")
Result: "Fruit"

Counting functions

Here are some commonly used counting functions:

COUNT
Microsoft Excel's COUNT function counts the number of cells that contain numbers.
Syntax: COUNT (argument1, [argument2, ...])
Example:
=COUNT(A1:A6)
Result: 3

COUNTA
The Microsoft Excel COUNTA function counts the number of non-blank cells.
Syntax: COUNTA (argument1, [argument2, ...])
Example:
=COUNTA(C2:C7)
Result: 3

COUNTBLANK
Excel's COUNTBLANK function counts the number of blank cells in a range.
Syntax: COUNTBLANK (range)
Example:
=COUNTBLANK(A1:A4)
Result: 1

COUNTIF
The COUNTIF function counts cells that satisfy a certain condition.
Syntax: COUNTIF(range, criterion)
Example:
=COUNTIF(A2:A7, D2)
Result: 1

FREQUENCY
The FREQUENCY function returns the frequency with which certain values ​​appear in a data set.
Syntax: FREQUENCY (data, intervals)
Example:
=FREQUENCY(B2:B10, D2)
Result: 2

MIN
The MIN function returns the lowest value of a set of numbers.
Syntax: MIN (number1, [number2, ...])
Example:
=MIN(A2, A3)
Result: 7.2

MAX
The MAX function returns the highest value of a set of numbers.
Syntax: MAX (number1, [number2, ...])
Example:
=MAX(A2, A3)
Result: 10.5

MEDIAN
The MEDIAN function calculates the median of a set of numbers.
Syntax: MEDIAN (number1, [number2, ...])
Example:
=MEDIAN(A2, A3)
Result: 8.85

MODE
The MODE function returns the value that appears most frequently in a group of numbers.
Syntax: MODE (number1, [number2, ...])
Example:
=MODE(B2:H2)
Result: 85

GROWTH
The GROWTH function calculates the expected exponential growth based on the data provided.
Syntax: GROWTH (known_y_values, [known_x_values], [new_x_values], [constant])
Example:
=GROWTH(B2:B6, A2:A6)
Result: 7.34

These numerical and logical functions are fundamental tools in Excel for performing complex calculations and data analysis.

CHAPTER 9: WORKING WITH EXCEL TABLES

What are Excel Tables?

Tables in Excel are tools for organizing data in an orderly manner. They act as containers for data, making it easier to examine and manage it. Using tables in Excel can speed up your work, especially when dealing with large amounts of information. Table headers allow you to easily reference each column, making them very useful for managing complex data.

Prepare the data:
Before creating an Excel table, follow these tips to organize your data:

●      The data must be arranged in rows and columns, where each row represents a single record.

●      Each column in the first row should have a short, descriptive title.

●      Each column must contain only one data type.

●      There must be no empty rows or columns.

●      Leave at least one blank row and column around the data to separate it from other information in the worksheet.

Create an Excel Table
Once your data is organized, follow these steps to create a table:

Select the data to include in the table, including any titles.

From the "Insert" menu, select "Table." A window will appear with the selected data range.

If your data contains headers, select the "My table has headers" option and click "OK."

Excel will create a well-formatted table that offers several advanced features with just one click.

Why use Excel Tables
Excel tables offer numerous benefits, including:

●      Style and formatting: Once created, the table layout can be easily modified via the "Table Design" menu.

●      Automatic expansion: As you add new data, the table automatically expands and applies the same format to new rows or columns.

●      Table Name: You can name the table to make references in formulas easier.

Structured references in Excel Tables
One of the advantages of tables is the ability to use structured names instead of cell references. You can use table and field names to create formulas without having to remember cell numbers.

Table pivot
PivotTables are powerful tools for analyzing and summarizing large amounts of data. They allow you to identify summaries, averages, and outliers. Follow these steps to create a pivot table:

Select the data to analyze.

From the "Insert" menu, select "PivotTable."

Customize the table fields in the "PivotTable Fields" window to view and organize the data as you wish.

You can sort data by name, value, or other parameters, and use filters to isolate specific sections of the data.

Two-dimensional pivot tables
Pivot tables can also be used with fields in rows and columns at the same time, allowing for two-dimensional analysis of the data. To create one, select the data and follow the procedure indicated for pivot tables, adding fields in both rows and columns.

Uses of Pivot Tables
PivotTables can solve many business issues, such as:

●      Calculate sums or averages.

●      Represent totals as percentages of the total.

●      Create a list of unique values.

●      Summarize complex reports in simple 2x2 grids.

●      Determine the maximum and minimum values ​​of a data set.

Table Style and Formatting
Excel allows you to use different styles to make the data contained in spreadsheets more understandable. These styles can be applied to improve the readability and interpretation of information.

Table Styles in Excel
Tables in Excel make data analysis easier with built-in tools like filtering, sorting, and structured references. You can choose from more than 50 predefined styles divided into categories (light, medium, dark) to customize the look of the table.

Table Style Options
In the "Table Design" menu you can customize several elements:

●      Header line: The headers remain visible even when the data is filtered.

●      Alternate rows: Excel applies alternating coloring to make it easier to read.

●      Total row: You can add a totals row to the table, which automatically calculates sums, minimums, maximums, and other values.

Select Table Style During Creation
To apply a specific style to a table:

Select the data to transform into a table.

Go to "Format as Table" in the "Home" menu and select a style from the gallery.

Changing the Style of an Existing Table
To change the style of an existing table, select a cell within the table, then click "More" in the Styles category and select the new style.

Removing Table Formatting
To maintain the functionality of a table while removing formatting:

Select a cell within the table.

Click "More" in the Styles menu, then select "Clear" to remove all formatting.

CHAPTER 10: CREATING CHARTS

Excel is widely used by businesses of all sizes and industries to store data. One of its main functions is the ability to convert data into graphs, offering a visual representation that facilitates understanding information and supports informed business decisions.

What are Excel Charts?

A chart is a visual representation of data organized into rows and columns, which allows you to analyze data sets for trends and patterns. Graphs and diagrams help interpret data by making quantities visible in a simple way. Although the terms "graph" and "diagram" are often used interchangeably, they differ from each other: Diagrams are simpler representations of data, typically for showing values ​​over time. Graphs, on the other hand, are more complex, allowing comparison between different parts of a data set, and are also more visually appealing. In presentations, graphs and charts are often used to provide a quick overview of progress or results to managers, clients or team members. Excel makes creating charts easy, thanks to the ability to keep data in a workbook without the need for imports from other software. It also has a wide range of predefined graphs that you can choose based on the relationships you want to highlight.

Types of Charts

Excel offers a large collection of charts to visually represent data. While many chart styles can fit a particular data set, it's important to choose the most suitable one to display your data effectively. Starting with the 2013 version, Excel includes a function that analyzes your data and suggests the most appropriate chart style. Below are the most used Excel charts and when to use them:

●      Column Charts: Ideal for comparing data or different categories of a variable. There are seven column chart styles, including grouped, stacked, 100% stacked, 3D, and other columns. The values ​​are displayed vertically.

●      Bar Charts: Similar to column charts, but with horizontal bars. They are useful when axis labels are too long for a column chart or to better display negative values.

●      Pie Charts: Useful for viewing the percentage composition of a set of data, representing each value as a slice of the pie. Pie charts should only be used when you have a single data set with fewer than five categories.

●      Scatter Charts: Show the correlation between two variables, similar to line graphs, but used to analyze the influence of one variable on the other. Variations include scatter plots with lines and markers, smooth lines, and bubbles.

●      Line Graphs: Perfect for viewing patterns or trends over time. There are seven types of line charts, including marker and 3D variations. These charts are ideal for representing long periods of time with many data points.

●      Combined Charts: Let you combine different chart types (for example, columns and lines) to show complex relationships between data of different sizes.

In addition to these, Excel offers other specific charts:

●      Area charts: Similar to line graphs, but the area below the line is colored, useful for showing differences between variables over time.

●      Surface Charts: Used to represent three-dimensional data, they are suitable for data sets with multiple variables.

●      Radar charts: Ideal for comparing different variables with each other. All variables start from the center and extend radially, making it useful for showing strengths and weaknesses.

Create a Chart in Excel

Charts are effective tools for visually communicating information. To create a chart, the first crucial step is to select the right data. After you enter data into your worksheet, you can highlight the cells containing the information you want to view and choose the type of chart you want.

For example, suppose you have a table with two columns: "Year" in column A and "Value" in column B. You want to create a chart that shows value on the vertical axis and year on the horizontal axis. Once you have selected your data, you can insert a chart by following these steps:

Select the data to use.

On the ribbon, click "Insert" and choose your preferred chart type.

How to edit charts in Excel

Once you insert your chart into Excel, you can change its appearance and position in various ways. Here are some options:

●      To add labels (such as title or axis labels), click "Add Chart Element" in the "Chart Layout" group on the "Design" ribbon.

●      Use the "Chart Tools" bar to change the chart type, data, or position.

●      Select an item in the chart, then use the Format ribbon to adjust the style, shape, and color of the text.

How to Create a Control Chart in Excel

The control chart, also known as a process behavior diagram, is often used to determine whether a business or manufacturing process is under statistical control. Here are the steps to create a control chart in Excel:

Enter data: First of all, enter the collected data into the worksheet. Each row will represent one sample, with each observation recorded in a separate cell.

Calculate sample statistics: For each control chart, calculate statistics such as mean and range using standard Excel formulas.

Calculate the Center Line and Control Limits: The Center Line represents the average of the data, while the upper and lower control limits are calculated by adding or subtracting three standard deviations from the mean.

Using charts in Excel has many advantages, such as:

●      Visual clarity: Charts transform complex data into intuitive, easy-to-read visual representations.

●      Customization: A wide range of chart types and formatting options allows you to tailor the chart to the specific needs of your presentation or analysis.

●      Trend analysis: Excel analysis tools make it easy to identify patterns, trends or anomalies in data, offering concrete support for business decisions.

●      Real-time updates: Thanks to integration with other applications and data updated in real time, Excel charts automatically adapt to changes in the data, maintaining their relevance.

Types of charts and uses

Choosing the right type of graph is essential to effectively communicate data. Excel offers several options, each suited to specific purposes:

Bar chart: Ideal for comparing categories or quantities, it is an immediate tool for viewing differences between data, such as monthly sales or team performance.

Line graph: Great for representing the evolution of data over time, such as quarterly sales or stock performance.

Pie chart: Perfect for showing the percentage composition of a whole, such as revenue breakdown by category or market share.

Scatter plot: Used to analyze the relationship between two variables, such as the correlation between budget and performance.

Cumulative bar chart: Effective for showing the progressive accumulation of quantities over time, for example for cumulative revenue analysis.

Creating charts in Excel

Creating graphs in Excel allows you to transform complex numbers into clear and convincing visualizations. Follow these steps to create a chart:

Select your data carefully: Organize information into columns or rows to make data easier to read.

Choose the chart type: Go to the "Insert" tab and select the most suitable chart from the available options.

Customize the chart: Edit colors, styles, labels, and titles to improve visual clarity.

Add design elements: Use the "Layout" tab to insert titles, axis labels, and legends.

Automatically update data: Link the chart to the original data for real-time updates.

Use analytics tools: Integrate analytics tools to gain insights from your data.

Chart customization

After you create a chart, Excel lets you edit various aspects of it: you can swap the axis, change the data source, update the title or layout, and apply different styles. For example, if you want to create a chart with quarterly sales:

Select the data range with headers.

Go to "Insert" and choose a chart, such as Clustered Column Chart.

Click "OK" to add the chart to the worksheet.

Inversion of the X and Y axes

You can swap the values ​​of the horizontal (X) and vertical (Y) axes. To do this, select the chart and click "Chart Design" > "Switch Row/Column" to swap the data on the axes.

Change the data source

To change the data used as the basis of the chart, follow these steps:

●      Go to the "Design" tab and click the "Select Data" button. The "Select Data Source" dialog box will open.

●      In the window, click the arrow next to the "Chart Data Range" field, turning it into a downward arrow.

●      Select the desired cells in the worksheet area, then click the down arrow again to return to the "Select Data Source" dialog box.

●      Click "OK" to confirm the change. The chart will now use the newly selected data.

Add titles to the axes

When you create a new chart, Excel displays "Chart Title" as the default text, which you'll need to replace with an appropriate title. Also, there are no labels on the axes, which you can add if necessary.

●      To change the chart title, simply click on the placeholder and enter the desired title. Alternatively, you can use a reference from the worksheet. For example, if the title "Sales by Quarter" is in cell A1, you can click the chart title and type "=A1" in the formula bar. The value of cell A1 will be used as the chart title.

●      You can also add titles to the chart axes. The Y axis is on the left, while the X axis is at the bottom. To change the chart layout and add axis titles:

Go to "Chart Design" and select "Quick Layout."

A popup will appear with several layout options. You can hover over each option to get a preview of the layout. Some include axis titles and move the legend to the side of the graph. For example, "Layout 9" is ideal if you want titles for both axes.

If you choose "Layout 9," the chart will display labels on the axes, which you can change by clicking on them or by entering a cell reference, such as =A1, to use the text contained in cell A1.

Chart styles

When you select a chart, the "Chart Design" tab appears in the ribbon, offering a set of predefined styles for customizing the look and color of the chart.

●      To change the color of the graph area:

Click on the central area of ​​the graph, called the plot area, to select it.

Once selected, go to the "Format" tab in the Ribbon.

Click the drop-down button in the "Shape Styles" group.

A menu with different theme styles will appear. Hover over each one to see a preview of how the chart will appear.

When you find the style you like, click on it to apply it to the chart.

Adding a Sparkline
To create a sparkline chart, select the data you want to graph. The "Quick Analysis" tool will appear at the bottom right of the selection.
Click on this tool to open a menu with various options, such as "Formatting", "Charts", "Totals", "Tables" and "Sparkling Lines".
Select the "Sparkling Lines" option and then choose between "Line", "Column" or "Victory/Loss".

In this example, the "Line" option has been chosen, and sparklines will be created in the cells immediately to the right of the selected values.

To format the Sparkline, click on it. The "Sparkle Line" tab will appear on the ribbon, where you can access the "Style" group to edit and customize the look of your sparkline.

You can use several options to customize the Sparkline:

●      Select "Line", "Column" or "Win/Loss" to change the graph type.

●      Turn on "Markers" to highlight specific values ​​in the graph.

●      Edit the Sparkline style or change the line and marker colors via the "Sparkline Color" and "Mark Color" options.

●      You can also change the line weight by clicking "Sparkline Color > Weight".

●      If you want to display an axis to separate positive and negative values, click "Axis".

Adding and removing titles in charts
Adding titles to graphs and axes makes information easier to understand. When you select a chart, title formatting options become available on the "Layout" tab under "Labels."
To add a title, click on "Graphic Title" and enter the desired text in the box that appears. You can also change the positioning of the title. Text formatting options are available once the title box is selected.
To add titles to the axes, select the chart and go to the "Layout" tab. In the "Labels" group, click "Axis Titles", where you can enter titles for the X, Y, or Z axis. After entering the title, you can change its format just like chart titles.

Adding labels to data points
To highlight specific data points within a chart, you can add labels. Select the chart, then go to the "Layout" tab, click "Data Labels" in the "Labels" group.
From here you can choose to add labels to all data points in a series or just individual points. You can also remove any unnecessary labels.

Adding a legend
Adding a legend allows you to clarify what the different parts of the graph represent. Select the chart and go to the "Layout" tab. In the "Labels" group click on "Legend" and choose where to place it.
If you need additional options, you can access "More Legend Options". You can move the legend with the mouse or change the settings through the menu. If you no longer need it, you can remove it by selecting it and pressing "Delete". You can also edit the legend entries individually by clicking on them.

Changing the chart size
You can move and resize charts by dragging them or using the options on the "Format" tab. In the "Dimensions" group you can specifically set the width and height of the chart.
Furthermore, in the same section you will find other options that allow you to rotate or resize the graph. The properties tab also lets you control how the chart behaves in relation to resizing worksheet cells.

Creating a chart template
To save a chart as a template for future use, select the chart and go to the "Design" tab. In the "Type" group, click "Save as Template."
Once you have given the template a name, it will be available in the "Insert Chart" menu under "Templates". The template will retain all the color, format and size settings of the original chart and can be edited like a normal chart.
The template will be available for use in any sheet or workbook.

CHAPTER 11: THE SECRETS OF EXCEL

Microsoft Excel is essential software for every business. Some highly appreciate its features, considering it a valuable tool for managing, reporting and illustrating data tables. Excel tricks and tips can make your work easier and more efficient when using this powerful tool. Many office operations are performed thanks to this "magic" software, which initially may seem like just a program for entering data into tables.

However, this is a limited view of the program's true capabilities. Knowing how to use Excel is essential for managing everything from company budgets to the data needed for the administration of an entire country.

Below are some helpful tips and techniques to improve your Excel skills.

Paste Special

Copying and pasting are among the most basic and frequently used operations in Excel. However, you often pass along an unwanted format or copy a formula when you just want the value. With the Paste Special function, you can choose which components of the copied cell to transfer. Use the shortcut Ctrl+Alt+V to open Paste Special and make your selection after copying the cell.

How to add multiple rows

It is often necessary to add new lines between existing ones. The shortcut Ctrl+Shift+ makes this operation easier, especially when you need to add multiple lines. By selecting the desired number of rows and right-clicking, inserting will be even faster than simply clicking the Insert option.

Conditional Formatting

Conditional formatting is a great visual tool for analyzing data. Allows you to change the color of a cell based on the data present and the criteria set. This feature is used to create heat maps, color label similar data, and much more. To use it:

●      Select the cells to format.

●      From the Home menu, choose Conditional Formatting.

●      Choose the desired criterion from the drop-down menu.

●      Set the rules and click OK to view the results.

How to spot duplicate data

To identify and highlight duplicate data groups in Excel, follow these steps:

●      Select the range of cells you want to work with.

●      Go to the Styles section of the Home menu and click Conditional Formatting.

●      From the menu, choose the Duplicate Values ​​option under Cell Highlight Rules.

●      Choose how to format duplicate cells and click OK to detect duplicates.

Use Keyboard Shortcuts

Excel is extremely comprehensive, and there are numerous tools to master. Knowing the main keyboard shortcuts can increase productivity and speed up work, especially when creating financial models or analysis. In the Appendix of this book you will find the 50 most useful shortcuts.

Copy-Paste Formulas without Changing References

When you copy a cell with a formula to another location, the references tend to change, causing confusion. Here's how to avoid it:

●      Select the cells to duplicate.

●      Go to the Home tab and choose Number. From the drop-down menu, select Text.

●      Press F2 to enter edit mode, then press Enter while holding down Ctrl.

●      Copy the cells and paste them where you want.

●      Change the format from Text to General and press F2, then Ctrl + Enter.

Debugging Formulas

Errors in formulas can arise from many causes: missing comma, incorrect reference, missing arguments, or incorrect parentheses. It can be difficult to spot the error, especially if the formula was created by someone else. To make debugging easier:

●      Select the cell with the formula.

●      Go to Formulas and choose Check Formulas.

●      Click Evaluate Formula to see the steps in which Excel examines the calculation.

Delete All Comments

If you need to clean up a worksheet and remove all comments, follow this simple method:

●      Select all cells.

●      Go to Home -> Edit -> Find & Select -> Special.

●      In the Go to Starred window, choose Comments and click OK.

●      Now right-click on one of the selected cells and choose Delete Comment.

Custom Sorting

Sorting is one of the most used features in Excel, like alphabetical or numeric sorting. However, you can create custom policies. Here's how to do it:

●      Select File -> Options from the File menu.

●      In the Excel Options window, choose Advanced.

●      Scroll until you find the Custom List section and click Edit List.

●      Enter the criteria in the appropriate box, separating them with a comma.

●      Click Add and then OK.

Insert Bullet Lists into Excel

If you need to create a bulleted list inside a cell, follow this trick:

●      Go to the cell and double-click to enter edit mode.

●      Press Alt + 7 or Alt + 9 to insert the bullet.

Multiple Level Sorting

Multilevel sorting allows you to sort one column and then another, maintaining the previous sorting. Follow these steps:

●      Select the data.

●      Go to the Data menu and choose Sort & Filter.

●      Set the desired sorting criteria.

Scrollable List in Excel

When you have large amounts of data and don't want to scroll the entire screen, you can create a scrollable list:

●      Collect your data.

●      Go to the Developer tab, click Insert and select Scroll Bar.

●      Place the Scroll Bar where you want it on the worksheet.

CHAPTER 12: USING EXCEL ON A SMARTPHONE

Microsoft Office includes a number of core products from the Microsoft Corporation, and when you purchase and install it on your PC, you will have access to various tools, including Excel. It is possible to purchase Office, in digital or physical version, via the official website office.com or other online and traditional stores. Currently, Microsoft offers Office 2019 and Office 365, with the Microsoft 365 (formerly Office 365) package available with a range of new features. If you don't already have Office installed, you can choose to upgrade to the 365 version.

Sharing an Excel File Using the Mobile App Once you've completed your Excel spreadsheet, you can share it by following these simple steps: • Tap the share icon located at the top right of the Excel app. • Choose whether to send the file as an email attachment or share it via OneDrive. By opting for OneDrive, you can collaborate with others by entering their email addresses or sharing a link via SMS or social platforms such as Facebook or WhatsApp. OneDrive is the cloud storage service offered by Microsoft. If you choose to send as an attachment, a box will appear where you can enter the recipients' email addresses. Enter one or more addresses and send the file.

Saving an Excel file on your smartphone The Excel smartphone app saves files automatically by default, but if you want to manually save a file with a specific name, do the following: • Tap the three vertical dots (ellipses) in the top corner to the right of the Excel sheet. • Select the "Save" option. • Enter the desired name for the Excel file. • Tap "Save" to choose the location - it can be on your phone or in your OneDrive account.

Where to buy an Office package with Excel You can buy and install Office in several places, such as: ● The official Microsoft Office website. ● Amazon (www.amazon.com). ● Walmart.

Other software providers are also available both online and in physical stores. For example, up www.microsoft.com, Microsoft offers Microsoft 365 with advanced features. On Amazon, you can search for "Software" in the search bar, while on Walmart, use the search field to find available versions of Office. If you would like help purchasing and installing, each platform's seller or customer support will assist you.

Get Excel for Free from Microsoft Microsoft has launched a free option to use Excel online, albeit with some limitations. Other Microsoft software such as Word, Outlook, OneNote and Skype are also available at no cost. To access Excel for free: Visit the Office site preferably using the Chrome browser and connect your computer to the Internet. If you don't see the "Sign up for Office for free" option, click "Sign in" and then select "Sign up." After entering your email address, which can be Gmail, Yahoo or any other, you will set up your Microsoft account. Once you log in to the Office online platform, you can access Excel and start working on your data.

CHAPTER 13: THREE-LEVEL EXCEL: THE BEGINNER

For beginners, Excel may initially seem complicated, but by following a gradual method and acquiring the fundamental concepts, it will transform into a very useful tool.

Add your most used tasks to the toolbar for easier access

To optimize your use of Excel, you can add your most frequent tasks to the toolbar, making it easier to access repetitive actions and increasing overall efficiency. This operation allows you to save precious time by avoiding having to search through the various menus. Customizing the toolbar, inserting the main activities, makes the workflow more intuitive and flexible, adapting it to your needs. This feature offers a significant advantage for those who want to optimize their work process and increase daily productivity. To add a task to the toolbar, simply go to the "View" tab, locate "Toolbar" and choose "Customize." From there, you can drag the desired tasks into the bar, making them immediately accessible.

Data filtering

Data filtering in Excel is a powerful tool that allows you to analyze and focus on specific information, ignoring what is not relevant. To apply a filter, select the data area and then go to the "Data" tab, where you will find the "Filter" option. Once activated, arrows will appear next to the column headings, allowing you to choose the values ​​you want to view or sort. Excel also offers advanced filters, such as the "Color Filter" or "Text Filter", to further narrow your search. The filter can be removed by repeating the operation in the "Data" tab. This feature is ideal for large data sets, facilitating detailed analysis and improving decision making.

Incorporate dynamic headers and footers

Adding dynamic headers and footers to an Excel document is critical to ensuring a consistent and professional presentation. This feature allows you to insert information such as the document title, page numbers or the current date, applying it uniformly across all pages of the worksheet. To insert these elements, go to the "Page Layout" tab and select "Header and Footer". You will be able to customize the content, also inserting graphic elements or changing the text format. The use of these elements is particularly useful in long documents or detailed reports, facilitating navigation and improving the communication of information.

Define the print area

Setting the print area in Excel allows you to delimit the portion of data to be printed, avoiding including superfluous information. This tool is essential for optimizing the presentation of reports or complex data. To define the print area, select the relevant part of the worksheet, then go to the "Page Layout" tab and click on "Set print area". The selected area will appear in preview mode, allowing you to further adjust the print settings. This feature is useful not only for reducing printed content, but also for logically and clearly organizing data across multiple pages, improving the readability and presentation of the document.

Special paste options

Excel's special paste options let you transfer data between cells precisely, preserving formatting or selecting only certain attributes, such as values ​​or formulas. When you copy data, you can choose between different pasting modes, customizing the transfer according to your needs. To use these options, after copying a cell, access the "Paste Special Options" function and select the mode you prefer, such as "Values ​​Only" or "Formatting Only". This feature is particularly useful when managing data from different sources, allowing you to maintain consistency and accuracy when transferring information.

Group and ungroup columns

Grouping and ungrouping columns in Excel are useful tools for temporarily hiding detailed data and focusing on more relevant information. This feature allows you to collapse a group of columns to reduce visual clutter and facilitate data analysis. To group columns, select the columns you want to group, right-click, and choose "Group." To expand or collapse the group, use the control icon that appears above the grouped column. This method simplifies the management of complex worksheets and makes it easier to focus on the essential elements.

Document security

Excel file protection is essential to ensure data security and confidentiality. You can protect documents by using strong passwords and encrypting the contents to prevent unauthorized access. Additionally, Excel lets you limit permissions to view or edit data and track changes you make to the document. Saving files in safe locations or on protected servers provides additional security. This attention to document protection is crucial to preserving privacy and preventing data loss or corruption, especially in sensitive work environments.

Search for dependent and previous cells

The precedent and dependent cell search function in Excel helps you understand how the data in cells is related to each other. This feature is useful for tracing the sources of data used in a formula (precedents) or cells that depend on a specific formula (dependents). To use this feature, go to the "Formulas" tab and select "Search Previous" or "Search Employees." This analysis is critical for maintaining the integrity and consistency of formulas in complex spreadsheets, making it easier to resolve errors and improving information management.

Data validation in drop-down menus

Drop-down data validation allows you to limit the input options in a cell, ensuring data integrity and consistency. To apply this feature, select the cell, go to the "Data" tab and select "Data Validation". From here, you can define validation criteria, such as custom lists or integers. You can also customize error messages to alert the user if they enter incorrect data. This feature is ideal for avoiding errors and ensuring that only valid data is entered, improving the reliability of the spreadsheet.

Formatting text in columns

Formatting text in columns is useful for organizing data and improving its readability. It can be especially beneficial when importing unstructured or concatenated data from other sources, such as CSV files. To separate the text into columns, select the relevant column, access the "Data" tab and click on "Text into columns". Follow the wizard to choose the appropriate separator and confirm the formatting. This process makes the data more manageable, making it easier to analyze and understand.

Creating charts

Charts in Excel provide a visual representation of data, making it easier to interpret the information. To create a chart, select the data and go to the "Insert" tab, where you can choose the most suitable chart type, such as bar, pie or line charts. Once inserted, customize the graph by adding titles, labels and changing the visual appearance to improve communication of your results. Charts simplify data analysis and make presentations and reports more effective and impactful.

CHAPTER 14: EXCEL AT THREE LEVELS: THE INTERMEDIATE USER

Microsoft Excel is a powerful tool for managing spreadsheets and offers intermediate users a wide range of advanced features that help them manage, analyze and visualize data more effectively. If you're already familiar with the basics of Excel, delving into its advanced features can greatly improve your experience, allowing you to tackle more complex and sophisticated tasks. Here's an overview of what you might explore at the intermediate level of Excel:

Advanced data analysis

One of the key capabilities for intermediate users is to perform more in-depth data analysis. Tools like pivot tables allow you to dynamically aggregate and summarize large amounts of data. The "SEARCH" and "IF" functions become precious allies for extracting and finding specific information in your datasets, significantly improving data analysis.

Handling large datasets

Excel can handle large amounts of data, and intermediate users can take advantage of advanced sorting, filtering, and structured data management features, such as organized tables. These tools make it easier to work with large data sets, making information management more efficient and organized.

Advanced formulas and custom functions

At this level, you can explore advanced formulas such as array formulas or financial functions, gaining more control over complex calculations. Furthermore, thanks to Visual Basic for Applications (VBA), Excel allows you to create custom functions, automating repetitive tasks and expanding the possibilities of using the software.

Creating dynamic charts

To visually represent data in an interactive way, dynamic charts offer the ability to manipulate and update data directly within the chart. This makes it easier to analyze results and improves the communication of information during presentations or reports.

"What-If" analysis and scenario management

With the “What-If” feature you can explore what-if scenarios, adjusting variables in real time to see how the results change. The "Scenario Manager" allows you to compare different situations or data sets, providing a powerful tool for making strategic decisions based on different perspectives.

Collaboration and data sharing

Intermediate users can delve deeper into the spreadsheet sharing features. You can protect specific sections of your file, track changes and manage access permissions in a more sophisticated way, promoting safer and more efficient collaboration.

Statistical analysis

For those interested in statistical analysis, Excel offers tools such as the AVERAGESE and CONT.SE functions, which allow you to analyze data based on specific criteria. These tools allow you to extract meaningful and detailed insights from datasets, facilitating data-driven decisions.

Import and connection with external sources

An intermediate user can take advantage of Excel's ability to import data from external sources such as databases, text files, or websites. This opens up the possibility of integrating data from different sources, making Excel a central point for collecting and analyzing information.

Excel keyboard shortcuts

Knowing keyboard shortcuts is essential for speeding up your workflow in Excel. For example, "Ctrl + arrows" allows you to quickly move between cells, "Ctrl + C/V" handles copy and paste operations, while combinations like "Alt + =" automatically calculate the sum of adjacent cells. Using these shortcuts improves your productivity and saves you valuable time.

Tricks for advanced Excel use at intermediate level

An intermediate user can perform more advanced tasks, handling complex spreadsheets, sorting and filtering data efficiently, and using functions like SUMIF or IF for in-depth analysis. Creating custom charts and pivot tables, as well as automating tasks with macros, is part of the skill set of an intermediate user. With the use of analysis tools such as data tables and "What-If" analysis, you can explore what-if scenarios to make more informed decisions.

Creation and management of worksheets

An intermediate user knows how to create complex worksheets, organize them into workbooks, and manage several sheets within the same file. Sorting and filtering data allows you to better structure the information, making it easier to analyze.

Automation of repetitive tasks

Thanks to macros, intermediate users can automate repetitive tasks, reducing the risk of errors and optimizing time spent on mechanical tasks. Recording macros allows you to simplify complex operations and improve the precision of your work.

Data management and cleansing

Intermediate users are able to sort, filter, and clean data using search, duplicate removal, and advanced search tools. These capabilities make data management easier and more organized, especially when dealing with large volumes of information.

Import, export and connections

Another benefit for intermediate users is the ability to import and export data from different sources, aiding Excel's integration with other platforms and making it a versatile tool for managing information from various sources.

CHAPTER 15: EXCEL AT THREE LEVELS: THE ADVANCED USER

For more experienced users, Excel proves to be a valuable tool for tackling complex challenges related to data analysis and information management. At this level, Excel's advanced features provide sophisticated tools to perform in-depth analysis, automate complex processes and represent data in a highly personalized way. Below are some particularly useful features for those who have advanced mastery of the software:

In-depth data analysis:
Advanced users can leverage Excel's capabilities for advanced data analysis, such as trend analysis, forecasting, and complex statistical functions. These tools allow you to extract relevant information from large data sets, facilitating strategic decisions.

Process automation with macros:
Thanks to Visual Basic for Applications (VBA), Excel allows you to create advanced macros to automate complex tasks. More experienced users can further customize Excel features to suit specific operational needs, reducing work time and improving efficiency.

Creating interactive dashboards:
Advanced users are able to design dynamic, custom dashboards, using interactive charts, form controls, and advanced formatting techniques. These tools allow data to be represented in a clear and accessible way, facilitating the interpretation of results.

Complex “what-if” analysis:
“What-If” features are further leveraged at an advanced level, allowing you to explore complex scenarios by adjusting multiple variables at once. This allows you to evaluate the impact of specific changes and make more informed decisions based on different scenarios.

Advanced statistical analysis:
Excel offers a range of tools for advanced statistical analysis, such as analysis of variance, regression, and hypothesis testing. These tools allow expert users to perform more detailed analyzes and obtain more accurate statistical results.

Advanced data management and cleansing:
Using tools like Power Query, advanced users can manage and cleanse data efficiently. This allows you to import, transform and load data from different sources, making it easier to integrate and prepare data for analysis.

Connection with external data sources:
Excel allows users to connect to a wide range of external data sources, such as databases, web services, and other applications. This integration capability offers new opportunities to combine and analyze data from different sources, expanding the possibilities for using the software.

Creating complex financial models:
For those working in the financial industry, Excel is an advanced tool for creating complex financial models, used for analyzing investments, evaluating projects, and planning detailed budgets. These models offer a detailed overview of financial dynamics.

Advanced security and data protection:
Advanced users understand how to implement sophisticated security measures, such as permission management and encryption of sensitive data. This ensures the protection of confidential information and increases security when collaborating with other users.

Network and cloud collaboration:
Excel offers advanced features for real-time collaboration, especially through platforms like Microsoft 365. Advanced users can leverage these features to work as a team on shared spreadsheets, facilitating group work and improving efficiency.

For advanced users, Excel evolves from a simple spreadsheet to a powerful, flexible environment that lets you manage complex projects, analyze detailed data, and make informed decisions based on sophisticated analytics. In this context, Excel becomes a complete tool for advanced data management and analysis.

Advanced formulas and functions in Excel

Advanced formulas and functions are a crucial element for those who want to master Excel at the highest level. These tools allow you to perform complex calculations, automate advanced processes and perform in-depth data analysis, providing powerful means to manage information precisely and efficiently.

The search and reference functions such as VLOOKUP, XLOOKUP, INDEX and MATCH, allow you to quickly extract specific information from large data sets. These functions are essential for the analysis of complex databases, as they make retrieval of relevant information immediate.

Among them database functions, such as SUMDB and AVERAGEDB, Excel offers useful tools for managing and analyzing structured data like a database, simplifying calculations specific to large, organized data sets.

The advanced logical functions, such as nested IF and IF formulas, are used to handle complex conditions in formulas. This allows you to perform calculations based on multiple criteria, optimizing the automation of decisions and results.

As for the advanced mathematical functions, tools such as RADQ, POTENZA and RADQ.CUBICA offer the ability to perform complex mathematical calculations within Excel, expanding the software's numerical analysis capabilities.

The advanced text functions, such as FIND, REPLACE, and CONCATENATE, allow you to manipulate and manage textual data in a sophisticated way, making them essential for cleaning and organizing datasets containing text strings.

In the context of date and time functions, Excel offers tools like MONTHDATE and DATEVALUE, which make calculations based on dates and times easier. These tools are particularly useful for time analysis and project planning.

The advanced statistical functions, such as MEDIA.SE, CONT.SE and DEV.ST, allow you to perform detailed statistical analyzes on data, offering a wide range of possibilities for those who work with quantitative analyzes and research.

For those who work in the finance field, the advanced financial functions such as VA, VF and IRR allow you to precisely calculate investments, cash flows and other complex financial aspects, improving economic analysis.

Finally, the engineering functions, such as ESAT and POTENZA.DIST, offer tools for technical and scientific analysis, while the data summary functions, such as SUBTOTAL and DATASUMMARY, let you aggregate large data sets effectively, making it easier to create detailed reports.

Acquiring advanced skills in using Excel formulas and functions opens up a higher level of use of the software, allowing you to tackle complex data management challenges and perform in-depth analyses. Mastering these tools represents a fundamental step in using Excel in a professional and analytical way.

CHAPTER 16: EXCEL DATABASE

Excel is a powerful data management tool, and databases in Excel offer a wide range of features to analyze, filter, and summarize data efficiently. Below we'll explore some of the most used database functions in Excel, including COUNT. NUMBERS, COUNT. VALUES, MAXIMUM, AVERAGE, MINIMUM and PRODUCT.

Number counter

The COUNT function. NUMBERS counts the number of cells in a range that contain numbers. For example:

This formula counts how many cells in the range A1 to A10 contain numbers.

Counting values

The COUNT function. VALUES counts the number of cells in a range that contain values. It can be useful to exclude blank cells. Example:

This formula counts how many cells in the range B1 to B15 contain values ​​(excluding blank cells).

Maximum (maximum)

The MAX function returns the maximum value in a range of cells. Example:

This formula returns the maximum value in the range from C1 to C20.

Media

The AVERAGE function calculates the average of values ​​in a range. For example:

This formula calculates the average of the values ​​in the range D1 to D25.

Minimum

The MINIMUM function returns the minimum value in a range of cells. Example:

This formula returns the minimum value in the range E1 to E30.

Product

The PRODUCT function multiplies the values ​​in a range of cells. For example:

This formula calculates the product of the values ​​in the range F1 to F10.

These functions are just a taste of Excel's data management capabilities. They can be used individually or combined into more complex formulas to analyze and interpret a wide range of data in Excel worksheets.

CHAPTER 17: THE MUST HAVE FEATURES OF 2025

The MOD function and how to use it

The MOD function in Excel allows you to obtain the remainder of a division between two numbers. The result will have the same sign as the divisor. For example, MOD(10,3) returns 1. This function belongs to the category of mathematical and trigonometric functions, and is also known as a modulus operation. This process is called "module", from which the acronym MOD derives.

The ROUND function and how to use it

The ROUND function in Excel allows you to round a number to a specific number of decimal places, either to the left or to the right of the decimal point. The goal is to return a number with a specific number of digits. The syntax of the function is =ROUND(number, num_digits), where "number" is the value to be rounded and "num_digits" indicates the number of digits desired. You can also use the MROUND function to round to the nearest multiple. Other related functions include the FLOOR function, which rounds down to the nearest multiple, and the ROOF function, which rounds up. The TRUNC function, however, eliminates decimal digits, while the INT function returns only the integer part of a number.

The SORT function and how to use it

To use the SORT function in Excel, select the range of cells you want to sort, go to the Data tab in the Ribbon and click the Sort command. From here, you can choose whether to sort the cells in ascending or descending order. If you prefer to sort the data within a grid, you can use the SORT BY function, which allows greater flexibility in managing columns. The SORT function refers to a column index number and is useful for rearranging data in a table.

The SUM function and how to use it

The SUM function allows you to add one or more numbers in a range of cells. You can select a blank cell below the range you want to sum and use the AutoSum button to automatically insert the function. An alternative method is to use the ALT + = keyboard shortcut. You can also manually enter the function with the syntax =SUM(number1, [number2]), where "number1" is required, and additional arguments are optional.

The ROUNDUP function and how to use it

The ROUNDUP function rounds a number up to a specified number of decimal places. The syntax is =ROUNDUP(number, num_digits), where "number" represents the value to round up, and "num_digits" indicates the number of digits to consider. The function rounds negative numbers to the left of the decimal point and positive numbers to the right.

The RANDBETWEEN function and how to use it

The RANDBETWEEN function returns a random integer between two specified values. Every time the worksheet is recalculated, a new random number is generated. You can insert the function into one or more cells by selecting the range and using the Ctrl + Enter key combination. If you want to generate random numbers that don't change with recalculation, enter the function and press F9 to convert the formula to a fixed value.

Mathematical functions in Excel

In Excel, mathematical functions are entered using the "=" symbol followed by the desired numeric values ​​and mathematical operators. The main operators include the sign "+" for addition, "-" for subtraction, "/" for division, and "*" for multiplication. Common math functions include SUM(), SUMIF(), COUNT(), AVERAGE(), and others.

The ROUNDDOWN function and how to use it

The ROUNDDOWN function rounds a number down. The syntax is =ROUNDDOWN(number, num_digits), where "number" is the value to be rounded and "num_digits" indicates the number of digits to consider. If "num_digits" is greater than zero, the number is rounded down to the specified decimal places; if it is equal to zero, it is rounded to the nearest integer.

The SUMIF function and how to use it

The SUMIF function adds cells that satisfy a certain condition. Can be used with numbers, dates and texts. The syntax is =SUMIF(range, criteria, [sum_range]), where "range" is the set of cells to evaluate, "criteria" defines the condition, and "sum_range" (optional) indicates the cells to sum. This function is useful for filtering the data before performing the sum.

The SUMIF function and how to use it

The SUMIF function allows you to add cells that satisfy multiple criteria. The syntax is =SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...), where "sum_range" is the range to sum, and "range1" and "criteria1" are the first interval and its criterion. You can add additional ranges and optional criteria. The SUMIF function supports logical operators such as "=", "<", ">", "<>", and allows you to perform calculations on cells that meet complex criteria.

CHAPTER 18: TIPS AND TRICKS FOR 2025

Rename a sheet with a double click
An easy way to rename sheets in Excel is to double-click the name of the sheet you want to change, allowing you to rename it immediately.

Change the uppercase and lowercase of a text
There are specific functions for changing the case of letters in a text. For example, the UPPER function makes all text uppercase, the LOWER function makes it lowercase, and the PROPER function makes the first letter of each word uppercase.

Join text with the & symbol
By using the & symbol, you can combine the contents of multiple cells into a single cell. For example, to merge cells A2, B2, C2 and D4, we will get the text "JOYUSA23F" in cell F2.

Show leading zero in Excel
When a number starts with a zero, Excel automatically removes it. To keep the leading zero, simply add an apostrophe ('), before the zero, as shown in the following table.

Extend a formula downward
To apply a formula to multiple cells, drag the small square in the lower-right corner of the cell containing the formula to the other cells you want.

Change the behavior of the Enter key
By default, pressing Enter moves to the cell below. You can change this setting so that Enter moves the cursor in a different direction. To do this:

Go to File, then Options.

Select the Advanced tab and look for the Edit option.

Quick selection of formulas
This feature saves you time entering formulas. When you start writing a formula, Excel offers you a list of suggested formulas; you can scroll and quickly select the one you want with the Enter key.

Disable the Excel splash screen
If you don't like being greeted by the splash screen every time you open Excel, you can disable it like this:

Go to File and then Options.

Choose General, then in Startup Options, deselect the splash screen.

Finally, click OK.

Enter the current date and time
You can insert the current date and time into the worksheet using the NOW function, which fetches the date and time data from the system.

Use the status bar
By right-clicking on the status bar, you can access several features that you can add to customize it to suit your needs.

Delete cells with error values
To remove cells that contain errors, follow these steps:

Go to the Home tab, then Edit and select Find and Replace.

In the "Go to Special" dialog box, select Formula and then check the Errors option.

Strikethrough text in Excel
To strikethrough text in Excel, select the desired cell and press the CTRL+5 key combination.

Clear formatting
If you want to remove formatting from one or more cells, follow these steps:

On the Home tab, go to Edit.

Select Clear and then click Clear Format.

Share Excel files online
You can share Excel files online, allowing multiple people to collaborate on the same document. Plus, you can save your files to OneDrive to access them from anywhere. To share an Excel file online:

Go to the File menu, then Share.

Choose an option from Invite people, send via email, or save to OneDrive.

Use: To save your files on OneDrive, you must have a Microsoft account associated with your computer.

Free add-ins for Excel
There are various free add-ins that you can use in Excel to improve your productivity:

●      ASAP utilities developed by Andrew Engwirda

●      Autosafe developed by Jan Karel

●      Findlink developed by Bill Manville

●      Menurighter developed by Doug Glancy

●      Name manager developed by Jan Karel

●      Xlg favorites developed by Ken Puls

CHAPTER 19: EXCEL IN EVERYDAY LIFE

Excel is not only a professional tool, but also a valid support for managing and optimizing everyday life. Thanks to its powerful features and flexibility in data management, it becomes a precious ally for simplifying daily activities and improving personal organization.

Manage costs with Excel

Keeping expenses under control is essential to maintaining economic stability, both in the personal and business spheres. Excel offers advanced tools for tracking and analyzing costs. You can use it as your primary financial management tool by creating a detailed budget sheet. Start by identifying all your monthly expenses, such as bills, groceries, subscriptions and other items. Excel formulas allow you to automatically calculate totals, giving you a clear overview of your finances.
Record your expenses monthly in an Excel sheet, dividing them into specific categories and updating them regularly. Thanks to the organized structure of the information, you will be able to easily identify areas where you can reduce expenses. Use line or bar graphs to analyze your spending over time to see monthly fluctuations and identify any patterns. This will help you better plan your future expenses and take preventative measures.
Set up automatic alerts in Excel to get notified when you exceed certain spending limits. With conditional formatting, you can highlight risks or budget overruns, allowing you to take action quickly. Classify expenses into fixed and variable, and create formulas to calculate the percentage of each on the total. This will help you better understand the composition of your expenses and identify where you can save.
Generate detailed monthly reports using dynamic charts and tables to get a clear view of your finances. These reports allow you to easily share information with family or business partners if necessary. If you want to make the process even easier, use Excel's pre-built budget and expense tracking templates and customize them to fit your needs.

Consolidate data from different sources with Excel

The ability to quickly organize and access data is essential for making informed decisions. Excel allows you to consolidate data from different sources into a single spreadsheet, making information management much easier. Here's how to do it.
Before you begin, identify the data sources you want to consolidate, such as separate Excel files, CSV files, or external databases. Use the data consolidation function, found in the "Data" tab under "Consolidate Data", to specify the sources and choose the most suitable type of consolidation (sum, average, count, etc.). Define the data ranges to consolidate, so Excel can automatically update the data without having to reconfigure the table every time.
PivotTables are very useful tools for analyzing consolidated data. Create one to get a detailed and dynamic view of your results. If your data changes frequently, turn on Excel's automatic refresh, so the consolidated data will be updated every time you open the worksheet. If you use data from separate sheets, you can take advantage of external references to connect the original data and keep it updated automatically. Once you've consolidated your data, customize your sheet to make it more readable, using colors, headings, and conditional formatting to highlight key information.

Access data via the Internet with Excel

By connecting Excel to the Internet, you can access real-time updated data, such as financial information, market quotes, and more, directly in your spreadsheet. This integration allows you to transform Excel into a dynamic tool for accessing online resources, ensuring that your analyzes are always based on fresh, accurate data.
Just a few clicks are enough to connect Excel to web resources, making the program an open window on the world of digital information. For example, if you're working on a financial report, you can enter the latest stock prices without leaving the worksheet. Update data in real time to get a complete and up-to-date view of your financial performance. This will save time and eliminate the need to manually search for data across different platforms, allowing you to focus solely on the analysis.
Additionally, web integration provides a more flexible environment for data analysis, allowing you to explore new sources of information and adapt your analyzes based on project needs.

Data security in Excel

In an era where data security is increasingly central, Excel goes beyond the management of simple spreadsheets, offering advanced tools to protect, maintain the integrity and guarantee the confidentiality of the information processed.
Excel provides robust options to protect sensitive data, such as file encryption, passwords, and access restrictions to ensure that only authorized users can view or modify certain data.
With advanced access management, you can monitor who has access to your files and what changes are made. With the use of permissions and reviews, user activity can be tracked and the security of the information processed can be improved. Excel also aligns with data privacy and security regulations, with features like data masking and revision management ensuring compliance with applicable laws.
Excel's advanced capabilities extend to defending against external threats. Integration with antivirus software and the ability to detect potential attacks further protects your data from cyber intrusions. Furthermore, automatic backup and restore features ensure continuity of operations, even in the event of data loss or corruption.
Finally, Excel offers advanced options for secure password management, such as credential complexity and expiration policies, which strengthen the overall security of file access.

CHAPTER 20: EXCEL AND ARTIFICIAL INTELLIGENCE

In recent years, the introduction of artificial intelligence (AI) in various sectors has profoundly transformed the way we work and manage data. One of the most significant examples of this transformation concerns the use of Excel, where artificial intelligence has elevated the program from a simple calculation tool to an advanced platform for data analysis, visualization and prediction. Integrating artificial intelligence into Excel opens up new possibilities, making it a more sophisticated tool for making decisions and analyzing data. With artificial intelligence, Excel does not just organize data, but automates repetitive tasks, suggests management strategies and simplifies data cleaning, improving the reliability of analyses. AI capabilities help you spot patterns and trends in your data and create graphs and charts that make your results more understandable.

Additionally, Excel allows you to make accurate predictions using historical data, thanks to sophisticated machine learning algorithms that can extrapolate future trends. This skill is especially useful for tasks like financial planning and sales management. AI not only simplifies data management, but also enhances analysis and visualization, allowing users to obtain reliable predictions to support informed decisions. As artificial intelligence evolves, its role within Excel will become increasingly crucial for those seeking efficient data-driven workflows.

Understand the fundamentals of artificial intelligence

To make the best use of Excel's advanced capabilities, it is important to understand the fundamental principles of artificial intelligence. Artificial intelligence mimics human intelligence in machines and includes technologies such as machine learning, natural language processing, and computer vision. These tools allow for in-depth analysis, pattern recognition and accurate predictions. In Excel, AI automates repetitive tasks, identifies anomalies and trends, and supports predictive analytics through techniques like decision trees and neural networks. Natural language processing helps in analyzing textual data, while computer vision allows you to process images and videos. Thanks to the integration of AI, Excel can predict future trends and facilitate strategic business planning.

Overview of Excel's AI features

Artificial intelligence has significantly expanded Excel's capabilities, making it a more powerful tool for analyzing and visualizing data. Excel can now automatically identify patterns and trends in your data, making it easy to create interactive visualizations that update in real time. AI capabilities enable advanced statistical analysis and predictions, as well as support text processing and translation to facilitate global collaboration. The integration of artificial intelligence makes Excel a more versatile tool, allowing users to gain insights and make decisions based on precise data.

Data analysis and visualization using Excel and AI

In the modern digital environment, the ability to analyze and visualize data is crucial in many professions. Thanks to the integration of artificial intelligence, Excel has become an even more powerful tool for managing large amounts of data. AI helps manipulate data, extract insights, and create compelling visualizations that improve understanding of results. One of the most significant aspects concerns forecasting, where AI algorithms provide more precise results, which are fundamental for decision-making. Additionally, Excel simplifies data cleansing and enables advanced visualization through interactive graphs, charts, and dashboards, giving users the ability to communicate results effectively.

Creating interactive dashboards with Excel and AI

Excel, thanks to AI, allows you to create interactive dashboards that improve data analysis. Artificial intelligence algorithms allow you to automate the forecasting process, saving time and improving the accuracy of the analyses. Whether you're a beginner or an expert user, AI features in Excel give you tools to make the most of your data and create dashboards that facilitate decision-making based on real information.

AI-powered forecasts in Excel

Accurate forecasts are key to making strategic decisions. Excel, thanks to integration with artificial intelligence, can use machine learning algorithms to analyze historical data and make reliable predictions. Users can forecast sales, manage inventories and anticipate market trends, thus improving business planning. Excel AI models can quickly adapt to changes, ensuring up-to-date and accurate forecasts, especially in dynamic market environments.

Evaluate and improve forecast accuracy

Evaluating the accuracy of predictions is essential to ensure the reliability of predictive models. Excel, through AI, offers tools to measure the accuracy of forecasts and continuously improve models using updated data. Incorporating external factors and adapting models in real time allows for increasingly accurate predictions, improving the ability to make data-driven decisions.

Incorporating external factors into forecasting models

To improve forecast accuracy, it is important to include external factors in models, such as economic or weather trends. Excel, using artificial intelligence, allows you to integrate these factors into predictive models, improving the precision of projections. This integration allows a more complete vision of the future and helps you make more informed decisions.

Leverage artificial intelligence for better data visualization

Traditional data visualization techniques often fail to handle complex data sets. Thanks to artificial intelligence, Excel can analyze large amounts of data, making it easy to create clear, concise visualizations. AI helps detect hidden patterns, generate predictions and visualize them interactively, improving the ability to make informed decisions.

Create dynamic, interactive data visualizations

AI in Excel helps you automate repetitive tasks and create dynamic, interactive visualizations. Thanks to AI algorithms, users can generate predictions and create predictive models that respond in real time to user input, improving the experience and facilitating data analysis.

Integrating machine learning into data visualization

Machine learning, combined with Excel, offers the ability to automate data cleansing and uncover hidden patterns, allowing you to create interactive and accurate visualizations. This integration allows users to explore data more deeply and communicate results more effectively.

Introduction to AI-based analytics

Artificial intelligence allows us to analyze large volumes of data more efficiently, detecting trends and anomalies that escape traditional analysis. In Excel, AI improves the ability to visualize complex data and create predictive models, giving users powerful tools to make strategic decisions.

Ethical implications of artificial intelligence in Excel

The use of artificial intelligence in Excel raises ethical questions related to privacy and transparency. Users should be aware of data security risks and limitations of AI algorithms. While AI can automate many tasks, it is important to consider how this technology will impact job roles, encouraging the integration of human skills and digital tools to avoid replacing human labor.

CONCLUSION

In conclusion, Excel 2025 is a comprehensive and valuable resource for anyone looking to deepen their understanding of Microsoft Excel, from basic to advanced levels. Through a detailed analysis of its many features, the book guides readers in discovering how to best use this tool for managing data, performing complex calculations, and visualizing information clearly and effectively. From creating simple tables to automating business processes through macros, the text provides the skills needed to tackle any analytical challenge efficiently.

The value of Excel 2025 lies in its ability to make even the most complex concepts accessible through clear language and a practical approach, enriched with examples and step-by-step instructions. Readers will not only gain mastery over Excel's core functions, but also develop a strong understanding of advanced techniques for data analysis, automation, and collaboration in professional environments.

With its versatility and ability to adapt to a wide range of needs, Excel remains an essential tool for professionals and students alike. Excel 2025 is positioned as the definitive guide for fully harnessing its potential, helping anyone work with greater accuracy, efficiency, and expertise.