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.
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
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.
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.
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”.
And if you are in a folder, click on the “New” dropdown and then click on the “Microsoft Excel 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.
Once you click “Save As”, click “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.
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.
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.
When you click on the Excel application icon, it loads add-ins and then opens the Excel application, showing you the Excel start screen.
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.
As I said, it will instantly open a new workbook named “Book1”. You need to save the workbook which you have inserted.
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.
From there, in the “Save As” dialog box, you need to enter the name and then click on the Save.
If you want to save the File to a different location, click “Browse” and locate the folder where you want to save the workbook.
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”.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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 “Protect” a Worksheet
- First, right-click on the worksheet tab, and click on “Protect Sheet”.
- In the “Protect Sheet” dialog box, select the options you want to allow the user to edit.
- And enter the password which you want to apply and click OK.
- Next, again enter the same password to confirm, and click OK to apply the lock.
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.”.
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
Macro Sheet
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.
- To change the width of “Single or multiple columns”, first, select that specific column on the spreadsheet.
- After that, move the pointer of the mouse to the border of the selected column in the column header.
- 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.
- 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.
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.
Set Column Width to a Specific Number
- Again, you need to select the column first to change its width to a specific number.
- Then, in the Home tab, go to the Cells group and now click on “Format”.
- Next, choose “Column Width” from the list of the drop-down.
- 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.
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.
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.
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:
- For this, you must first select the single, multiple, or all columns to set the width.
- Next, go to “Format” in the cells group under the Home tab. Select the “Autofit Column Width” option from the list of options.
- 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.
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.
- 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.
- Now, go to the “format” and click on the “Default Width” option.
- Then, it will open the “Standard Width” dialog box. There, you can see the current width of the blank columns, and it’s 20.
- Last, you need to enter the “Standard Column Width” in the box and click “OK” to apply.
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.
In Excel, there are two significant ways you can use to share your Excel file (Workbook) with others:
- Add it to Cloud (Like OneDrive) and then Share it with others.
- Send it through an E-Mail.
In this tutorial, we are going to learn both methods in detail. So, let’s get started.
In Excel (Office 365), there are multiple ways that you can use to share the file.
- 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.
- 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.
- From here, you need to click on the copy link option to get the link to the active workbook.
- Next, it will show you a dialog box with the link to the workbook. This will automatically copy the link to the clipboard.
You can share this link with others so that they can access the 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.
When you click the send button, it sends an e-mail to the user with the link to access the workbook.
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.
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.
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 a Password
And in the same way, you can also set a password to the file for sharing it with other users.
With this, the user must enter the password before accessing the workbook.
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.
Here you have two options to use:
- 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.
- 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.
- Now in the options window, click on Save Tab.
- Here you have these three options which you can configure according to your need.
- Save files in this format: You can choose the format of the workbook in which you want the save your file with unsaved work.
- Save AutoRecover option every: You can set the duration time in minutes to get your file autosave.
- 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).
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”.
- Now, click on “Recent Workbooks”.
- After that, scroll to the end and click on “ Recover Unsaved Workbooks”.
- 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 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.
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.
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.
- Here you have a list of all the versions of the workbook which you can restore.
- 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.
- After that, select that file and right-click.
- Now, click on “View History” to open that file in another tab.
- Here on the left side of the window, you have a list of the versions which you can open by clicking on it.
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
- First, activate the sheet for which you want to create a copy.
- Now, press the Ctrl key.
- After that, by holding the Ctrl key, click on the sheet tab.
- In the end, drag and drop the sheet to a new place within the sheet tabs.
It creates a duplicate sheet of the sheet, which drags and drops.
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.
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.
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.
You can choose the position to add the duplicate sheet in this dialogue box.
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
- Select the entire data or a single cell from the data.
- Next, open the Data Tab.
- After that, go to the Sort and Filter Group.
- Finally, click on the “Filter” button to apply the filter to the data.
Keyboard Shortcut to Apply Filter in Excel
- Select the entire data or a single cell from the data.
- Press and hold the Ctrl + Shift Keys.
- 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
1. Excel Basics
- Activating the Dark Mode in Excel
- Add a Button in Excel
- Add a Header and Footer in Excel
- Add Dollar Sign in Excel
- Alignment in Excel
- Automatic Rolling Months in Excel
- Automatically Add Serial Numbers in Excel
- Convert a Formula to Value in Excel
- Convert Negative Number into Positive in Excel
- Copy and Paste Column Width in Excel
- Copy and Paste Values Without Formatting in Excel
- Draw a Line in Excel
- Fill Justify in Excel
- Find and Replace in Excel
- Format Painter in Excel
- Freeze Panes in Excel
- Get the Scroll Bar Back in Excel
- Hide and Unhide a Workbook in Excel
- Increase and Decrease Indent in Excel
- Insert (Type) Degree Symbol in Excel
- Insert a Check Mark Symbol [Tickmark] in Excel
- Insert a Timestamp in Excel
- Insert an Arrow in a Cell in Excel
- Insert Bullet Points in Excel
- Insert Delta Symbol in Excel in a Cell
- Insert Text Box in Excel
- Keyboard’s Arrow Keys Not Working (Scroll Lock ON-OFF)
- Make a Copy of the Excel Workbook (File)
- Make First Row Header in Excel
- Merge – Unmerge Cells in Excel
- Rotate Text in Excel (Text Orientation)
- Save a File in Excel
- Save an Excel File on Mac (Workbook)
- Show Ruler in Excel
- Spell Check in Excel
- Zoom In or Zoom Out in Excel
2. Keyboard Shortcuts
- Absolute Reference (Excel Shortcut)
- Add Column (Excel Shortcut)
- Add Comments (Excel Shortcut)
- Add Indent (Excel Shortcut)
- Add New Sheet (Excel Shortcut)
- Align Center (Excel Shortcut)
- Apply Border (Excel Shortcut)
- Auto Fit (Excel Shortcut)
- AutoSum (Excel Shortcut)
- Check Mark (Excel Shortcut)
- Clear Contents (Excel Shortcut)
- Close (Excel Shortcut)
- Copy and Paste (Excel Shortcut)
- Currency Format (Excel Shortcut)
- Cut and Paste (Excel Shortcut)
- Delete Cell (Excel Shortcut)
- Delete Row(s) (Excel Shortcut)
- Delete Sheet (Excel Shortcut)
- Drag Down (Excel Shortcut)
- Edit Cell (Excel Shortcut)
- Fill Color (Excel Shortcut)
- Find and Replace (Excel Shortcut)
- Format Painter (Excel Shortcut)
- Freeze Pane (Excel Shortcut)
- Full Screen (Excel Shortcut)
- Group (Excel Shortcut)
- Hyperlink (Excel Shortcut)
- Insert Cell (Excel Shortcut)
- Insert – Add Row(s) (Excel Shortcut)
- Lock Cells (Excel Shortcut)
- Merge-Unmerge Cells (Excel Shortcut)
- Open Format Cells Option (Excel Shortcut)
- Paste Values (Excel Shortcut)
- Percentage Format (Excel Shortcut)
- Print Preview (Excel Shortcut)
- Save As (Excel Shortcut)
- Select Row (Excel Shortcut)
- Show Formulas (Excel Shortcut)
- Strikethrough (Excel Shortcut)
- Subscript (Excel Shortcut)
- Superscript (Excel Shortcut)
- Switch Tabs (Excel Shortcut)
- Transpose (Excel Shortcut)
- Undo and Redo (Excel Shortcut)
- Unhide Columns (Excel Shortcut)
- Wrap Text (Excel Shortcut)
- Zoom-In (Excel Shortcut)
- Apply Date Format (Excel Shortcut)
- Apply Time Format (Excel Shortcut)
- Delete (Excel Shortcut)
- Open Go To Option (Excel Shortcut)
3. Range
- Range in Excel
- Add and Remove Hyperlinks in Excel
- Change Column Width in Excel
- Copy and Paste a Column in Excel
- Delete a Single Row or Multiple Rows in Excel
- Delete Blank Rows in Excel
- Deselect Cells in Excel
- Find a Name Range in Excel
- Lock Cells in Excel
- Make Cells Bigger in Excel
- Make a Paragraph in a Cell in Excel
- Move a Row and Column in Excel
- Multiply in Excel using Paste Special
- Quickly Swap Two Cells in Excel
- Row Vs Column in Excel (Difference)
- Select Non-Contiguous Cells in Excel
- Select the Range in Excel
- Unhide Rows in Excel
- Use Column Numbers in Excel Instead of Alphabets
- Write (Type) Vertically in Excel
4. Worksheets
- Quickly Refresh a Sheet (Worksheet) in Excel
- Rename Sheet in Excel
- Select All the Worksheets (Sheets)
- Steps to Create a Duplicate Sheet or Worksheet in Excel
- Unhide an Excel Sheet or All the Sheets (Worksheet)
- Unprotect or Unlock a Sheet with a Password or Without a Password
- View Two Sheets Side by Side in Excel
5. Workbooks
6. Conditional Formatting
- Complete Guide to Conditional Formatting
- Conditional Formatting Based on a Date
- Conditional Formatting Based on Another Cell
- Conditional Formatting Based on Another Column
- Conditional Formatting on Blank Cells
- Conditional Formatting to an Entire Column
- Multiple Conditions in Conditional Formatting
- Applying Color Scales using Conditional Formatting
- Compare Two Columns using Conditional Formatting
- Copy Conditional Formatting from Range to Another
- Data Bars in Excel using Conditional Formatting
- Find Duplicates in Excel using Conditional Formatting
- Highlight IF a Cell Contains a Specific Text with Conditional Formatting in Excel
- Highlight Rows using Conditional Formatting in Excel
- Stop IF True in Conditional Formatting
- Use Icon Sets in Excel (Conditional Formatting)
- Why Conditional Formatting Not Working in Excel
7. Printing
8. Charts
- Types of Charts in Excel + Other Charting Tutorials
- Add a Horizontal Line in a Chart in Excel
- Add a Vertical Line in a Chart in Excel
- Add Secondary Axis in a Chart in Excel
- Advanced Excel Charts
- Bullet Chart in Excel
- Create WAFFLE CHART in Excel
- Dynamic Chart Range in Excel
- Dynamic Chart Title in Excel
- Excel Copy Chart Format
- Funnel Chart in Excel
- Gantt Chart in Excel
9. Formulas
- #DIV/0
- #N/A
- #REF!
- #SPILL!
- #VALUE
- 3D Reference in Excel
- Add a Total Row in Excel
- Add Commas (Cell-Text)
- Add Hours to Time in Excel
- Add Leading Zeros in Excel
- Add Minutes to Time in Excel
- Add Month to a Date in Excel
- Add New Line in a Cell in Excel (Line Break)
- Add Seconds to Time
- Add Space (Single and Multiple)
- Add Years to Date in Excel
- Add-Subtract Percentage from a Number
- Add-Subtract Week from a Date in Excel
- Average But Ignore Errors
- Average Number but Exclude Zeros
- Average of the Percentage Values
- Average of the Time Values
- Average Only Non-Blank Cells
- Average TOP 5 Values in Excel
- Business Days in a Month
- Calculate Coefficient of Variation (CV) in Excel
- Calculate Compound Interest in Excel
- Calculate Cube Root in Excel
- Calculate Nth Root
- Calculate Percentage Variance (Difference) in Excel
- Calculate Simple Interest in Excel
- Calculate Square Root in Excel + Insert Symbol
- Calculate the Cumulative Sum of Values (Excel Formula)
- Calculate Time Difference Between Two Times in Excel
- Calculate VAT
- Capitalize First Letter in Excel
- Change Column to Row (Vice Versa) in Excel
- Change Text Case (Upper, Lower, Proper)
- Change Time Format in Excel
- Change to Sentence Case in Excel
- Check IF 0 (Zero) Then Blank in Excel
- Check IF a Cell Contains a Partial Text
- Check IF a Cell Value is a Number
- Check IF a Value Exists in a Range in Excel
- Combine Date and Time in Excel
- Combine IF and AND Functions in Excel
- Combine IF and OR Functions in Excel
- Combine VLOOKUP with SUMIF
- Compare Two Cells in Excel
- Compare Two Dates in Excel
- Compare Two Strings (Text)
- Concatenate (Combine) Cells with a Comma in Excel
- CONCATENATE IF (Combine with Condition)
- Concatenate with a Line Break in Excel
- Conditional Ranking in Excel using SUMPRODUCT Function [RANKIF]
- Convert a Date into a Month and Year
- Convert an Excel Time Value into a Decimal Number (Hours)
- Convert Date into a Text
- Convert Date to Number in Excel
- Convert Minutes into Hours and Minutes (HH:MM)
- Convert Month Name to Number
- Convert Seconds to Hours and Minutes
- Convert Text to Date in Excel
- Convert Time to Decimals
- Convert Time Value into Minutes (Excel Formula)
- Convert Time Value into Seconds
- Convert to Julian Date
- Count Between Two Numbers (COUNTIFS) in Excel
- Count Blank (Empty) Cells using COUNTIF in Excel
- Count Cells Less than a Particular Value (COUNTIF) in Excel
- Count Cells Not Equal To in Excel (COUNTIF)
- Count Cells that are Not Blank in Excel
- Count Cells with Text in Excel
- Count Characters in Excel (Cell and Range)
- Count Days Between Two Dates (COUNTIF Date Range)
- Count Filtered Rows in Excel
- Count Greater Than 0 (COUNTIF) in Excel
- Count Rows (All, Blank, Non-Blank, and with Numbers)
- Count Specific Characters in Excel
- Count Total Number of Cells from a Range in Excel
- Count Unique Values in Excel
- COUNT Vs. COUNTA
- Count Words in Excel
- Count Years Between Two Dates in Excel
- Countdown Days
- Create a Date Range in Excel
- Create a Dynamic Hyperlink
- Create a Horizontal Filter in Excel
- Create a Star Rating Template in Excel
- Custom Date Formats in Excel
- DATEDIF Function in Excel
- Does Not Equal Operator in Excel
- Extract (Get) Year or Month from a Date in Excel
- Extract Last Word from a Cell
- Extract Only Numbers from a Text (String)
- Extract Text After and Before a Character in Excel
- Find the Lowest Value from a List of Numbers (N)
- Find the Smallest Value from a Range of Cells (Smallest Number)
- Flip the First & Last Names and Add a Comma Between
- Generate Random Groups
- Get Current Time (Excel Formula)
- Get Day Name from a Date in Excel
- Get Day Number of Year in Excel
- Get End of the Month Date in Excel
- Get File Name in Excel
- Get File Path (Excel Formula)
- Get First Day of the Month in Excel (Beginning of the Month)
- Get Month from a Date in Excel (Extract Month)
- Get Previous Sunday
- Get Quarter from a Date [Fiscal + Calendar] in Excel
- Get Sheet Name in Excel
- Get the Domain from the Email ID
- Get the Most Frequent Number(s) from an Array
- Get the Value from a Cell
- Get Total Days in Month in Excel
- Get Years of Service in Excel
- Greater Than (>) and Equal To (=) Operator
- Gross Profit (Margin and Ratio)
- Hide Formula in Excel
- Highlight Dates Between Two Dates in Excel
- How to use Arithmetic Operators in Excel
- If a Cell Value Starts with a Text or a Number
- IF Cell is Blank (Empty) using IF + ISBLANK in Excel
- IF Negative Then Zero (0) in Excel
- IFERROR with VLOOKUP in Excel to Replace #N/A in Excel
- Ignore All the Errors in Excel (Explained)
- Indirect with VLOOKUP
- Lookup for the Cell Address Instead of the Value
- Lookup Last Value from a Column or a Row
- Match/Compare Two Columns with VLOOKUP
- MAX IF in Excel
- Median with IF (Conditional Criteria)
- Military Time (Get and Subtract) in Excel
- Number of Months Between Two Dates in Excel
- OR Logic in COUNTIF/COUNIFS in Excel
- Perform Two Way Lookup in Excel
- Quickly Calculate Ratio in Excel
- Quickly Concatenate Two Dates in Excel
- Quickly Generate Random Letters in Excel
- Quickly Use AutoSum in Excel
- R1C1 Reference Style in Excel
- Random Date Generator (Excel Formula) in Excel
- Randomize a List (Random Sort) in Excel
- Remove Commas
- Remove Extra Spaces in Excel
- Remove First Character from a Cell in Excel
- Remove Line Break from a Cell
- Remove Parentheses in Excel (Brackets)
- Remove the Last Character from a String
- Remove Unwanted Characters
- Reverse VLOOKUP (Backward: From Right to Left ) in Excel
- Round a Number to Nearest 1000 in Excel
- Round Percentage Values
- Round to Nearest .5, 5. 50 (Down-Up) in Excel
- Running Total
- Separate Date and Time in Excel
- Separate Names in Excel – (First and Last Name)
- Split a Text using a Space Between
- Square a Number in Excel
- Substitute Multiple Values (Nested)
- SUBTOTAL with IF (Conditional SUBTOTAL)
- Sum an Entire Column or a Row in Excel
- Sum Greater than Values using SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Not Equal Values (SUMIFS) in Excel
- Sum of Squares
- Sum Only Visible Cells in Excel
- Sum Random Cells in Excel
- Sum Time in Excel (Excel Formula)
- Sum Values Based on the Month (SUMIF)
- Sum Values Based on Year (SUMIF Year)
- Sum Values by Group
- Sum Values Less Than a Particular Value (SUMIF Less Than)
- Sum Values that are Greater than Zero (SUMIF)
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF By Date (Sum Values Based on a Date)
- SUMIF Non-Blank (Sum Values for Non-Empty Cells)
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- SUMPRODUCT IF to Create a Conditional Formula in Excel
- TRUE and FALSE in Excel (Boolean Values)
- Use SUMIF to Sum Blank Values or Empty Cells
- Using INDIRECT with SUM
- VLOOKUP Dates (VLOOKUP for a Date from Data)
- VLOOKUP from Another Sheet (Between Sheets)
- VLOOKUP MATCH Combination in Excel
- VLOOKUP with Multiple Criteria in Excel
- Weighted Average
- Wildcard Characters in Excel
- Wildcards with VLOOKUP in Excel
- XLOOKUP Return All Matches. Possible?
- XLOOKUP with Multiple Criteria
- Years Between Dates in Excel
10. Functions
11. Sorting
12. Formatting
- 5 Best Fonts for Microsoft Excel
- Add Barcode in Excel (Install Font)
- Add Border in Excel
- Apply Background Color to a Cell
- Apply Comma Style in Excel
- Apply Strikethrough in Excel
- AUTO FORMAT Option in Excel
- Change Border Color in Excel
- Change Date Format in Excel
- Clear Formatting in Excel
- Copy Formatting in Excel
- Default Font (Change Style, Size, and Color)
- Excel Cell Style
- Grey Out Cells in Excel
- Highlight Alternate Rows with Color Shade
- Highlight Blank Cells in Excel
- Highlight Top-Bottom N Values in Excel
- How to Hide Zero Values in Excel
- Insert Checkbox in Excel
- Insert Diameter Symbol in Excel
- Insert PI Symbol (π) in Excel
- Make Negative Numbers Red in Excel
- Merge Cells without Losing Data in Excel
- Excel Gridlines
- Remove Dashes in Excel
Recommended Books
Below are my two favorite Excel books for beginners which every person who is starting out with Excel should read.
- 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 […]
- 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 […]
Thank you
Thankyou
Thank you
Thank you sir
Very helpful thanks 👍
Thank you so much
Good
Good explanation sir
Thanks for u r valuable information….
Thanks Soo much wish I can have much knowledge about Microsoft Excel.
Thanks for sharing this important document.
Thanks for sharing this informative post
My name is Raunak jaiswal
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.
Thanks for the valuable information regarding Microsoft Execl. found useful from every aspect of features of software.
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
Very thoughtful and helpful content. Thank you so much for your work and time
This is very wonderful website for learning Excel components thank you very all team members of this site good work keep it up.
im satisfy with traning its really help full and undestanding
iwant to learn more thank you
excel is so good it helps in data management
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.
yes, for sure.
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
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
Well, that was “amazing” i’m greatful..
Thank you for this post it really helped me. Keep it up.
Thank you for this post it really helped me. Keep it up.
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.
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.
Thank you for your inconvenience.
Thanks very much for ur valuable info abt excel….it will help the beginners who are trying out a job has a fresher…..
thanks for all
that is my question too …
Very helpful for children
very helpful
not at all
I satisfy on these notes, good!
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?
Honestly I really appreciate your work , you’re doing great work it,s help me to perform job tasks
Wow! i love this write-up, it’s of help to me.
It is very good
Thanks
Sir Ms Excel Ka Hindi and English language me pdf note kaise milega
IT IS VERY GOOD
This is very helpful main points for ms excel .
Thanks
Thanks for this..
This is very helpful .
thx good notes
thanku
thank you so much
I’m so glad you liked it. 🙂
Thanks for your clear explanation about excel and its functions
Hi, I believe the column is vertical while the row is horizontal
Thanks a lot.
thanks a bunch
You’re welcome.
Yes
Hi…isnt that the column is vertical set and rows are horizontal…
Thanks for the correction.
Thanks for sharing this informative post. It is very useful the beginners, who are new to excel.
You’re welcome. 🙂
Thanks you Sir
Thank you Sir