MS-Excel is one of the most important tools to manage and analyze data in various fields either personal or professional. This tutorial will provide you complete MS-Excel knowledge from beginner to advance level that will help you to make MS-Excel pro. The best thing is about MS-Excel is that its scope is infinite. You will find it everywhere either in the Technical or Non-Technical field. Let’s start with step by step guide to learn MS-Excel.
Introduction to MS-Excel
MS-Excel is a computer application software and spreadsheet developed by Microsoft for Windows, Android, macOS, and iOS. It consists of lots of unique features e.g. calculation and analysis of data, integrating the information, graphing tools, pivot tables, chart, etc.
Any type of data that is used by an organization can be recorded and manage by MS-Excel.
This tutorial includes all MS-Excel topics e.g.
- MS Excel Themes
- Printing Options
- Data Tables
- Pivot Tables
- Pivot Charts
- Data Validation
- Filtering Data
- Data Sorting
- Cross Referencing in Excel
- Language Translation, etc.
This tutorial is written for beginners as well as advanced professionals and students. If you have no basic idea of Ms-Excel then no need to worry.
Before going further, you only have knowledge of basic computer terminologies such as a computer, monitor, mouse, keyboard, etc.
Best of Luck!
What is MS-Excel?
Microsoft Excel is the most powerful computer application software and spreadsheet function developed by Microsoft. It helps a user to manage record and analyze data. MS-Excel also helps to create charts and graphs. It can be used in various fields like balance a checkbook, create formulas, maintain bills and monthly expenses, etc. MS-Excel is available for Windows, macOS, Androids, and iOS.
It mainly comprises three parts:
- Groups of commands,
The combination of rows and columns made a spreadsheet table and the intersection point of rows and columns form a cell. Columns represent the alphabetical entries and rows represent the number of entries. A cell address represents the position of rows and columns. Let’s understand with some images:
In MS-Excel, every single box represents a cell.
Scope of MS-Excel
MS-Excel has a vast use in the daily life of a professional as well as the student. If you will go to a bank, IT office, market, etc. you will definitely find all are using MS-Excel to record their data. Further, some of the people maintains their daily expenses in MS-Excel itself. As it helps to analyze the monthly income and expenditure data easily. If you are a job seeker, then MS-Excel is a great tool that helps you to find a better job in the market. If you belong to Non-Technical background, then it is a great tool that will surely help to get you a dream job without any difficulty. So, we can’t explain the scope of MS-Excel on a single page as it is a tutorial itself.
Where to get MS-Excel?
There are lots of methods to get MS-Excel for your system. Either you can purchase it from any computer hardware showroom or you can also get it online from Microsoft’s official website (here).
Features of MS-Excel
1- Find and Replace Commands
You can find data in a spreadsheet easily and update new data by replacing old data with the help of MS-Excel.
2- Auto sum
It is used to add the data of different rows and columns.
3- List AutoFill
It is used to format the cell while a spreadsheet is updated
4- Password Protection
It allows a password protected spreadsheet that helps u user from unauthorized access.
5- Data Filtration
Data Filtration is done, whenever a large data is present in a spreadsheet. To find a particular data by their name, serial number, modification data, etc. It provides users two different commands to filter the data as follows:
6- Auto Filter
Used for finding easy access data in a spreadsheet.
7- Advanced Filter– Used for finding complex accessible data in the spreadsheet.
8- Add Header and Footer
To make the spreadsheet attractive, MS-excel provides Header and Footer in the document. The header is the head or topmost part of your spreadsheet while the footer is the lowermost part of your spreadsheet.
9- Data Sorting
“Sorting” is defined as the process to arrange the data either in an ascending or descending manner. Ms-Excel provides a facility for data sorting.
10- Auto Shapes
It provides its user to draw some shapes automatically with a toolbar. You can draw different shapes like squares, circles, arrows, and stars, etc.
11- Charts creation
It provides its user to create different chart creation e.g. Pie, Bar, and line, etc. This helps a user to analyze the data in graphical form.
12- Pivot Table
Pivot table is a tool to analyze complex data in pictorial form. It allows a user to flips and sums the complex data easily within seconds. It is mostly used for statistical data.
13- Formula Auditing/Create formulae
It helps a user to create different formulae to make calculation easy. You can create formulae for addition, subtraction, an average of different numbers, and many more. Further, we can define the relationship between cells and formulas with the help of Excel.
Auto edits the results
It provides a facility of auto-editing spreadsheet data whenever any change to be need.
It provides its users to correct their data automatically in the spreadsheet. If there is any grammatical mistake found, it corrects it automatically. And it provides a special feature in form of a dictionary. You can find synonyms, antonyms, and suggestions for the same word with the help of MS-excel.
Read Also: Salesforce full tutorial
How to open MS-Excel?
First install MS-Excel in your windows with a GUI like (windows XP, Vista, Windows 10, etc.). Further, follow the below steps:
- Click on the start menu or Windows Icon in the bottom left corner of your screen.
- Type Excel or MS-Excel in the search box and click Microsoft Office Excel (your Excel version).
- Now after click Microsoft Excel 2016, you will see the below the window on your screen.
- If you want to open a new file or existing recent documents that you have accessed in past, click on the Windows icon (represent with an arrow) and you will get the below screen with different options.
Explore Microsoft Excel Windows Environment
MS Excel is a combination of two different Windows,
Application Windows- This part comprises all applications of MS-Excel.
Workbook Windows- This part consist of the main workplace spreadsheet.
- Application Windows and Workbook Windows combination is called MS-Excel Windows and this is as follows:
Office Button/File Tab
Office button (2007) is now replaced in 2010 and the latest version. This is the first button in the MS-Excel spreadsheet. We can open new and recent spreadsheets with this button. Further, we can save, print, send, and publish the spreadsheet by this button.
Note– in Latest MS- Excel versions, this office button is replaced by File Tab.
Quick Access Toolbar
Quick Access Toolbar is present just above the office button and inside the Title Bar. This toolbar consists of most commonly used commands as save, new, open, undo, redo, sorting commands, etc.
Note: You can also add more commands from the drop-down option
The title bar is the topmost section in MS-Excel spreadsheets. It shows the title of the workbook.
MS-Excel replaced traditional menus from its older version with Tabbed Ribbon System. The Ribbon comprises the following three components:
Tabs appear on the top of a Ribbon with different tabs e.g. Home, Insert, Page Layout, etc.
This part of Ribbon shows the different commands and appears below the group on the Ribbon.
Ex– Fonts commands, Alignments, Number, Style, Cells, and Editing.
Each group consist of different number of commands.
Each group consist of different number of commands.
How to customize the Ribbon in MS Excel?
We can customize the Ribbon in Excel 2016 in few simple steps as follows:
Select the ribbon group that you want to customize and right-click on that command.
Click “Customize the Ribbon” from the drop-down menu. You will get the below screen on your Windows.
In below Windows, a dialogue box appears with the name Excel Option. Now select “New Tab” as shown in the screenshot.
We can also create a New Group and rename all groups with this process.
We can add or remove commands from the commands menus to the New Tab/New group which we are creating.
MS-Excel provides the facility of Drag and Drop to its users. You can Drag and Drop commands in order to create a New Tab.
After adding commands in New Tab, click the Ok button at bottom of the windows. Now you will find a New Tab is created and this will appear on the Ribbon.
The Name Box
The Name Box appears on the below section of Ribbon. It shows the Name and Location of the Cell in the spreadsheet.
In the above screenshot, the highlighted Cell is located on C3.
C– It represents the Row.
3– It represents the Column.
Formula Bar is used to enter and edit the data in any cell. And this is used for entering the formula and function that is applied in a particular cell.
The Backstage View (File Tab)
The Backstage view also named the File tab, is the main controlling station of the spreadsheet and located at the upper-left corner of the Ribbon. It is introduced in Excel 2010 and later versions.
The backstage view helps a user to create, save, open, print, share, export, and publish a spreadsheet.
These options have different functions as listed below:
Info– It shows the information of the current opened Excel spreadsheet.
New- It is used to open a new spreadsheet.
Open- It is used to open an existing spreadsheet.
Save- It is used to save the spreadsheet. If there is any update in the spreadsheet, you can also save it with this tab.
Save As- This tab is used to save the spreadsheet with file type and location (File location in the system, where the user wants to save this spreadsheet).
Print- This option is used to print the spreadsheet.
Share- This tab is used to share spreadsheets with people through Email or cloud share.
Export- This tab is used to create PDF/XPS documents.
Publish- This tab is used to publish the spreadsheet into power business Intelligence (BI). Power BI is used to create and share rich visual reports and dashboards from your spreadsheet.
Close- This tab is used to close the spreadsheet. A dialogue box appears to ask either for save, don’t save, or cancel the file.
Account-This tab is used to sign in the MS-Excel products. In this tab, you can manage settings, sign in to the office, update excel, and gather information about MS-Excel.
Feedback– This tab is used for product feedback. You can send a smile for liking this product, you can send a Frown for something you don’t like about this product and also send a suggestion if you have about the product.
Options- This tab is used to choose and change different options like General, Formula, Proofing, save, Language, Ease of access, advanced, Customize the Ribbon, etc.
The Workbook View
MS-Excel has a variety of display options to change the workbook view. We can change our workbook in three different desired views:
Normal View- To view a normal display of your spreadsheet.
Page Layout view- To view the Layout of the workbook windows.
Page Break View- To view the workbook in page break mode.
Zoom Option-This option is used to Zoom In and Zoom Out your workbook windows.
What is Workbook Windows?
In MS-Excel, a spreadsheet opens with max of 1,048,576 rows and 16,384 columns, called Workbook Windows.
All excel files come under Workbooks and each spreadsheet within a workbook is called a Worksheet. A Worksheet is a combination of Rows and Columns. When a user creates a new worksheet, it gets the name Sheet1. when the user creates another new worksheet, it gets the name Sheet2 and so on.
We can also create multiple worksheets for a large amounts of data. So, we can organize multiple worksheets same time.
How to Rename a Worksheet?
For a better understanding of the content of any file, we can rename the worksheets. Below are some steps to change the name of automatically created Worksheets to the desired name;
Right-click on the sheet name (Sheet1, Sheet2, etc.), which you want to rename, and select the Rename option from the drop-down menu.
Assign a new desired name to the worksheet and press enter anywhere on the windows.
Now the sheet name has been changed.
How to insert a new Worksheet in MS Excel?
You can insert a new worksheet by clicking on the ‘+’sign just right to worksheets.
How to Remove/delete Worksheets in MS Excel?
You can also remove or delete any worksheet. To delete a worksheet, just right click on the worksheet which you want to remove, and select the Delete option from the drop-down menu.
Now this worksheet has been deleted from your workbook.
How to change the default number of worksheets in your MS Excel workbook?
You can also change default sheets on your workbook in just simple steps:
Click to Backstage view/File tab in your Worksheet.
Now click “Option” as represented by the arrow mark below.
Now you can change the default number of sheets from the highlighted option as shown below.
How to Copy a Worksheet in MS Excel?
You can also make a duplicate copy of your worksheet. Below are the simple steps to copy a worksheet.
Right-click on the worksheet which you want to copy and select the Move or Copy option from the drop-down menu.
After clicking this a dialogue box appears to ask for the Before sheet option.
Now click the option “Create a copy” given below in dialogue box.
Now the worksheet has been copied with similar content and version number.
How to change the color of Worksheets in MS Excel?
You can also change the color of the worksheets so that you can easily identify your worksheet. Below are some steps to change the color of the worksheets.
Right-click on the worksheet for which you want to change color.
Now select the option “Tab Color” from the drop-down menu and select color from the theme colors as shown in the image.
Now your worksheet color has been changed.
What are Scroll Bars in MS Excel?
If you want to see the content that is not in front of the worksheet window or if there is a large amount of data in your worksheet. Then you can directly scroll the rows and columns to find the data which you want to check.
If you want to scroll your workbook Rows, then drag this Horizontal scroll bar to the desired location. And further, if you want to scroll columns, then drag the vertical scroll bar from top to bottom.
How to enter data/values in Excel Worksheet?
To enter data in an Excel sheet is very easy and quick. You can enter alphabetical data, numeric data, and formulas, etc. Below are some steps to enter data in an Excel sheet.
First, select any cell or location on a spreadsheet where you want to enter data or value.
After selecting a cell double click on that cell and enter data in it.
“The data that you have entered in the cell also appears in Formula bar as shown in the screenshot above. You can also change the data/value by entering it in Formula bar.”
After entering data in the cell either press the Tab key or Enter key.
How to select data in an Excel worksheet?
There are so many methods to select data over Excel Workbook. Some of the common are as follows:
First, click on the cell (From where you want to select data) and drag your mouse up to the cell, which you want to select.
To select the particular area of cells, press the shift + arrow key together till the desired cell and then release both keys together.