Microsoft Excel PDF

## Chapter - 04 ### Microsoft Excel In class six, we read about MS-Excel, a software which helps us to manage large data and perform calculation. We also learnt to enter data in a worksheet. Now, we will learn some extra features of MS-Excel. ### 4.1 Introduction of MS Excel Microsoft Excel is a...

## Chapter - 04 ### Microsoft Excel In class six, we read about MS-Excel, a software which helps us to manage large data and perform calculation. We also learnt to enter data in a worksheet. Now, we will learn some extra features of MS-Excel. ### 4.1 Introduction of MS Excel Microsoft Excel is a spreadsheet program that is used to record and analyse numerical data. We all deal with numbers in one way or the other. We all have daily expenses which we pay for from the monthly income that we earn. For one to spend wisely, they will need to know their income versus expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data. Think of a spreadsheet as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns and numbers are usually assigned to rows. The point where a column and a row meet is called a cell. The address of a cell is given by the letter representing the column and the number representing a row. Let's illustrate this using the following image: | | A | B | C | D | E | F | G | H | I | J | |---|---|---|---|---|---|---|---|---|---|---| | 1 | | | | | | | | | | | | 2 | | | | | | | | | | | | 3 | | | | | | | | | | | | 4 | | | | | | | | | | | | 5 | | | | | | | | | | | | 6 | | | | | | | | | | | | 7 | | | | | | | | | | | | 8 | | | | | | | | | | | | 9 | | | | | | | | | | | | 10 | | | | | | | | | | | | Cell address box (A1) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Row numbers | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Formula bar | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Column letters | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Collection of rows and columns forming a spreadsheet (looks like a table) | | | | | | | | | | | ### 4.2 Opening of WorkBook in MS Excel There are different methods of opening Work Book in MS Excel and some methods are described below. #### 4.2.1 Opening a Blank Workbook in MS Excel 1. Click the File tab. 2. Click New. 3. Under Available Templates, double-click Blank Workbook. **Keyboard shortcut:** To quickly create a new, blank workbook, you can also press CTRL+N. #### 4.2.2 Opening of a new workbook on an existing workbook 1. Click the File tab. 2. Click New. 3. Under Templates, click New from existing. 4. In the New from Existing Workbook dialog box, browse to the drive, folder, or Internet location that contains the workbook that you want to open. 5. Click the workbook, and then click Create New. ### 4.3 Advantage and Disadvantage Excel has the ability to organize large amounts of data into orderlyspreadsheets and charts quickly. It is easy to enter and format the data. It has the ability to create the graphical or the visual representations of your data, and it is easy to integrate Excel with other business applications. Entering the data into Excel manually can take a very long time, especially if you have a lot of data to enter. The amount of time it takes to enter the data can be inefficient and can lead to boredom which leads to potentially costly inattentiveness. ### 4.4 The Cell, Row and Column A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1. Below is an illustrated example of a highlighted cell in Microsoft Excel; the cell address, cell name, or cell pointer "D8" (column D, row 8) is the selected cell and the location of what is being modified. | | A | B | C | D | E | F | G | H | |---|---|---|---|---|---|---|---|---| | 1 | Check number | Date | Description | Ammount | | | | | | 2 | 100 | 2/23/2015 | Water bill | $45.00 | | | | | | 3 | 101 | 3/20/2015 | Power bill | $67.00 | | | | | | 4 | 102 | 4/20/2015 | Internet Bill | $50.00 | | | | | | 5 | | | | | | | | | | 6 | | | | | | | | | | 7 | | | | Total: | $162.00 | | | | | 8 | | | | Check Balance: | $523.00 | | | | | 9 | | | | Available: | $361.00 | | | | | 10 | | | | | | | | | | 11 | | | | | | | | | | 12 | | | | | | | | | | 13 | | | | | | | | | | 14 | Row Header | | | | | | | | | 15 | | | | | | | | | | 16 | | | | | | | | | | 17 | | | | | | | | | | 18 | | | | | | | | | | 19 | Sheet tabs | | | | | | | | ### 4.5 The Cell Address A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1. Below is an illustrated example of a highlighted cell in Microsoft Excel; the cell address, cell name, or cell pointer "D8" (column D, row 8) is the selected cell and the location of what is being modified. | | A | B | C | D | E | F | G | H | |---|---|---|---|---|---|---|---|---| | 1 | Check number | Date | Description | Ammount | | | | | | 2 | 100 | 2/23/2015 | Water bill | $45.00 | | | | | | 3 | 101 | 3/24/2015 | Power bill | $67.00 | | | | | | 4 | 102 | 4/20/2015 | Internet Bill | $50.00 | | | | | | 5 | | | | | | | | | | 6 | | | | | | | | | | 7 | | | | Total: | $162.00 | | | | | 8 | | | | Check Balance: | $523.00 | | | | | 9 | | | | Available: | $361.00 | | | | | 10 | | | | | | | | | | 11 | | | | | | | | | | 12 | | | | | | | | | | 13 | | | | | | | | | | 14 | Row Header | | | | | | | | | 15 | | | | | | | | | | 16 | | | | | | | | | | 17 | | | | | | | | | | 18 | | | | | | | | | | 19 | Sheet tabs | | | | | | | | ### 4.6 Worksheet An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. A worksheet begins with row number one and column A. Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. #### 4.6.1 Insertion of New Worksheet Three new blank sheets always open when you start Microsoft Excel. Below steps explain you how to create a new worksheet if you want to start another new worksheet while you are working on a worksheet, or you closed an already opened worksheet and want to start a new worksheet. **Step 1:** Right Click the Sheet Name and select Insert option. #### 4.6.2 Renaming of Sheet To rename a worksheet tab, just follow these steps: 1. Double-click the sheet tab or right-click the sheet tab and then click Rename on its shortcut menu. The current name on the sheet tab appears selected. 2. Replace the current name on the sheet tab by typing the new sheet name. 3. Press Enter. #### 4.6.3 Deleting a Sheet Here is the step to delete a worksheet. 1. Right Click the Sheet Name and select the Delete option #### 4.6.4 Moving or Copying of a Sheet 1. Select Home > Format > Move or Copy Sheet. Or, you can also right-click a selected sheet tab, and then click Move or Copy. In the Move or Copy dialog box, click the workbook to which you want to move or copy the selected sheets. ### 4.7 Entering Data To the Cell In MS Excel, there are 1048576*16384 cells. MS Excel cell can have Text, Numeric value or formulas. An MS Excel cell can have maximum of 32000 characters. For inserting data in MS Excel, just activate the cell type text or number and press enter or Navigation keys. ### 4.7.1 Inserting Formula For inserting formula in MS Excel go to the formula bar, enter the formula and then press enter or navigation key. See the screen-shot below to understand it. ### 4.8 Formatting Cells When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). 1. Enter the value 0.8 into cell B2. #### 4.8.1 Adding font style, Design, and Color to the text and Cell You can assign any of the fonts that is installed for your printer to cells in a worksheet. **Setting Font from Home:** You can set the font of the selected text from Home >> Font group >>>>> select the font. #### 4.8.2 Deletion of data from cells To delete the actual cell selection rather than just clear the cell contents, follow these steps: 1. Select the cells, rows, or columns you want to delete. 2. Click the drop-down button attached to the Delete button in the Cells group of the Home tab. 3. Click Delete Cells on the drop-down menu. ### 4.9 Insertion of Pictures To insert picture in to the cell you have to follow some steps described below: 1. Open your spreadsheet in Excel 2010. 2. Select the cell into which you want to paste the picture. 3. Click the Insert tab at the top of the window. 4. Click the Picture button in the Illustrations section of the ribbon at the top of the window. 5. Browse to the picture that you want to add to your spreadsheet, select it, then click the Insert button. 6. Manually resize the cell so that the picture is contained within it. You can do this by clicking the right border of the column letter at the top of the spreadsheet, then dragging it so that the cell is wide enough for the image. You can then repeat this with the row number. Your finished cell and image will look something like the image below. ### 4.10 Use of Function The following steps have to be followed to insert function in Excel. 1. Open an existing Excel workbook. If you want, you can use this example. 2. Create a function that contains more than one argument. 3. Use Auto Sum to insert a function.... 4. Insert a function from the Functions Library.... 5. Use the Insert Function command to search and explore functions. * SUM * AVERAGE * AUTOSUM * COUNT ### 4.11 Creating of Charts A chart is a tool you can use in Excel to communicate data graphically. To create a chart: 1. Select the source Data for Which you want create the chart. 2. Click on Insert Tab 3. Select the required Chart Type