An Introduction to Microsoft Excel | Basics Knowledge + Components + Examples

- Written by Puneet

The truth is that before you go for a job interview, you must have basic knowledge of Microsoft Excel. From an accountant to a receptionist, human resources to administration departments all use Microsoft Excel.

learn-excel-basics-introduction

It is not only limited to large companies, small entrepreneurs and college students are using it for their day-to-day work. That’s something that you can’t skip.  To get a job, learning basic Excel tasks (at least some) is a must in today’s era, that’s a firm truth.

And, to help you with this I have compiled this guide. This guide will help you to learn all those basics using some examples. And some of the most important beginner’s tutorials. So, without further ado let’s get down to the business.

Latest Excel Tutorial

Top 10 Benefits of Microsoft Excel

Introduction to Microsoft Excel

There are many spreadsheet programs but of all of them, Excel is the most widely used. People have been using it for the last 30 years and throughout these years, it has been upgraded with more and more features.

The best part about Excel is, that it can apply to many business tasks, including statistics, finance, data management, forecasting, analysis, tracking inventory & billing, and business intelligence. Following are the few things that it can do for you:

  • Number Crunching
  • Charts and Graphs
  • Store and Import Data
  • Manipulating Text
  • Templates/Dashboards
  • Automation of Tasks
  • And Much More…

A spreadsheet is a document in an application like Microsoft Excel, Google Sheets, or OpenOffice Calc. It is designed as a grid of cells organized in rows and columns. The primary purpose of a spreadsheet is to manage and organize data, which can be numerical, textual, or a combination of both.

Each cell in the spreadsheet can hold individual data points, and these cells can be further grouped into rows and columns for higher-level organization. One of the critical features of spreadsheets is their built-in mathematical functions.

These include basic arithmetic operations like addition and multiplication and more complex tasks for statistical analysis, financial calculations, etc.

This makes spreadsheets invaluable for simple budget tracking, data analysis, and prediction modeling.

In addition, spreadsheets often provide tools for graphing or visualizing data in various ways, such as bar charts, pie charts, and line graphs. This can help users understand trends, patterns, and relationships within the data.

Major Components of a Spreadsheet Program

A spreadsheet consists of many useful components:

  • Cells: Cells are the most basic but powerful units of a spreadsheet. They hold individual data points.
  • Rows and Columns: Cells are organized into rows and columns. Rows are horizontal and are usually numbered, while columns are vertical and are generally labeled with letters.
  • Ranges: A group of cells. Ranges can be named for easy reference in formulas.
  • Formulas: These are expressions that perform operations on data. They can involve mathematical operations, text manipulation, and more.
  • Functions: Preset formulas in spreadsheets. They perform specific calculations using the data in cells.
  • Charts and Graphs: Visual representations of the data in the spreadsheet.
  • Pivot Tables: Pivot Tables are a tool for reorganizing and summarizing selected columns and rows of data in a spreadsheet.

Common Spreadsheet Applications

Some popular spreadsheet applications include Microsoft Excel, Google Sheets, and Apple’s Numbers. These applications provide a wide range of data processing, analysis, and visualization features.

Microsoft Excel is the most well-known and widely used spreadsheet application. It offers many tools and features, including pivot tables, conditional formatting, and an extensive library of functions.

Google Sheets, on the other hand, is web-based and allows for real-time collaboration, making it a popular choice for team projects.

Apple’s Numbers is known for its user-friendly interface and aesthetic design, which integrates well with other Apple software.

Workbook and Worksheet

In a spreadsheet program, a file is referred to as a ‘workbook’.

A workbook can contain multiple ‘sheets’ – also known as ‘worksheets’ – that can store different sets of data. These sheets within a workbook offer a way to organize information in a structured manner, allowing users to segregate data based on their specific requirements.

For instance, one sheet could be used to maintain sales data, while another could be used to track inventory.

Each sheet operates as an individual entity, but they all exist within the same workbook, allowing easy navigation between these sheets.

An essential feature of workbooks is the ability to link and reference data across different sheets.

This interconnectivity empowers users to create complex data models where changes in one sheet can dynamically update related data in another sheet, thus ensuring the data’s consistency and accuracy.

Word Processors Vs. Spreadsheets Applications

While word processors are powerful tools for creating, editing, and formatting text-based documents, they need to gain the computational and data management capabilities of spreadsheets. Spreadsheets are designed to handle numerical data and perform complex calculations effortlessly.

They allow users to organize, analyze, and visualize data effectively, which isn’t possible with word processors.

Additionally, spreadsheets provide dynamic data manipulation features like pivot tables and the ability to reference data across different sheets, making them ideal for tasks involving large datasets, financial analysis, data modeling, and more.

On the other hand, word processors are better suited for tasks that primarily involve text manipulation, such as writing essays, creating reports, or drafting letters.

Microsoft Excel Window Components

Before you start using it, it’s really important to understand what’s where in its window. So ahead we have all the major components which you need to know before entering the world of Microsoft Excel.

excel-basic-with-window-components

link to download

The three most important components of Excel you need to understand first:

1. Cell

A cell is the smallest but most powerful part of a spreadsheet. You can enter your data into a cell either by typing or by copy-paste. Data can be a text, a number, or a date. You can also customize it by changing its size, font color, background color, borders, etc. Every cell is identified by its cell address, cell address contains its column number and row number (If a cell is on the 11th row and on column AB, then its address will be AB11).

2. Worksheet

A worksheet is made up of individual cells which can contain a value, a formula, or text. It also has an invisible draw layer, which holds charts, images, and diagrams. Each worksheet in a workbook is accessible by clicking the tab at the bottom of the workbook window. In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is accessible by clicking a tab.

3. Workbook

In Excel, a workbook is a file that stores data in multiple sheets. Each workbook is a separate entity. Below are some of the features of an Excel workbook.

  • Each workbook has its name.
  • Each workbook has a specific format.
  • Each workbook needs to be saved in a specific location.

Now, in this tutorial, we look at how to create a new workbook, save it with a name, and then rename it if required.

Create, Add, or Insert a New Workbook in Excel

  • Open Excel Application.
  • Go to the Home (By default, it is open already).
  • Click on the “Blank Workbook” Icon.
  • It will open a new workbook with the name Book1.
insert-new-workbook

Create a New Workbook from Right-Click

If you are using Excel in Windows, you can insert a new workbook from the right-click menu or the new option.

If you are on the desktop, right-click, click the new “New”, and then click on the “Microsoft Excel Workbook”.

create-workbook-from-right-click

And if you are in a folder, click on the “New” dropdown and then click on the “Microsoft Excel Workbook”.

choose-microsoft-exce-workbook

Keyboard Shortcut to Insert a New Workbook

If you have an Excel workbook is already open, and you want to add a new workbook, you can use the keyboard shortcut:

Ctrl + N

When you use the shortcut, it creates a new workbook with the name Book and a number.

Save a Workbook

Once you insert a new workbook, you need to save that workbook, and for this, you need to go to the File Tab > Save As.

save-a-workbook

Once you click “Save As”, click “Browse”.

save-as-browse

After that, select the location where you want to save the workbook, enter the name you want to give to the workbook and click “Save” at the end.

select-location-to-save

Rename a Workbook

Once a workbook is saved on your system, you can right-click on it to then click on the “Rename” option. You can also use the F2 key to edit the name on the workbook.

rename-a-workbook

Once you enter the new name, just hit enter to apply it.

Excel Terminology

1. Active Cell

An Active Cell in Excel is a cell that is currently selected. It will be highlighted by a rectangular box and its address will be shown in the address bar. You can activate a cell by clicking on it or by using your arrow buttons. To edit a cell, you double-click on it or use F2 as well.

2. Column

A COLUMN in Excel is a vertical set of cells. A single worksheet contains 16384 total columns. Every column has its own alphabet for identity, from A to XFD. You can select a column by clicking on its header.

3. Row

A ROW in Excel is a horizontal set of cells. A single worksheet contains 1048576 total rows. Every row has its own number for identity, starting from 1 to 1048576. You can select a row by clicking on the row number marked on the left side of the window.

4. Fill Handle

Fill Handle a small dot present in the lower right corner of the active cell. It helps you to fill numeric values, text series, insert ranges, insert serial numbers, etc.

5. Address Bar

It shows the address of the active cell. If you have selected more than one cell, then it will show the address of the first cell in the range.

6. Formula Bar

The formula bar is an input bar, below the ribbon. It shows the content of the active cell, and you can also use it to enter a formula in a cell.

7. Title Bar

The title bar will show the name of your workbook, followed by the application name (“Microsoft Excel”).

8. File Menu

The file menu is a simple menu like all other applications. It contains options like (Save, Save As, Open, New, Print, Excel Options, Share, etc).

9. Quick Access Toolbar

A toolbar to quickly access the options which you frequently use. You can add your favorite options by adding new options to the quick access toolbar.

10. Ribbon

Starting from Microsoft Excel 2007, all the options menus are replaced with the RIBBON. Ribbon tabs are a bunch of specific option group which further contains the option.

11. Worksheet Tab

This tab shows all the worksheets which are present in the workbook. By default, you will see, three worksheets in your new workbook with the names Sheet1, Sheet2, and Sheet3 respectively.

12. Status Bar

Status Bar in Excel is a thin bar at the bottom of the Excel window. It will give you instant help once you start working in Excel.

Top 25 Excel Skills + Describe Your Excel Skills on Resume

HOW TO USE EXCEL

1. Start Excel Application

Once you install Excel in your system, you can open it from your Start menu. You can search from the search bar and add an icon on the desktop.

start-excel-application

When you click on the Excel application icon, it loads add-ins and then opens the Excel application, showing you the Excel start screen.

excel-application-icon

2. Creating a New Workbook in Excel

Once you open the Excel application, it shows you the backstage from where you need to click on the “Blank Workbook” to open a new blank workbook.

creating-new-workbook

As I said, it will instantly open a new workbook named “Book1”. You need to save the workbook which you have inserted.

new-workbook-opened

3. Save a Workbook

Once you create a new workbook, it’s better to save it right away before you start working on it. To save it, click on the File > Save As.

save-a-workbook

From there, in the “Save As” dialog box, you need to enter the name and then click on the Save.

save-as-dialog-box

If you want to save the File to a different location, click “Browse” and locate the folder where you want to save the workbook.

browse-to-save-to-different-location

4. Insert a New Sheet

There are three (more or less) worksheets by default in each workbook, but you can also insert new worksheets (sheets). And here are multiple ways for this. When you right-click on the sheet tab that you already have in the workbook, there’s an option “Insert”.

insert-a-new-sheet

When you click this option, you get a dialog box to select the type of sheet to insert and then click OK to insert the sheet.

dialog-box-to-select-type-of-sheet

Apart from this, if you want to insert a worksheet, you can use the keyboard shortcut Shift + F11.

5. Delete a Sheet

You can also delete a sheet you don’t need or don’t want to use further. For this, you must right-click and click the “Delete” option.

delete-a-sheet

When you click on this option and delete a sheet or worksheet where you have data, Excel will show you a prompt to ask you for permission to delete it.

delete-a-sheet

And you can also use a keyboard shortcut Alt > H > D > S for this.

6. Enter Data in a Cell

In Excel, you can enter data in a cell using multiple methods, but the easiest way to enter the data is to use the keyboard keys.

enter-data-in-cell

Just select the cell where you want to enter data and type what you want to enter. Once you enter the data, press the “Enter” key to move to the next cell downwards.

Or, if you already have data in the cell and you want to enter the data, you can use the F2 key. It will edit the cell, and you can enter the data.

7. Using Options from Ribbon

In Excel (starting from the Excel 2007 version), the majority of the options that you need to use are listed on the Ribbon. And further on the Ribbon, you have tabs.

using-options-from-ribbon

On each Tab, there are buttons and drop-down lists to use the options. For example, on the Home Tab, you have basic options for formatting and options to work with worksheets, range, and cells.

8. Right-Click Menu

Like other applications, Excel has a right-click options menu listing some of the most important and useful options.

right-click-menu

The right-click menu has a lot of options that you can use quickly from there instead of looking at Ribbon.

There is also a tiny search bar on the top of the menu, which you can use to search for the option you want. This search bar finds options for the entire application instead of only the right-click menu.

9. Cut, Copy, and Paste

When you select a cell or a range of cells, you can find cut, copy, and paste options on the right-click menu. You need to click the option to cut, copy, and paste.

cut-copy-paste-options

Apart from this, you can also use the keyboard shortcuts:

  • Ctrl + X – Cut
  • Ctrl + C – Copy
  • Ctrl + V – Paste

All these options can also be used from the Home Tab > Clipboard.

home-tab-clipboard

10. Opening Excel Options

In Excel, you have options to customize the application. When you go to the File > More > Option, Excel opens the options dialog box where you can find all the options to change.

opening-excel-options

There are further tabs in the options dialog box to explore and find the options you want to change.

11. Changing Font Style and Size

In Excel, you can change the font style and font size. That means you can change the default font to a different font you like. On the Home Tab, you have a drop-down to select the font style.

font-style-and-size

And next to that, there’s a drop-down to select the font size. From both drop-downs, you can edit the name and size by typing. If you know the font name you want to change or the size you want to apply, enter it by typing and hit enter to apply.

12. Change Cell Alignment

You can also change the alignment of the cell if required. On the Home Tab, you have the buttons for the alignment options. There are two options to align (centre, left, and right) and (top, middle, and bottom).

change-cell-alignment

With these option buttons, you can apply borders to a single cell, range, or multiple ranges. But as I said, there are two options here, so you need to apply alignment in two ways.

13. Apply Border to a Range or a Cell

You can apply cell borders on a cell or a range of cells. Once you select a range or cell, you need to go to the Home tab and click on the Borders drop-down.

apply-border

In this drop-down, you have all the options to apply the Border to the selected range. In the last option of the drop-down, you can click “More Borders” to apply custom borders.

more-borders-options

14. Open Cell Format Options

Excel allows you to add a comment to a cell. A cell comment can have multiple usages, like a message for other users or an identification for a cell so that you can find it easily.

add-comments

In the Review tab, click “New Comment” to add a new comment and then enter your comment in the input bar.

15. Adding Comments

Excel allows you to add a comment to a cell. A cell comment can have multiple usages, like a message for other users or an identification for a cell so that you can find it easily.

add-comments

In the Review tab, click “New Comment” to add a new comment and then enter your comment in the input bar.

16. Replying to Comments

Once you add a comment, a user or even you can reply to those comments. It is like a conversation that you can have with the comments.

replying-to-comments

In the reply input bar, you can enter your reply, press Ctrl + Enter, or click the send button to send your response.

17. Adding Notes

Apart from comments, you also have notes to enter on a cell specifying additional information for the user.

adding-notes

In the review tab, click on the notes drop-down and then click on the new note to insert a recent note to the selected cell.

18. Entering a Function in a Cell

Function is one of the most powerful features in Excel. Select a cell first to enter a function and type (=). Once you enter =, you need to start typing the name of the function that you want to use.

entering-function-in-cell

Once you enter the function’s name, specify its arguments to get the desired result. For example, we have two arguments to define in the above function.

specify-arguments

But if you see the second argument enclosed in square brackets, it’s optional.

In Excel, a function is a predefined formula that performs a specific calculation by using values a user input as arguments. Every Excel function has a specific purpose, in simple words, it calculates a specific value. Each function has its arguments (the value one needs to input) to get the result value in the cell.

Components

Each function has two major components. In short, each function (except a few) is made up of two following things:

  • Function Name
  • Arguments

Let me show you an example. Let’s take a look at the below function which we have inserted in the cell A1.

Now if you look at the formula bar you can understand the structure of the function by splitting it into two parts i.e. name and arguments.

Function Arguments

As I have already mentioned in a function you need to specify input values to get the desired result. An argument is that value that you need to specify. If you look at the syntax of a function you can see there in each function there is a set of arguments to specify.

Below are the types of arguments:

  • Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
  • Optional: If you skip specifying these arguments it will not stop a function from calculating its result value.
  • No Arguments: There are a few functions (like NOW) where you don’t need to specify any argument.

How to INSERT a Function in Excel

The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with equals to sign.

Let’s say you want to insert the SUM function:

  • First of all, you need to type = and then type SUM.
  • After that, enter the opening parentheses.
  • Specify the arguments (refer to a cell or you can directly enter values into the function).
  • In the end, type closing parentheses and hit enter.

Major Types

Below are the major types:

  • Financial Functions: These functions can help you calculate some of the common but important financial calculations easily.
  • Text Functions: If you deal with data where you have text, then below are some of the functions that you need to learn to work efficiently.
  • Date Functions: Dates are one of the major ingredients of data that you use every day, and help you to analyze your data in a better way.
  • Time Functions: Just like dates, time could also be there in data and you can use time functions to deal with data where you have time values.
  • Logical Functions: Logical functions can help you create some of the most helpful formulas in your spreadsheet.
  • Maths Functions: Excel is all about calculations and analysis, and mathematical functions and you can use these functions to get better in calculations and analysis.
  • Statistical Functions: One of the best things about Excel is there are a bunch of statistical functions that you can use to analyze data easily.
  • Lookup Functions: In Excel, some specific functions can help you to look up a value or specific information about a cell or a range of cells.
  • Information Function: These are some specific functions that you can use to get information about the values you supplied.

19. Create a Basic Formula in Excel

In Excel, you can create a basic formula in a cell using the basic calculation operator (Addition, Subtraction, Division, and Multiplication).

Type the equal sign, and then you can start writing your formula. Let’s say you want to sum 10 and 20, then, you need to write a formula like (=10+20).

basic-formula-in-excel

And if you want to refer to cells in the formula, you can also do that. In the same way, you can also do subtraction, division, and multiplication.

refer-to-cells-in-formula

20. Creating a Named Range

As you have seen, each cell in a worksheet has a cell address. But Excel also allows you to specify a meaningful name for a range of cells or a single cell. It’s called a named range.

create-name-range

Select the range and click within the address bar for this. After that, enter the name you want to give and hit enter to create the named range. And then, you can use this named range within the formulas and functions.

select-range

21. Lock a Sheet (Worksheet)

In Excel, there is an option to lock the sheet. As you know, we have four different types of sheets in Excel to use, and you can lock all these sheets. This tutorial teaches us to use the “Protect Sheet” option.

lock-a-sheet

Lock “Protect” a Worksheet

  1. First, right-click on the worksheet tab, and click on “Protect Sheet”.
    2-click-on-protect-sheet
  2. In the “Protect Sheet” dialog box, select the options you want to allow the user to edit.
    3-choose-options-that-users-can-edit
  3. And enter the password which you want to apply and click OK.
    4-enter-the-password
  4. Next, again enter the same password to confirm, and click OK to apply the lock.
    5-re-enter-password-to-conform

When you click OK, it locks the sheet using the specified password (123456). When a user tries to edit a cell, it will show a message saying, “The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect a sheet. You might be requested to enter a password.”.

popup-to-unprotect-sheet

To unprotect, you need to use the same steps and enter the password which you have specified (123456). The protection of the sheet remains intact even if you move or copy the sheet to a new or an existing workbook.

Note: You can skip specifying the password but lock the sheet. In this case, the user doesn’t need the password to unlock the sheet. But the sheet will have all the protection which you have specified.

Lock a Chart Sheet, Macro Sheet, or Dialog Sheet

As I said, you can lock all the sheet types, and each sheet has a set of elements that you can protect with the lock.

Chart Sheet
lock-a-chart-macro-or-dialog-sheet
Macro Sheet
lock-macro-sheet
Dialog Sheet
lock-dialog-sheet

As you can see in the above examples, specifying the password is optional in the sheets.

22. Change Column Width and AutoFit Columns

In Excel, a sheet has multiple cells of the same size, each containing 8.43 characters. Especially while preparing reports, creating data tables, or doing other calculations, you often need to alter (increase -decrease) the width of a column. And, by increasing your columns wider, you can easily get the visibility of the whole data on your sheet.

Notes

  • Try not to make columns narrow enough, as it can make your data hard to read and take up unnecessary space.
  • If you have many columns to adjust, selecting all of them may be more efficient for adjusting the width.
  • Autofit is quick and easy, but if your cells contain a lot of text, there may be better choices.

Adjust Column Width with Mouse

Adjusting the width of the column by clicking and dragging is very easy and understandable. Let’s do this step by step.

  1. To change the width of “Single or multiple columns”, first, select that specific column on the spreadsheet.
    select-the-column
  2. After that, move the pointer of the mouse to the border of the selected column in the column header.
    mouse-pointer-on-column-border
  3. Once it looks like a four-sided arrow, press and hold the left button of the mouse and drag it towards the right side to increase its width size.
    drag-towards-with-mouse
  4. For changing the width of “All Columns”, press Ctrl + A first to select all columns from the sheet and now drag on any column header to adjust it as you required.
    select-the-sheet-to-expand-all-columns

You can also view the current width of the column by clicking on the border of the column in the column header with its pixels.

current-width-of-column

Set Column Width to a Specific Number

  1. Again, you need to select the column first to change its width to a specific number.
    set-column-width-to-specific-number
  2. Then, in the Home tab, go to the Cells group and now click on “Format”.
    home-tab-cells-icon
  3. Next, choose “Column Width” from the list of the drop-down.
    choose-column-width
  4. Once you click, it opens the dialog box of column width. Now, enter the exact value of the width that you want in the box and click “OK” to apply.
    column-width-dialog-box

Change the Column Width by Right-Click in Excel

To change the column width in Excel, right-click on the column header and select the column you want to adjust.

After selecting the column, right-click on the column header. A menu will appear.

column-width-by-right-click

In the drop-down menu, look for the option labeled ‘Column Width’. Click on this option. A dialog box will appear, asking you to enter a new width for the column. Type in the desired width and then click ‘OK’. The width of the column will be adjusted accordingly.

column-width-dialog-box

Repeat these steps for any other columns you want to adjust. When you’re done, remember to save your changes.

Adjust Column Width by AutoFit Option

If the data in the columns are not visible, you can use the autofit option to adjust the width of the columns using double click. The following are the steps to do:

  1. For this, you must first select the single, multiple, or all columns to set the width.
    column-width-autofit
  2. Next, go to “Format” in the cells group under the Home tab. Select the “Autofit Column Width” option from the list of options.
    select-autofit-column-width
  3. Then, it autofits the width of all selected columns automatically so that you can see all the data easily, as shown in the following picture.
    columns-width-adjusted

If you have other columns that you want to adjust, simply repeat these steps for each of those columns. With the AutoFit option, you can easily ensure that your columns are the perfect width for their content, enhancing the readability and appearance of your Excel worksheets.

Setting the Default Width of Columns

With default column width, you can simultaneously change the width of complete worksheet columns and set the width for all worksheets in a workbook.

  1. Select the single or multiple spreadsheets where you would like to adjust the default width. Here, we are selecting the single worksheet as an example.
    default-width-of-the-columns
  2. Now, go to the “format” and click on the “Default Width” option.
    default-width-option
  3. Then, it will open the “Standard Width” dialog box. There, you can see the current width of the blank columns, and it’s 20.
    standard-width-dialog-box
  4. Last, you need to enter the “Standard Column Width” in the box and click “OK” to apply.
    standard-column-width-dialog-box

Points to Remember

  • The standard default width applies to all blank cells in the worksheet instead of those with data already.
  • The autofit column width doesn’t apply if the column width is sufficient for the data in the column.

Understanding the Width used in Excel Columns

In Microsoft Excel, the width of a column is determined not by pixels but by the number of characters that can fit within it. This measurement is based on the default font size and the character ‘0’ width. It means that one unit of column width is equivalent to the width of one ‘0’ character in the default font size.

For example, if you set a column’s width to 10, you could fit ten ‘0’ characters side by side in that column. This character-based measurement method is unique to Excel and some other spreadsheet software. It helps maintain consistency when viewing the workbook.

It’s always a good idea to check how your data displays on different screens and under different view settings to ensure it remains legible and well-formatted.

23. Share a Workbook with Others in Excel

In Excel, there are two significant ways you can use to share your Excel file (Workbook) with others:

  1. Add it to Cloud (Like OneDrive) and then Share it with others.
  2. Send it through an E-Mail.

In this tutorial, we are going to learn both methods in detail. So, let’s get started.

Upload on the Cloud and Then Share                      

In Excel (Office 365), there are multiple ways that you can use to share the file.

  1. First, using the Save As option, you need your file to the one drive. This is precisely the same way that you use to save your files normally. Here, you need to change the location to OneDrive Instead of your system.
    1-share-workbook-with-others
  2. Once you save your file to OneDrive, return to the workbook and click the Share drop-down button on the top right corner of the Excel Window.
    2-share-drop-down-button
  3. From here, you need to click on the copy link option to get the link to the active workbook.
    3-get-link-to-activate-workbook
  4. Next, it will show you a dialog box with the link to the workbook. This will automatically copy the link to the clipboard.
    4-copy-link-to-clipboard

You can share this link with others so that they can access the file.

share-link-to-access-file

Giving Access to an E-mail

You can also share access to an e-mail ID directly. For this, you need to click on the share button from the top right of the Window.

access-to-an-email

When you click the send button, it sends an e-mail to the user with the link to access the workbook.

email-link-to-user

Only Sharing the Worksheet

It also allows you to share the link to the current worksheet with a user. When you click the Share drop-down, you can get the shareable link only to the current worksheet.

only-sharing-the-worksheet

Changing Access Type

When you share a workbook with a link, you can change the access you want to give the user. In more settings, you can select between edit and view.

change-access-type

Set an Expiration Date

The link which you share can also be date specific. When you apply a date, it will expire after that is passed.

set-expiration-date

Set a Password

And in the same way, you can also set a password to the file for sharing it with other users.

set-a-password

With this, the user must enter the password before accessing the workbook.

enter-password-to-access-file

Share the Workbook through an E-mail

As I said, there is another way to share the workbook with others. For this, once you go to the File Tab, click on the “Share” option.

share-workbook-through-email

Here you have two options to use:

  1. Send as Attachment – When you click this button, it opens your default e-mail client and attaches the workbook by composing a new e-mail.
  2. Send a Link – When you click this button, it opens the e-mail client with the new e-mail and paste the link to the workbook.

You can use both ways to share the workbook with others. But there’s a slight difference between these two methods.

When you share the entire file, it won’t share the future changes with the user, but when you share the file link, it also shares all the changes that you make in the future with the user who has the link.

Note: You can attach the workbook to an e-mail and then send it to the user you want to share it with.

24. Recover Unsaved Excel Files When Excel Crashed

There are a few things that I hate from my soul and a crashed Microsoft Excel is one of those things. Just think like this, you are working on an important report and suddenly the application crashes and you get a message “Excel is not responding” or “Excel has stopped working”.

Well, it’s fine if you have saved your workbook but, if not, then it’s almost like the world is going to end kind of feeling. The fact is, you and, we can’t avoid these kinds of things because it all depends on the system’s performance.

But the good news is that in Excel, we can use some options and features to recover an unsaved file and get our work back.

Today in this post, I’d like to share with you these simple steps to recover unsaved Excel files and all the important points related to them.

Activate/Configure AutoSave Option to Recover Files

In Excel, there is a feature “AutoSave” which allows it to save files [temporary basis] even if you haven’t saved them. To configure it:

  • First of all, go to File Tab and click on options.
to activate auto save click on file tab options to recover unsaved files after that
  • Now in the options window, click on Save Tab.
to activate auto save click on save tab to recover unsaved files after that
  • Here you have these three options which you can configure according to your need.
to activate auto save fill all the three options to recover unsaved files after that
  1. Save files in this format: You can choose the format of the workbook in which you want the save your file with unsaved work.
  2. Save AutoRecover option every: You can set the duration time in minutes to get your file autosave.
  3. Keep the last AutoRecover: The last version if I close without saving: It creates backup versions of a file.

Important Note: Make sure to keep the duration time short to get more versions to save.

Apart from this, there is also an “AutoRecover” option which helps you to recover the last unsaved version of a file (which is saved once).

auto recovery pane for unsaved files

The good news is, by default these options are activated and continuously save unsaved workbooks on your system.

Note: While working with Excel for a while, I have observed that there can be three different scenarios when you need to recover your Excel files.

(Recovering Unsaved Excel Files) You Want Your Unsaved File Back with Data

The first case is when you are working on a file that is not yet saved anywhere just before you save it Excel crashes or you close it without saving it. Here are the steps to recover a file that is not yet saved by you.

  • First of all, go to the File tab and click on “Open”.
click on open to recover unsaved files folder
  • Now, click on “Recent Workbooks”.
click on recent to open unsaved files folder
  • After that, scroll to the end and click on “ Recover Unsaved Workbooks”.
click on recover unsaved workbook button to open the folder
  • Once you click on it, it shows an open dialog box, and locate it to (C:Users/UserName/AppData/Local/Microsoft/Office/UnsavedFiles) where Excel has saved a copy of all the unsaved workbooks.
select file from the folder which you want to open
  • Select the workbook to open and click OK.
  • Once you open a workbook, it shows a message alert to save that file before you use it or make any changes to it.
save as file which you want to open the unsaved file

Quick Note: The files that Excel saved as a backup are in “xlsb” format, so when you save them make sure to use the correct format.

(Recovering Unsaved Excel Files) Forget to Save Files or Excel Got Crashed

Now, this is the thing that happens to all of us. We are working on a file that is already saved somewhere. And, after updating we forgot to save it, or Excel got crashed. In this case, everything that you have updated is lost.

But…But…But…

With the auto-recover option, you can recover the file with all the updated data. Here the point is simple when you re-open Excel it shows you the “Document Recovery Pane” with the list of all the unsaved versions of the files.

auto recovery pane for unsaved files

You can click on the file that you want to open and once you open it save it.

(Recovering Unsaved Excel Files) Get the Previous Version of a Workbook

If you use Excel 2010 or above then you can also recover the previous version of a workbook. It can be helpful if you have made a mistake in your work that can’t be undone. Here are the steps to do it:

  • First of all, click on the “File” tab and go to the “Info” category.
click on info to get version of a workbook
  • Here you have a list of all the versions of the workbook which you can restore.
click on any of the saved version to open it
  • When you click on a version, Excel opens that version along with the present one so that you can compare both.

The save AutoSave option works behind this as well. Let’s say, you have specified 10 minutes in the AutoSave option, and Excel saves a version of your file after every 10 minutes.

3.1 Recover the Previous Version of an Excel File from OneDrive

If you are like me and love to use cloud storage to save files then you can also recover the previous version of a file. In OneDrive, there is an option to get a list of the old version of an Excel workbook. Follow these simple steps which you can use:

  • Open OneDrive and then open the folder in which your file is saved.
open folder in one drive to get old versions of a file
  • After that, select that file and right-click.
  • Now, click on “View History” to open that file in another tab.
click on version history to open that workbook
  • Here on the left side of the window, you have a list of the versions which you can open by clicking on it.
a list of old versions of file saved in one drive which you can recover

25. Change Column Width and AutoFit Columns

In Excel, if you want to create a duplicate sheet which means creating a sheet’s copy within the same workbook, you can use two different methods.

  • Drag and Drop with Mouse.
  • Create a Duplicate Copy with Copy and Move

In this tutorial, we are going to look at both methods.

Drag and Drop to Create a Duplicate Sheet

  1. First, activate the sheet for which you want to create a copy.
  2. Now, press the Ctrl key.
  3. After that, by holding the Ctrl key, click on the sheet tab.
  4. In the end, drag and drop the sheet to a new place within the sheet tabs.
duplicate-sheet-excel-drag-drop

It creates a duplicate sheet of the sheet, which drags and drops.

drag-drop-to-create-duplicate-sheet

You can see in the above example that you have a new duplicate sheet of “Sheet3” with (2) along with the original name.

Use Copy and Move to Create a Duplicate Copy of the Sheet

When right click on a sheet tab, you can see the options “Move or Copy” there. This option allows you to copy the sheet to a new workbook or an open existing one.

copy-and-move-to-duplicate-sheet

But with the same option, you can create a duplicate sheet copy within the same workbook. When you click on the option, it shows you a dialog box to select the destination to copy the sheet.

copy-within-same-workbook

In the Move or Copy dialogue box, tick-mark the “Create a copy” checkbox, and make sure to have the same workbook name in the “To book:”. In the end, click OK to create a duplicate copy.

tickmark-create-a-copy

You can choose the position to add the duplicate sheet in this dialogue box.

choose-position-to-add-duplicate-sheet

Note: From both methods, the first method is quick and easy. But you can use any of these according to your preference.

26. Use Filter in Excel

A filter is an option to filter data from a single column or multiple columns in Excel. It allows you to have a drop-down on a column to get a list of values you have, and then you can select one or multiple values to filter. It’s helpful to analyze a large set of data.

What is FILTER used for?

In Excel, the filter filters an extensive data set to get the information for specific criteria. Take the example below, where we list the first name, last name, state, and zip codes.

Now, when I use the filter on the state name and filter “AK” it only shows me the entries where names are from the state “AK.” In the same way, you can also filter the data using the zip code.

How to Apply Filter in Excel

There are two quick ways to apply a filter to the data in Excel:

  • Apply Filter Button
  • Keyboard Shortcut

Filter Button

  1. Select the entire data or a single cell from the data.
  2. Next, open the Data Tab.
  3. After that, go to the Sort and Filter Group.
  4. Finally, click on the “Filter” button to apply the filter to the data.

Keyboard Shortcut to Apply Filter in Excel

  1. Select the entire data or a single cell from the data.
  2. Press and hold the Ctrl + Shift Keys.
  3. And then press the key “L”.

So, the keyboard shortcut will be Ctrl + Shift + L. And there’s one more keyboard shortcut which you can use: Alt > D > F > F.

Note 1 – If you want to apply a filter to specific columns in a large data set, in that case, you need to select only those columns (let’s say two) and use the method we have discussed above to apply the filter.

Note 2 – You can also apply the filter button to the quick access toolbar. For this, you must right-click the filter button and click “Add to Quick Access Toolbar.”

Note 3 – You can also use the right-click menu to apply the filter. For this, you need to select a cell, right-click it, and then use the options to apply the filter. For this, we have a detailed explanation ahead.

Open Filter in Excel

You have two ways to open the filter once you apply the filter on a column.

  • Click on the filter button.
  • Select the header cell and then open the filter using the keyboard shortcut Alt + Down.

1. Basic Filter Options

When you open the filter from a column header, you can see that there are multiple options to use.

One of the easiest ways to filter values is using the search bar. When you type a value in the search bar, it will show you all the values which contain that value.

You can also use the mouse to check mark values you want to filter. Or, you can use the spacebar to check mark a value if you’re going to use the keyboard shortcut.

If you have a long list of values in the filter drop-down, you can use the scroll bar on the right side to move the list.

Once you tick-marker the values you want to filter, click the “OK” button to apply the filter.

2. Text Filter Options

When you apply a filter to a column where you have text values, it shows you “Text Filter” options. You can use these options to get

In the test filter options, you can use six pre-defined options.

  • Equals – To filter exact match value.
  • Does Not Equal – To filter values that don’t equal the value you define.
  • Begin With – To filter values that begin with a specific value.
  • Ends With – To filter values that end with a specific value.
  • Contain – To filter values that contain the value you specify.
  • Does Not Contain – To filter values that do not contain the value you specify.

In the end, there’s also an option to open the custom filter dialog box. You can find all the options we discussed above in this dialog box.

Let’s take an example to understand how to use these options. From the state column, you want to filter values that start with the alphabet A. You need to open the filter, go to the Text Filters option, and click “Begins With.”

Now, in the custom autofilter dialog box that begins with is already selected, you must enter “A” in the input bar.

In the end, click OK to apply the filter. Now you can see in the below snap show that it has filtered all the values that start with the alphabet A.

3. Number Filter Options

Like text filters, there are specific options when applying a filter on a column with a number.

In the “Number Filters,” you can use the options like:

  • Less Than or Greater than to filter values that are greater or lower than the value you specify.
  • Use above or below average to filter values above or below the average of the values you have in the column.
  • Top 10 to get the top ten values from all your values in the column.

Let’s take an example to understand how to use these options. Let’s use the between options to filter values between 10000 and 11000.

When you click on this option, it opens the custom filter option with a pre-defined condition applied to filter the values between two numbers.

Now, you must enter the first lower number in the first input bar and the higher number in the second input bar.

Finally, click OK to apply the filter to the column.

4. Date Filter Options

In the same way, when you have dates in a column, in that case, you can get options related to dates.

There are options to filter dates based on weeks, months, quarters, and years. You can also filter dates up to a specific date or range.

Let’s take an example to understand how to use these options. Let’s say you need to filter all the dates that are from the last year.

For this, you have a pre-defined option to use. You need to open the filter and then, in the “Date Filters,” click on the “Last Year.”

When you click it, it filters the dates from the last years.

And when you click on the custom filter, you can see a custom filter already created to filter last year’s dates.

There’s a custom filter at the backend for each pre-defined option you have there to use.

These options help you save time applying a custom filter of your own. But you can still use a custom filter to create a condition to filter dates.

While using the date filters, in the custom filter option, you can use the date picker to select the date you want to enter in the input bar.

Note – While using the filter in Excel, you can use wildcard characters with it. And here’s a complete guide to learn about these.

Use Filter from Right Click Menu

You can also apply and use the filter from the right-click menu. Once you right-click on the cell, you can go to the Filter option. And in the filter option, you will find the list of options.

All the essential options are there for you to use:

  • Clear Filter
  • Reapply
  • Filter values that match the value of the selected cell.
  • Filter values that match the cell color of the selected cell.
  • Filter values that match the font color of the selected cell.
  • Filter values that match the selected cell’s Icon (Conditional Formatting).

Filter by Cell Color and Font Color

In the example below, we have a few cells with the cell color, and Excel gives you options to filter cells with the cell color and font color.

When you open the filter drop-down, you can filter using the cell color.

If you have font color applied, you will also have the option to filter cells using those colors.

Clear a Filter

You can use the “Clear” button from the data tab to clear a filter and show all the values back.

You can also use the clear option from the filter drop-down.

Note – The first method works best if you have applied a filter to multiple columns and want to remove it. With the second method, you need to open the filter drop-down for each column and then clear the filter one by one.

Reapply a Filter

There’s also an option to reapply the filter. Let’s say you have to filter a specific value from a column and then add a new value that is not the filtered value.

In the above example, you can see that we have applied the filter on the state column for the “AK” state, but there’s a new entry with the state name “LA.”

Now, to filter out this entry and only show the entries for the state “AK,” you need to reply to the filter. You must go to the Data Tab and click the “Reapply” button to reapply the filter.

Note – You can also use the reapply options from the right-click menu. Check this tutorial’s “Use Filter from Right Click Menu” section for details.

Multi Column Filter

Yes, you can apply a filter to multiple columns simultaneously to filter data. Once you use the filter to the data:

Then, you can apply the filter to the first column and then open the filter from the second column filter values.

You can see that we have applied two filters:

  • The first is on the state column, where we have filtered the states “AK” and “CA.”
  • Second is the column data, where we filtered the dates that fall in 2024.

Remove a Filter in Excel

If you want to remove a filter, you need to repeat the method which you have used to apply the filter:

  • Click on the Filter button.
  • Use the keyboard shortcut Ctrl + Shift + L or Alt > D > F > F.

Copy Filtered Data

When you want to filter data, you can copy that data and then paste it into another range. You can see in the below example that we have a filter “CA” state. After filtering, we selected data along with headers.

Then press Ctrl + C to copy data, go to the range where you want to paste the data, and then use the keyboard shortcut to Ctrl + V to paste the values.

Using the SUBTOTAL Function with the Filters in Excel

You can use the SUBTOTAL function while using filters in Excel. The SUBTOTAL function helps you create a dynamic formula to get count, sum, max, min, etc.

In the SUBTOTAL function, in the first argument, you can choose the calculation you want to get when you filter the data. In the below example, we have used the SUBTOTAL to get the count of the values filtered.

In the first argument, we have chosen 3 (COUNTA), which tells the function to count the cells that are not blank. In the second argument, we have specified the entire range.

BASIC EXCEL TUTORIALS

2. Keyboard Shortcuts

9. Formulas

Recommended Books

Below are my two favorite Excel books for beginners which every person who is starting out with Excel should read.

  1. Excel 2016 for Dummies: This book covers everything you need to know to perform the task at hand. Includes information on creating and editing worksheets, formatting cells, and entering formulas […]
  2. Microsoft Excel 2016 Bible: Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs Whether you use Excel at work or […]

62 thoughts on “An Introduction to Microsoft Excel | Basics Knowledge + Components + Examples”

    • Thanks Soo much wish I can have much knowledge about Microsoft Excel.

      Reply
  1. This website is like a bliss for the candidates who are really keen to learn MS EXCEL and even for beginners who are struggling to understand it . A big thank you to the author.

    Reply
  2. Thanks for the valuable information regarding Microsoft Execl. found useful from every aspect of features of software.

    Reply
  3. Just now I have gone through your website and found very prized notes in excel, which are written in a lucid manner and can easily be understandable for everyone. I saved YOUR NOTES. Your patience is really appreciable. Please keep it up. Thanks

    Reply
  4. Very thoughtful and helpful content. Thank you so much for your work and time

    Reply
  5. This is very wonderful website for learning Excel components thank you very all team members of this site good work keep it up.

    Reply
  6. im satisfy with traning its really help full and undestanding
    iwant to learn more thank you

    Reply
  7. excel is so good it helps in data management

    Reply
  8. I’m using Office 2019 in my PC. You have recommended in your article “Excel 2016 for Dummies”.

    I would like to know as to whether your recommended book is also suitable for Office 2019.

    Reply
  9. its best view in excel consequently step by step content .its so well for learn this web realy helped me.So more keep it up thanks

    Reply
  10. How can I link the data in a worksheet with the chart in another worksheet in the same workbook so the when I enter the data the charts appear automatically. It a budget workbook I designed

    Reply
  11. Well, that was “amazing” i’m greatful..

    Reply
  12. Thank you for this post it really helped me. Keep it up.

    Reply
  13. This article is really helpful. Thank you so much. Excellent post. This article is really very interesting and effective. I think its must be helpful for us. Thanks for sharing your informative.

    Reply
  14. Great post. Wonderful information and really very much useful. Thanks for sharing and keep updating Thank You so much for sharing this information. I found it very helpful. Thank you so much again.

    Reply
  15. Thanks very much for ur valuable info abt excel….it will help the beginners who are trying out a job has a fresher…..

    Reply
  16. how to find all cells together which are having manual numbers in a spreadsheet with lot of data where formulas are applied at most the places?

    Reply
  17. Honestly I really appreciate your work , you’re doing great work it,s help me to perform job tasks

    Reply
  18. Sir Ms Excel Ka Hindi and English language me pdf note kaise milega

    Reply
  19. Thanks for your clear explanation about excel and its functions

    Reply
  20. Hi, I believe the column is vertical while the row is horizontal

    Reply
  21. Hi…isnt that the column is vertical set and rows are horizontal…

    Reply
  22. Thanks for sharing this informative post. It is very useful the beginners, who are new to excel.

    Reply

Leave a Comment