170

MS Excel Tutorial

MS Excel tutorial provides basic and advanced concepts of Excel. Our Excel tutorial is designed for beginners and professionals by keeping their requirements in mind.

Microsoft Excel is a computer application program written by Microsoft. It mainly comprises tabs, groups of commands, and worksheets. It stores the data in tabular form and allows the users to perform manipulation operations on them.

Our MS Excel tutorial will cover all topics from basic to advance, such as Introduction of MS Excel, worksheets, ribbon and tabs, functions, formula, MS Excel online, Excel VBA editor, data validations, conditional formatting, and more. Along with it, we will also show you the steps to download and activate MS Excel.

What is Microsoft Excel?

Microsoft Excel is an office use application designed by Microsoft. It comes with Office Suite with several other Microsoft applications, such as Word, Powerpoint, Access, Outlook, and OneNote, etc. It is supported in Windows as well as Mac operating system too.

Microsoft Excel is one of the most suitable spreadsheet programs that help us to store and represent the data in tabular form, manage and manipulate data, create optically logical charts, and more. Excel provides you the worksheet to create a new document in it. You can save the Excel file with .xls extension.

Note: We are using Excel 2016 for this Excel tutorial.

Worksheet

A worksheet is made of rows and columns that intersect each other to form cells where data is entered. It is capable of performing multiple tasks like calculations, data analysis, and integrating data.

In Excel worksheet, rows are represented by numbers and columns by alphabets.

A single Excel workbook can consist of several sheets, named Sheet1, Sheet2, Sheet3… SheetN. You can add one or more sheets to your Excel document.

Microsoft Excel Features

There are several features that are available in Excel to make our task more manageable. Some of the main features are:

1. AutoFormat: It allows the Excel users to use predefined table formatting options.
2. AutoSum: AutoSum feature helps us to calculate the sum of a row or column automatically by inserting an addition formula for a range of cells.
3. List AutoFill: It automatically develops cell formatting when a new component is added to the end of a list.
4. AutoFill: This feature allows us to quickly fill cells with a repetitive or sequential record such as chronological dates or numbers and repeated documents. AutoFill can also be used to copy functions. We can also alter text and numbers with this feature.
5. AutoShapes: AutoShapes toolbar will allow us to draw some geometrical shapes, arrows, flowchart items, stars, and more. With these shapes, we can draw our graphs.
6. Wizard: It guides us to work effectively while we work by displaying several helpful tips and techniques based on what we are doing. Drag and Drop feature will help us to reposition the record and text by simply dragging the data with the help of the mouse.
7. Charts: This feature will help you to present the data in graphical form by using Pie, Bar, Line charts, and more.
8. PivotTable: It flips and sums data in seconds and allows us to execute data analysis and generating documents like periodic financial statements, statistical documents, etc. We can also analyze complex data relationships graphically.
9. Shortcut Menus: The shortcut menu helps users to make the work done through shortcut commands that need a lengthy process.

How to Open Microsoft Excel?

In Windows 10 operating system, click on the Start button and search for the MS Excel application. If it is already installed in your system, it will appear here like this.

Double-tap on this icon to open the Excel.

When the Excel opens, an interface will appear like this. From here, you can create a new workbook, choose a template, and access your recently edited workbooks.

Create a new workbook

To create a new workbook, click on the Blank Workbook here.

A blank Excel worksheet will open and display to you.

Open an existing workbook

If you want to work with an existing workbook, you can either choose from the Recent list or click on the Open button to select from the specific location.

When you click the Open button, it will ask you to open the existing file from different locations, such as – Recent, OneDrive, This PC, and Browse.

We will go for Browse this time; it will directly take you to the local computer location. From here, you can choose the Excel file you want to open.

Choose a file from your computer and click on the Open button.

An existing Excel file that is stored on your local computer will open like this.

Setup the option to open the blank workbook automatically

In MS Excel, you can setup the option to open the blank Excel workbook by default whenever you start the Excel.

1. Click File then Options (Inside the More… in the right panel).
2. On the General tab, scroll down and go to the Start up options.
3. Here, uncheck the Shows the Start screen when this program starts checkbox and then click OK.
   
4. The next time you start Excel, it will open a blank workbook automatically.

Excel Interface

It is the main interface of an Excel worksheet, where we work and store our data. This interface contains various components. Before start working with Excel worksheet, you should be familiar with these components so that you can use the Excel application efficiently.

Once you get familiar with the Excel interface, you will able to identify the basic and most-used components of an Excel workbook. We have explained a bit about these components.

Quick Access Toolbar

The Quick Access Toolbar contains some common and most used commands of Excel, which users repeatedly need while working with Excel. By default, Save, Undo, and Repeat commands are added in the quick access toolbar.

It provides fast access to its users by adding most-used commands in it. This quick access toolbar is customizable. It means you can add other commands, whichever you need most.

Add commands to the Quick Access toolbar

Step 1: Click on the drop-down arrow to the right of the Quick Access toolbar.

Step 2: Select the command you wish to add in the quick access toolbar from the drop-down menu.

For more command, which is not available here, click on More Commands and choose from there.

Step 3: Here, we have selected command Print Preview and Print that has been added to the Quick Access toolbar along with other commands. You can see it here.

Excel Ribbon

Excel 2016 utilizes a tabbed Ribbon system instead of traditional menus. The Ribbon includes multiple tabs, each with several groups of commands. We will use these tabs to perform the most common function in Excel.

File, Home, Insert, Page Layout, Formula, Data, Review, View, and Help are the tabs consists by the Excel ribbon.

Each tab of Excel Ribbon contains its related operations list. For example, the formula tab contains all the mathematical, logical, text, string, finance, Date, and time functions.

To minimize and maximize the Ribbon

The Ribbon is designed to respond to our current function, but we can choose to minimize it if we find that it takes up too much screen space.

1. To click the Ribbon Display Options arrow in the upper-right corner of the Ribbon.
   
2. Select the desired minimizing options from the drop-down menu:
   • Auto-hide Ribbon: Auto-hide shows our workbook in full-screen mode and hides the Ribbon completely. To show the Ribbon, click Expand Ribbon command at the top of the screen.
     
   • Show Tabs: This option hides all command groups when not in use, but tabs will remain there. To show the Ribbon, simply click on any of the tabs.
     
   • Show Tabs and Commands: This option maximizes the Ribbon. All of the tabs and commands will always be visible to the user. This option is selected by default when we open Excel for the first time.
     

To Customize the Ribbon in Excel 2016

We can customize the Ribbon by creating our own tabs with whichever commands we want. Commands are always housed within a group, and we can create as many groups as we want to keep our tab organized. If we want, we can even add commands to any of the default tabs, as long as we create a custom group in the tab.

If we want, we can even add commands to any of the default tabs, as long as we create a custom group in the tab.

1. Right-click the Ribbon and then choose Customize the Ribbon from the drop-down menu.
   
2. The Excel Optionsdialog box will occur. Locate and select New Tab or New group, whichever you want to add.
   
3. Now, select a command from the left panel and click the Add button to the new customized tab/group. You can also drag the commands directly into a group.
   
4. When you are done adding commands, click OK. The commands will be added to the Ribbon in a new tab like this.
   

Note: You can also rename the tab and group name.

Formula Bar

In the formula bar, we can enter or edit data, a formula, or a function that will occur in a specific cell. It allows to write the function and formulas to manipulate the data.

In the image below, cell C1 is selected, and 2000 is entered into the formula bar. Note how the data contains in both the formula bar and in cell C1.

Name Box

The Name box presents the location or “name” of a selected cell.

In the image below, cell B4 is selected. Noted that cell B4 is where column B and row 4 intersect.

The Backstage View (The File Menu)

Click the File tab on the Ribbon. The Backstage view will emerge.

It is the backstage view of MS Excel and information about the options it contains.

The Worksheet Views

Excel 2016 has a variety of displaying options that change how our workbook is showed. We can choose to view any workbook in the Normal view, Page Layout view, or Page Break view. These views can be useful for several tasks, especially if we’re planning to print the spreadsheet.

To change the worksheet views, locate and choose the desired worksheet view command in the bottom-right corner of the Excel window.

Zoom Control

To use a Zoom control, click and drag the slider. The number to the right of the slider reverse the zoom percentage. It presents at the bottom right corner of the Excel worksheet.

By default, Excel view zoom percent is 100%.

Prerequisite

Before learning MS Excel, you must have the basic knowledge of Computer Fundamental.

Audience

Our MS Excel tutorial is designed to help beginners and professionals.

Problem

We assure that you will not find any problem in this MS Excel tutorial. But if there is any mistake, please post the problem in contact form.

---