How to use MS Excel?
MS Excel is a software application offered by Microsoft to store the data in digital form. Excel seems to be good and easy to store the data and perform manipulation operations on that data as well. Excel provides sheets having with rows and columns.
Note: There are 1,048,576 total number of rows and 16,384 columns in an Excel sheet.
Excel allows its users to perform unlimited operations on data. Even you can store your data on the cloud (OneNote, Microsoft backup store), so you will never face the data loss problem. It is simple to use but no as like as tally.
This chapter will guide you on basic and some advanced use of Excel. How can a beginner use MS Excel to store and manipulate data using some basic operations? We have created a complete tutorial of MS Excel with detailed steps. So, you can also follow it (https://www.javatpoint.com/excel-tutorial) to learn Excel in deep.
Why MS Excel used for?
It is important to know that why MS Excel is used for. So, MS Excel is a software tool used for storing and organizing the data in a computer system. It provides a platform to keep the data and records digitally. It is not just that; it also allows its users to perform various mathematical and manipulation operations over the data.
You can also keep your data online by logging in to your Microsoft account and take the backup of your files here. Therefore, you can access them anytime and anywhere. So, your data will always be available.
It provides complete
Benefits of using MS Excel
MS Excel offers many advantages of using it. Using MS Excel, you completely change your way of storing data.
Levels of MS Excel users
Usually, MS Excel users are categories into three types:
These are three levels of MS Excel users, which are decided based on the user's knowledge and expertise in Excel application.
What do you learn?
In this chapter, you will learn the basics of Excel, such as creating an Excel file, storing data, saving data, performing basic mathematical operations (sum, avg, min, max, and count), and many other tasks that a beginner usually needs.
If you are a beginner, you will find that we have discussed every single step from scratch. Therefore, you will not face any problems while using Excel, even for the first time. For detailed learning, follow our complete Excel tutorial. In this chapter, you will learn -
Search the Excel application in your computer system and open it. Currently, it is a blank Excel sheet containing no data in it.
We will create a list of some data like products, their price, and quantity of purchase. We will also provide some data in it and show you the basic mathematical operations on it.
Every time for each task, if you use the mouse, it takes time. So, here we have some shortcut keys that make the work easy and fast for the users.
Tab - Use the tab key to move to the next cell in Excel.
Ctrl + A - Select the entire data of an Excel sheet using this shortcut key.
Ctrl + C - Use this shortcut key to copy the data of cells.
Ctrl + X - Cut the data of cells.
Ctrl + P - Paste the cut data of the cells using this shortcut key.
Ctrl + Z - Usually know as Undo key to take the action back performed in Excel sheet.
Ctrl + Y - Usually know as Redo key to take action forward performed in Excel sheet.
Ctrl + B - Select the text and use this shortcut key to make the selected text bold.
Create an Excel sheet
First of all, we will create an Excel sheet containing products name, their price list, and quantity. For this, you have just to open the Excel sheet and enter the data in it.
Note: Select heading data and press the Ctrl + B key to bold the heading data.
In each column of first row of the Excel sheet, we have provided the heading to the table and in below rows, we will enter data accordingly.
Now, press Ctrl + S shortcut key to save the file in your computer system. It will take you to the Save As page.
Here, you can browse the folder in your computer system where you would like to save your created Excel sheet. Click on the Browse button here and search the folder.
We have saved this file named purchase.xlsx in the Documents folder (default folder chosen by Excel).
Perform Multiplication operation
Now, we will show how one can perform multiplication operations on Excel data. We will multiply the per product price with purchase quantity and get the total price of purchased products.
In Excel, the PRODUCT() function is used to get the multiplication of the cells. You can either manually type this function and provide parameter details if you are familiar the Excel formulas. Otherwise, select the cells, use this function and get the result automatically for ease of use.
Follow the steps below for this:
1. Go to the cell where you want to keep the calculated multiplication result.
2. Navigate to the Formula bar tab in the Excel menu bar. Here, in the Function Library section, you will find a Math & Triangle dropdown list that contains all the required mathematical formulas.
3. Click on this Math & Triangle dropdown list, scroll down and choose the Product from the list for multiplication.
4. A dialogue box will open by selecting their corresponding numeric cell number. If it is correct, press the Enter key.
5. The product of the selected cells will be pasted after calculating in the selected cell. You can get this in the below screenshot.
6. Similarly, you can perform the same product operations with other rows data. You can see that we have calculated product for all rows and paste the result in their corresponding cells.
In addition, you will get so many other in-built functions in the function library, such as Date & Time, Mathematical, Logical, Financial, recently used function list, and many more. In addition, Excel will also provide the string functions that can be applied to a text string.
Calculate sum of column
In Excel SUM() function is used to get the total of the cells/column data. You can either manually type this function and provide parameter details if you are familiar with these Excel formulas. Otherwise, select the cells, use this function and get the result calculated automatically for ease of use.
Now, we will show you how and where you can perform SUM operation on a column using the sum() function. To calculate the total of a column, we will use the in-built sum() function of Excel and get the total of that column. With the help of an example, we will describe each step in detail.
Follow the steps below:
1. First of all, open your Excel worksheet, and select the last empty cell of the column for which column you would like to calculate the total sum.
This cell will keep the calculated sum result.
2. Navigate to the Formula bar tab in the Excel menu bar, where you will get an AutoSum
Note: This AutoSum list contains the most used Math formulas in Excel. You can also customize this list according to your usage.
3. Click on this AutoSum dropdown button and choose Sum function from this list. The sum of the respective column will be calculated and paste into the selected cell.
4. It will automatically add up all the respective cells in that column, shown in the below screenshot.
Press the Enter key to get the total sum for the selected cell values.
Although, you can also specify the range.
5. The sum is calculated successfully for the Total price column. See the screenshot below:
By following the same steps, you can find the total sum of other columns.
Add or Delete a column to an Excel sheet
Excel allows its users to add and remove a column from the Excel sheet. Sometimes, there is a need to insert a column between the two columns to add up some more data. In this scenario, Excel allows the insertion of a new column into the Excel sheet. It also does not affect the other data of the Excel table.
Add a new column to Excel
1. Open the Excel sheet and select the column before which you would like to insert a new column. For example, column B.
2. Keep the mouse over the selected column and right-click the mouse. A menu list will open where choose Insert from here to add a new column before the selected column.
3. A new column will be added to the Excel sheet just before the selected column. Other columns have been shifted to the right in the Excel sheet.
We have given it a name to the newly inserted column, i.e., Type.
Delete an existing column from Excel
Now, we will delete this Type column and show you the steps for this.
1. Open the Excel sheet and select the column which you would like to delete, i.e., Type (column B).
2. Keep the mouse over the selected column and right-click the mouse. A menu list will open where choose Delete from here to add a new column before the selected column.
3. You can see that the Type column has been deleted successfully, and other columns present at the right have been shifted to the left.
Sort the Excel table data
Excel provides Sort() function to rearrange the table data in order. It sorts the stored data. So, it becomes easy to find any record in arranged Excel sheet. The sort() function allows the users to sort the data alphabetical, numeric, or date wise.
Steps to sort the data
Here, we will sort the entire table data by following few simple steps and rearrange the data.
Step 1: Open your existing Excel sheet whose data you want to sort. Here, we will sort the data by taking price as a sorting parameter.
Step 2: Select all data of the sheet to sort the entire Excel sheet using one column value. It means that when a column values sort, its associated column data will also be sorted.
Step 3: Now, navigate to the Data tab in the Excel menu bar, where you will get a Sort function option in Sort & Filter group.
Click on this Sort option that will open a popup dialogue box.
Step 4: In this dialogue box, select a column from the list on the basis of which you want to sort the data. We will choose the total price.
Step 5: Specify the order of sorting either smallest to Largest or Largest to Lowest and click on the OK button.
Step 6: See that the entire table data has been sorted successfully according to your Price column, and its associated data also has been sorted.
Similarly, data can be sorted based on another column parameter.
These all are some basic operations that can be needed at the beginner level. For deep knowledge, follow our complete tutorial (https://www.javatpoint.com/excel-tutorial).