Spread Sheet MS Excel

1 Introduction to MS Excel

2 What is MS Excel ?

3 MS Excel interface 

4 Components of MS Excel.

Elements of Electronic Spreadsheet Window / इलेक्ट्रॉनिक स्प्रैडशीट विंडो के तत्व

Features Tools of MS Excel.

5 Layout of excel Header

Office button / ऑफिस बटन

Quick access toolbar / क्विक एक्सेस टूलबार

Title bar / टाइटल बार

6 Ribbon / रिबन

Home Tab in MS Excel.

Insert Tab in Ms Excel.

Page Layout Tab in Ms Excel.

Formulas Tab / bar / फार्मूला बार in MS Excel.

Data Tab in MS Excel

Review Tab in MS Excel

View Tab in MS Excel

7 Formula bar

8 Worksheet / वर्कशीट

Row & Columns in MS Excel.

9 Workbook / वर्कबुक

10 Absolute Reference cell  

11 Addressing cell

12 Formatting label

Advance Filter in MS Excel.

Shortcut Keys in MS Excel.

Formulas in MS Excel.

Addressing the cells

Absolute Cell Reference

Relative cell Reference

Mixed cell reference / सेल रिफरेन्स

Manipulation of cells

Formatting the label/ फोर्मटिंग

Editing the data of worksheet/ एडिटिंग

Functions and charts / चार्ट

Order of Evaluation of Formulas

1.Introduction to MS Excel-

Microsoft Excel is an example of a program called a “spreadsheet.” Spread sheets are used to organize real world data, such as a check register or a rolodex. Data can be numerical or alphanumeric (including letters or numbers).. The key benefit to using a spread sheet program    is that you can make changes easily, including correcting spelling or values, adding, deleting, formatting, and relocating data. 

You can also program the spread sheet to perform certain functions automatically (such as addition and subtraction), and a spread sheet can hold almost limitless amounts of data—a whole filing cabinet’s worth of information can be included in a single spread sheets.. Once you create a spreadsheet, you can effortlessly print it (as many copies as you want!), save it for later modifications, or send it to a colleague via e-mail. MS Excel is a very powerful  calculator—This handout covers  just a small number of its features!

MS Excel is available on both PCs and Macs, so what you learn in class today should be applicable to any computer you use. The program may look slightly different depending on the version and computer that you’re using, but Microsoft Excel will function in the same basic ways.. 

There are other spreadsheet programs out there, including Google Spreadsheets (part of Google Docs), Open Office Calc, Apple iWorks Numbers, Lotus 1-2-3, and WordPerfect Quattro.. They have many features in common with Microsoft Excel, and you should feel  free to choose any program you prefer.

2. What is MS Excel ?

The MS-Excel interface consists of groups of blocks arranged as rows and columns (also known as cells). Rows are arranged -->horizontally, while columns are arranged vertically. Note that each row specifies a particular entity, while each column specifies the attributes (features or properties) of each entity contained in the rows. 

MS Excel is a clever application that allows us to do more and more work in less time. There are columns and rows on each worksheet. The columns are lettered A to Z, then AA, AB, AC, and so on, and the rows are numbered 1 to 1,048,576. A cell address is made up of a column coordinate and a row coordinate. For example, cell A1 in the upper-left corner of the worksheet denotes column A, row 1.

The first columns contain the header/description of the information contained in subsequent columns, while the rows contain information (data) about individual entities/observations. The arrangement of rows and columns made up a Spreadsheet. Depending on the version of Microsoft Office installed on your computer, the Microsoft Excel different functions (menu driven) arranged in order of similarity of functions into tabs on its menu bar.

3. MS Excel interface -

MS Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculations, pivot tables, graph tools, macro programming, etc. It is compatible with multiple OS like Windows, macOS, Android and iOS.

A Excel spreadsheet can be understood 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.

The MS-Excel interface consists of groups of blocks arranged as rows and columns (also known as cells). Rows are arranged horizontally, while columns are arranged vertically. Note that each row specifies a particular entity, while each column specifies the attributes (features or properties) of each entity contained in the rows. 

The first columns contain the header/description of the information contained in subsequent columns, while the rows contain information (data) about individual entities/observations. The arrangement of rows and columns made up a Spreadsheet. Depending on the version of Microsoft Office installed on your computer, the Microsoft Excel different functions (menu driven) arranged in order of similarity of functions into tabs on its menu bar. These tabs include: Home, Insert, Layout, Formulas, Data, Review, View, help . 

3.1 MS Excel Uses:-

•  How to prepare the database.

•  For accounting work.

•  Mathematical passion.

•  Data Management.

•  Chart Creation.

•  Filter & Shorting Database.

•  How to prepare the report.

•  How did you fill the budget ?

•  Data formatting.

Why Should I Learn MS Excel?

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 vs.. expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data. Let’s illustrate this using the following image.

4. Components of MS Excel.

1. Keyboard review Opening Microsoft Excel

2. Pointer Shapes

3. Typing in Cells Formatting Cells in MS excel

4. Inserting Rows and Columns Sorting Data in MS excel

5. Basic Formulas and Cell Reference in MS excel

6. AutoSum and Excel Equation

5. Features of MS Excel.

There are a number of features that are available in Excel to make your task easier. Some of the main features are: AutoFormat - lets you to choose many preset table formatting options.

Cell. A cell, which is where a column and a row intersect, is where a user enters data.

Reference to cells. The location of a cell is indicated by this set of coordinates. While columns are vertical and given a letter, rows are horizontal and numbered.

Activated cell The cell that is currently selected is indicated by a green box.

Workbook. This Excel document includes one or more worksheets.

Worksheet. These are the various documents that make up a Workbook.

The worksheets tab. These are the tabs on the spreadsheet’s bottom left.

Headings for columns and rows. These are the cells with numbers and letters that are situated close to the edges of the columns and rows. When a header is chosen, the entire row or column is highlighted.

Formula. For a cell to produce a value, a formula can be any mathematical equation, cell reference, or function. Formulas must begin with the equals sign (“=”).

Bar formula This is the lengthy input field used to enter formulas or values into cells. It is situated next to the “fx” label at the top of the worksheet.

Address line. The number and letter co-ordinates of an active cell are displayed in this bar, which is to the left of the formula bar.

Filter. A user can use these rules to choose which rows in a worksheet to display. Under “Sort & Filter,” in the top right corner of the home bar, is where you’ll find this option. Rows that match particular values will be displayed if the auto filter option is chosen.

AutoSum - helps you to add the contents of a cluster of adjacent cells.Users can add multiple values with the help of this feature. Users can press the Alt and Equal keys while selecting the cells they want to add. Additionally, there is a button to turn on this function in the upper right corner of the home page, just below “Fill” and across from “Sort & Filter.”

List AutoFill - automatically extends cell formatting when a new item is added to the end of a list.

AutoFill - We can automatically copy data to multiple cells using this feature. A user can choose both cells in a series of two or more cells and drag the bottom right corner downward to automatically fill the remaining cells.

 This feature allows us to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text. AutoFill can also be used to copy functions. We can also alter text and numbers with this feature.

AutoShapes- toolbar will allow you to draw a number of geometrical shapes, arrows, flowchart elements, stars and more. With these shapes you can draw your own graphs.

List AutoFill - automatically extends cell formatting when a new item is added to the end of a list.

Wizard - guides you to work effectively while you work by displaying various helpful tips and techniques based on what you are doing. 

Drag and Drop - feature will help you to reposition the data and text by simply dragging the data with the help of mouse.

Charts - features will help you in presenting a graphical representation of your data in the form of Pie, Bar, Line charts and more.

PivotTable - This data summarization tool automatically sorts and computes data. This can be found on the far left, underneath the insert tab. flips and sums data in seconds and allows you to perform data analysis and generating reports like periodic financial statements, statistical reports, etc. You can also analyze complex data relationships graphically.

PivotChart. This chart provides graph representations of the data as a visual aid for the PivotTable. It is situated next to the maps under the center of the insert page. Source information This data is what is used to build a pivot table.

Shortcut Menus - commands that are appropriate to the task that you are doing appear by clicking the right mouse button.

Starting with MS Excel- 

To get started with MS Excel, you will need to locate and open the program on your computer. To open the program, point to Excel’s icon on the desktop with your mouse and double-click on it with the left mouse button.

 If you don’t see the Excel icon on your desktop, you’ll have to access the program from the Start Menu. Click on the button in the bottom left corner to pull up the Start Menu.. You may see theA Excel icon here, so click on it once with your left button. If you still don’t see it, click on “All Programs” and scroll through the list of programs until you find it. It may also be located in a folder called “Microsoft Office” or something similar—it will depend on your specific computer. Click once with your left button to open the program. MS Excel will then open a blank page called “Book1.”

Microsoft Excel Features

This is a close-up view of the Title Bar, where file information is located. It shows the name of the file (here, “Book1,” the default title) and the name of the program (“MS Excel”). You will be able to name your file something new the first time that you save it.. Notice the three buttons on the right side of the Title Bar, controlling the size and closing of the program....

Quick Access Toolbar

On the top left-hand side of the Title Bar, you will see several little icons above the File menu. These let you perform common tasks, such as saving and undoing, without having to find them in a menu. We’ll go over the meanings of the icons a little later.

The Ribbon Menu System

The tabbed Ribbon menu system is how you navigate through MS Excel and access various MS Excel commands. If you have used previous versions of MS Excel, the Ribbon system replaces the traditional menus..

At the bottom, left area of the spreadsheet, you will find worksheet tabs. By default, three worksheet tabs appear each time you create a new workbook. On the bottom, right area of the spreadsheet you will find page view commands, the zoom tool, and the horizontal scrolling bar.

Home Tab in MS Excel.

The most commonly used commands in Excel are also the most accessible. Some of these commands available in the Home Tab are given bellow. The functions in the home tab are arranged in order of their similarity into sub- headings which are:

i. Clipboard: for Cutting, copying and pasting, Pasting special, Format painter contents on the excel spreadsheet.

ii. Font: Here, we get to format your input data by using choice text font, font size, switch between increase and decrease font size, bolden, underline, Border, Font color, Font background color other actions on your entries.

iii. Alignment: Here, we get to align your entries Top Middle bottom Align, Left right center Align, Orientation (Rotation), , Increase Indent, Decrease Indent, wrap texts, merge and center texts, and other actions.

iv. Number: Here, we get to change the data type of the input entries these are Custom, Number, Currency, Percentage, date, time, Fraction, Increase & Decrease Decimal. This allows for easy performance of operations on the entries.

v. Styles: we get to format your input entries to allow for easy spotting of trends and patterns using bars, colors, and icons to visually highlight important values. We can also format tables, and style cells.

vi. Cells/Sheet : We get perform additional cell operations such as: inserting a new cell, deleting an existing cell/Sheet, and formatting cells/Sheet.

vii. Editing: We get to perform operations such as AutoSum,Fill, Clear, sorting&Filter  (either in ascending or descending order), find&select, quick analysis, and other editing operations.

viii. Analysis: here, you get to perform analysis to drive insights from the input data.

Formula Bar

The Formula Bar, also known as the Equation Editor, is generally found below the ribbon menu. The left side denotes which cell is selected (“A1”) and the right side allows you to input equations or text into the selected cell. There are two ways to input information into a cell. You may either select an individual cell and type the equation or text into the formula bar or type the equation or text directly into the selected cell.

Equations (for example, =SUM(D5+E5)) will automatically be hidden inside the cell and can only be viewed using the formula bar; the result of the equation will display in the cell.

 If any written text is longer than the cell width, then the spreadsheet will cover up any portion longer than the cell width. The information will still be in the cell, you just won’t be able to see it at all times.

Keyboard shortcuts can save time and the  effort  of  switching from the keyboard to the mouse to execute simple commands. Print this list of Excel keyboard shortcuts and keep it at your computer desk for a quick reference.

Action                                    Keystroke

Document Actions

Open a file                            CTRL+O

New file                                  CTRL+N

Save As                                   F12

Save                                        CTRL+S

Print                                         CTRL+P

Find                                         CTRL+F

Replace                                  CTRL+H

Go to                                       F5

Cursor Movement

One cell up                           up arrow

One cell down                       down arrow

One cell right                             Tab

One cell left                                  SHIFT+Tab Top of worksheet (cell A1)                             

                  CTRL+Home End of worksheet

(last cell with data)                         CTRL+End

End of row                                          Home

End of column                                                             CTRL+left arrow Move to next worksheet                                                CTRL+PageDown Formulas

Apply AutoSum                               ALT+=

Current date                                     CTRL+;

Current time                                    CTRL+:

Spelling                                               F7

Help                                                     F1

Macros                                                 ALT+F8

Selecting Cells

All cells left of current cell                                                   SHIFT+left arrow All cells right of current cell                                 SHIFT+right arrow Entire column                                       CTRL+Spacebar

Entire row                                        SHIFT+Spacebar

Entire worksheet                            CTRL+A

Text Style-

Bold                                                     CTRL+B

Italics                                                  CTRL+I

Underline                                           CTRL+U

Strikethrough                                   CTRL+5

Formatting-

Edit active cell                                  F2

Format as currency with                        SHIFT+CTRL+$ 2 decimal places

Format as percent with no              SHIFT+CTRL+% decimal places

CTRL+X                                               Cut

CTRL+C                                              Copy

CTRL+V                                               Paste

CTRL+Z                                               Undo

CTRL+Y                                               Redo

Format cells dialog box                      CTRL+1

Note: A plus sign in the above list indicates that the keys need to be pressed at the same time.

Pointer Shapes

As with other Microsoft programs, the pointer often changes its shape as you work in Excel. Each pointer shape indicates a different mode of operation. This table shows the various pointer shapes you may see while working in Excel.

MS Excel Typing in Cells-

Cells are the small rectangular boxes that make up the spreadsheet. The boxes are the intersection of columns (A, B, C, etc.) and rows .(1, 2, 3, etc.). To reference a cell, use the column and the row name. For example, the cell in the first column and first row is called “A1”. All the information entered into an Excel spreadsheet is entered into cells..

Click on a cell to begin typing in it. It is that easy! When you are finished typing in the cell, press the Enter key and you will be taken to the next cell down. You can then begin typing in that cell. We can easily navigate around the cells using your arrow keys.

Please keep in mind that the Formatting toolbar in Microsoft Excel 2007 is exactly the same as the one used for Microsoft Word 2007. The biggest difference between the two programs is that, in Excel, the format is set for each individual cell. So if you change the font and applied the bold option in cell C5, then this format will only be applied to cell C5. All remaining cells will remain in default mode until they have been changed.

Sometimes you may only wish to adjust the format of one particular cell. In this case, simply select the cell by clicking the mouse on it and make any necessary adjustments to the font, size, style, and alignment. Those changes will not carry over when you begin typing in a new cell.

Other times, you may wish to adjust the text format of a group of cells, entire rows, or entire columns in MS Excel.

In MS Excel, you can choose groups of cells in rectangular units—all the cells you select must form a rectangle of some kind. To select a group of cells, begin by clicking on the cell that would be in the upper-left hand corner of your rectangle. Hold down the Shift key on your keyboard and use the arrows (←, →, ↑, ↓) on the keyboard to expand the selection of cells, or click and drag your mouse.

Once the group of cells has been selected, you can make adjustments to the font, size, style, and alignment and they will be applied to all selected cells.

MS Excel Formatting Cells-

The cell width and height will usually need to be adjusted to view all the information entered into a cell.

To adjust the cell width, move the mouse pointer in between two cell columns in the column header. Hold down the left mouse button and drag the mouse left to shorten the width or right to expand the width. Notice that all cells within the column are automatically adjusted.

 Adjust the cell height using the same method. Move the mouse cursor between two rows, hold down the left mouse button and move the mouse up to decrease the height and down to increase the height.

Before you begin entering data into a spreadsheet, you may already know the width and height you want your cells to have. In this case, you can adjust all the widths and heights by doing the following:

 Select the “square” between Column A and Row 1. This will select ALL the cells in the spreadsheet. From the “Home” tab of the Ribbon Menu, within the “Cells” box, click on “Format,” and select Row Height. You will now be asked to enter a numerical value for height. The default value is 15, but you can enter your own height value (10, 20, 25, etc.).

Repeat the same steps for Column width. From the “Home” tab of the Ribbon Menu, within the “Cells” box, click on “Format,” and select Column Width. Note that the default value for the width is 8.43. Enter your own width value (5, 10, 15, 20, etc.).

For any given cell or selected cells, We can also format the way your data is represented within the cell(s). Select a single cell or multiple cells.. Again, from the “Home” tab of the Ribbon Menu, within the “Cells” box, click on “Format.” Select “Format Cells.” The format window will now appear, giving you a wide variety of options on how to format your cell..

Number – This allows you to choose how to represent the numbers that are entered into a cell (number, currency, time, etc.).

Alignment – This determines how the data will be aligned within the cell (left-side, centered, or right-side).

Font – Select the type of font to be used within the cells

Border – This option lets you choose what type of border, if any, you would like around the cells or some of the cells.

Fill – This allows you to change the background color of the cell.

Protection – This option allows you to “lock” cell information so that other users cannot make changes.

Excel Shortcuts -

• Editing Shortcuts

• Formatting Shortcuts

• Navigation Shortcuts

• File Shortcuts

• Ribbon Shortcuts

• Paste Special Shortcuts

• Clear Shortcuts

• Selection Shortcuts

• Data Editing Shortcuts

• Data editing (inside a cell) Shortcuts

• Other Shortcuts

• Advanced Excel Formulas ()

  • INDEX MATCH

  • IF combined with AND / OR

  • OFFSET combined with SUM or AVERAGE

  • CHOOSE 92 XNPV and XIRR

  • SUMIF and COUNTIF

  • PMT and IPMT

  • LEN and TRIM

  • CONCATENATE

  • CELL, LEFT, MID, and RIGHT functions

MS excel Cell References-

What if you want to be able to add numbers in two different cells, but those numbers might change and you don’t want to have to retype your equation every time they do? In situations like this, you can use cell references within your equation. This tells Excel to use whatever value is in a specific cell to calculate the equation. That way, if the number in one of the cells does change, your equation will automatically recalculate the answer.

In the example to the right, we want to add the contents of cells B3 and B4 (181 + 376). We could write our formula the way we did above, but then if we changed the value in cell B3 to 200, our equation wouldn’t reflect the sum of these two cells anymore. We would have to update the equation manually.

 Instead, we can write our equation using the names of the cells rather than the numbers in them. The name of

a cell is its column letter and its row number (A2, C3, etc.). To write a formula using cell references:

Type the numbers you want to add in two different cells.

 Click on the cell where you want the answer to the equation to appear.

Begin the equation by typing the = sign.

 Either click on or type in the name of the cell with the first number to be added (in this example, B3).

 Type the + sign.

Either click on or type in the name of the cell with the second number to be added (in this example, B4).  Your equation should look like this: =B3+B4

Press the Enter key.

 You will now see the answer appear in the cell – in this example, 557.

 Now, if you changed the value of cell B3 to 200, the answer that appears in the cell where you typed your equation will be 576 (200+376). It automatically recalculates when one of the numbers in a referenced cell changes.

 Once you have entered your equation, when you click on the cell with that equation it will highlight the cells it is referencing by drawing colored borders around them (see the example above). This helps you see if it is using the cells you want it to use or if you have made a mistake in typing the formula.

AutoSum and Excel Equations

One of the most powerful features of Excel is its ability to perform basic math functions on data. Excel can add, subtract, multiply, divide, find the average, and perform general counting functions on the numerical data that you enter. To enable this feature, highlight all of the cells in a column, plus one additional empty cell in which to display the result.

Select the AutoSum icon from the ribbon menu:  

If you click directly on the ∑, Excel will automatically add up the numbers you have selected. If you click on the little dropdown arrow next to it (▼), you will get the full choice of mathematical functions.

If you double-click on the cell in which the answer appears, you will see an equation that looks something like this (you will also see this equation in the Formula bar):

Let’s break down what exactly the equation means:

= indicates that you are starting an equation in this cell.

SUM tells the function to be performed. In this case, all the cells will be added together.

( ) The parentheses contain the cells that the function will be performed on.

D2 This is the first cell to be included in the addition formula.

D10 This is the last cell to be included in the addition formula.

: indicates that all cells between the first and the last should be included in the formula.

The spreadsheet will often “select” the cells that it thinks you wish to include. But you can manually change the cell range by typing into the Formula bar.

When you are ready to execute the formula, just press the “Enter” key.

Other mathematical functions you can perform from the AutoSum button include:

Average – This function will calculate the average of the selected cells. Count Numbers – This function simply counts the number of cells selected. Max – This function will return the highest value of the selected cells.

Min – This function will return the lowest value of the selected cells.

*Remember* Excel equations are similar to programming languages, so have some patience and if at first you don’t succeed, try again. Even Excel professionals create incorrect formulas on their first try.

Once you get an equation to work, you will technically be a computer programmer!

Keyboard Shortcut/Review MS Excel-

In order to use Excel effectively, you must input commands using both the mouse and the keyboard. The above image of a keyboard should look similar to the keyboard in front of you; learning just a few certain keys will help to improve your efficiency in typing as well as present you with more options within the program. The following is a list of commonly used keys that you may already be familiar with:

1.  Backspace: This key deletes Data backwards.

2.  F2 Function Key : This key used to rename any data.

2.   Delete: This key deletes input data letters/Number forward.

3.   Shift: This key, when pressed WITH another key, will perform a secondary function.

4.   Spacebar: This key enters a space between words or letters data.

5.   Tab: This key will indent what you type, or move the text to the right. The default indent distance is usually ½ inch.

6.   Caps Lock: Pressing this key will make every letter you type capitalized.

7.   Control (Ctrl): This key, when pressed with another keys, performs a shortcut.

8.   Enter: This key either gives you a new line next line, or executes a command.

9.   Number Keypad: These are exactly the same as the numbers at the top of the keyboard; some people just find them easier to use in this position.

10.  Arrow Keys: Like the mouse, ,these keys are used to navigate through a document or page.

CLOSING MS EXCEL

Saving Spreadsheets

When you finish your spreadsheet and want to leave the computer, it is important to save your work, even if you are printing a hard copy. To save your work in Excel, it is essential to know WHAT you are trying to save and WHERE you are trying to save it..

Click the Office Button, then hover your mouse over Save As. Select MS Excel Workbook.

You can change the filename that Excel has chosen just by typing a new one in the “File name” box at the bottom of the window that appears.

The My Documents folder on your computer’s hard drive is a good place to store your documents. A blank CD or a USB jump drive are great portable storage options and can contain a LOT of data..

Excel will automatically save your document with the suffix “.xls”–this is simply a tag that lets Excel know that your work is specific to this program and what version it is in. You do not have to type it–just highlight what is there (default is “Book1”) and write a new file name. You may also chose to save it in an older format so that it can be opened with older versions of Excel.

After the first save, you can just click “Save” to preserve your work. However, it is important to note that every following command of SAVE will overwrite your original file, creating the most up-to-date version.

If you would like to keep saving different versions of your worksheet, be sure to use the “Save As” function each time you save, using a slightly different name for each version.

Printing Spreadsheets MS Excel

 To print your Excel document, click on the Office Button, then click “Print” from the menu. From the window that pops up, you can make changes to your print job and release it.

As with all commands in Excel, you can make changes along the way. You can change the number of copies you would like to print, change the paper orientation, choose which printer you want to use, and more.

Other useful tools are the Print Preview function found alongside the Print command and the Page Setup function. Print Preview will allow you to look over an exact copy of what will come out of the printer before actually executing the print command. Page Setup will allow you to select the page order in which multiple pages will be printed and to determine if the Gridlines should be printed or not.

Finding More Help

 You can get help with Excel by clicking on the Question Mark symbol in the upper-right hand corner of the main menu bar or by pressing the “F1” button. This will take you to help from Office.com, Microsoft’s help website.

There are also many other resources and tutorials available online. You might try a Google search with the words “Excel 2010” and the function you are trying to perform. Ask your instructor for help finding these resources if you have any trouble.

Closing the Program

 Click on the Office Button, then click “Exit.” OR

Click on the X in the top right corner of the Excel screen.

It’s that easy!! If you don’t save before attempting to close the program, Excel will prompt you to save the file. Make sure you save if you don’t want to lose any changes!!

NOTE: Images and screen captures may differ from those seen on another system.

@informaticsasssistant