Microsoft Excel: All IN

Microsoft Excel: All IN

Introduction

What is Microsoft Excel?

Before the advent of computers, accountants diligently penciled in data on columnar pads, designed with as many as 13 columns and 40 rows per sheet. If one sheet couldn’t hold all the data, the accountant used a second sheet, and then a third, etc. The data in each column and row was usually subtotaled on each sheet, with a grand total at the end of the entire document. When a number had to be changed, the accountant would painstakingly re-add, re-subtotal, and re-total the row and column that held the revised number. Accountants penciled in their data and used up a lot of erasers.

Spreadsheet software revolutionized the process. Spreadsheets are documents laid out in rows and columns. One worksheet on Microsoft Excel can store 16,384 columns and 1,048,576 rows of data. It can be used to add, subtract, divide, multiply, average, count, create charts, and perform many more functions, some of which are highly complex. Best of all, if you change a number, the totals update automatically.

Microsoft Excel is the spreadsheet program most commonly used in business today. Companies use Microsoft Excel to prepare profit and loss statements, budgets, balance sheets, statistical analysis, etc. However, spreadsheets are also an incredible tool for individuals who need an easy way to prepare personal budgets, track investments, record expenses, balance checkbooks, keep lists which can be automatically sorted (rearranged), etc. It is a user-friendly software package. Once you understand the structure of spreadsheets, you will discover that keeping records can be an enjoyable process. You may find yourself volunteering to keep your club’s or organization’s records simply because you can do it easily and well. Let the fun begin!

How to Get Microsoft Excel

Microsoft Excel is part of the Microsoft Office software suite. Microsoft Office also includes Microsoft Word (a word processing program) and Microsoft PowerPoint (a slide presentation program). Microsoft Professional includes Excel, Word, PowerPoint, as well as Microsoft Outlook (an e-mail program), Access (a data base program), and Publisher (a program used to create signs, newsletters, etc.). The Microsoft products are designed to work together to create and combine letters, charts, mailing lists, etc. Individuals who are proficient in all the Microsoft Office products and who can produce professional integrated documents are prized by industry. Most personal computers now come equipped with Microsoft Office.

If your computer did not come with Microsoft Office you can subscribe and download Microsoft Excel and the rest of the Microsoft Office programs at https://www.office.com/.

Opening Microsoft Excel

How do you start the Excel spreadsheet program? The first step is to open the Start menu. The Start menu is accessed through the button, displaying the Microsoft Windows logo, called the START button, located in the lower left of your desktop screen. The Start menu contains the option ALL PROGRAMS or APPS which includes a list of all the available programs on your computer. To display the list, position your mouse arrow directly on top of the ALL PROGRAMS option. The ALL PROGRAMS option will become highlighted in blue. Click your left mouse button and the computer will display a list of programs you can access. There are so many programs on the computer that a scroll bar will be displayed on the right side of the programs list. Use the scroll bar to locate the option MICROSOFT OFFICE. A small yellow folder will be located on the left side of the MICROSOFT OFFICE option. The yellow folder symbol indicates there are more options located within the folder. In the list, find the MICROSOFT OFFICE folder. Highlight the MICROSOFT OFFICE folder with the mouse and click the left mouse button. The list of Microsoft Office programs will be displayed. Locate the option MICROSOFT OFFICE EXCEL in the new list. It will have a green X symbol in a square next to its name. Place your mouse arrow on top of the MICROSOFT OFFICE EXCEL option. It will become highlighted in blue. Click your left mouse button. The menu will disappear and a blank Excel spreadsheet will be displayed.

Opening Microsoft Excel: Step by Step Instructions
  • Click the START button to open the Start menu.
  • Click the ALL PROGRAMS option.
  • Click the MICROSOFT OFFICE option.
  • Click the MICROSOFT OFFICE EXCEL option.
Microsoft Excel Screen Layout

Microsoft Excel is now open on your computer screen. Before you start typing data, take a moment to look at the basic layout. Look at the Title Bar of the program window. It is a bar at the top of your open screen/window. The title bar will read “Book1-Microsoft Excel.” “Book1” is the generic name given to a blank spreadsheet. If you open another new blank spreadsheet, it will be assigned the name “Book2.” The words Book, Workbook, or Spreadsheet are used interchangeably to describe a file created using Microsoft Excel. When you save the book for the first time, Excel will ask you to give the spreadsheet a new name. After completing the save process, the new name you selected will be displayed in the title bar of the Excel window.

Located just below the title bar is the ribbon. The top of the ribbon contains a group of tabs which allows you to change the options being displayed on the ribbon. The ribbon has eight different tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. If you look just below the tabs, you will see an area filled with tiny symbols and pictures. These tiny symbols and pictures make up the remainder of the ribbon. Each symbol on the ribbon represents a different function of Microsoft Excel. As you can see, Microsoft Excel has the ability to perform many different tasks. As this book progresses, we will review many aspects of this ribbon.

Under the ribbon is the Quick Access Toolbar. This bar displays buttons which work independently of the ribbons. The Quick Access Toolbar contains the Save button, the Undo typing button, and the Repeat typing symbol (commonly called the Redo button). If you make an error, you can simply click the Undo button and your latest instruction to the computer will be undone. This incredibly useful button will be discussed in more detail later in this section.

Overview of the Ribbon

The tabs located at the top of the ribbon control which symbols are displayed on the ribbon. If you place the mouse arrow on top of the Insert tab and click the left mouse button, a new set of symbols which support the Insert process, will be displayed on the ribbon. If you click on the Page Layout tab, another set of symbols, relating to the Page Layout, will be displayed on the ribbon, and so on. In summary, the different tabs help categorize the numerous functions of Microsoft Excel.

You can select any option located on the ribbon by placing your mouse pointer over the desired option and clicking the left mouse button once. When you position the mouse pointer over an option, it will become highlighted in orange. If you hold the mouse steady, without moving, a small balloon will appear, giving you the name of the option and providing a brief description of its use. To make the selection, just click the left mouse button and the action will be performed. At the bottom of the ribbon are category titles which give you a hint of what a particular set of options does.

Using the Ribbons: Step by Step Instructions
  • Click a ribbon tab (example: The HOME tab).
  • Move the mouse arrow over the ribbon.
  • Place the mouse arrow on top of the desired option.
  • Click the left mouse button.
Summary of the Ribbon Functions: File Tab
  • Save & Save As – Creates a permanent copy of your workbook
  • Open – Opens a previously saved workbook
  • Close – Removes the workbook currently active on the screen
    Info – Gives details on the Properties, Permissions, Sharing, and versions of the current workbook
  • Recent – Lists recently opened workbooks
  • New – Creates a new, blank workbook
  • Print – Prints a copy of the workbook
  • Save & Send – Saves the workbook and then sends it out via email
  • Help – Opens a Help window for getting Excel tips and helpful data
  • Options – Opens an Options window listing all the tools and options available in Excel and gives you the opportunity to change them
  • Exit – Closes all the open Excel workbooks and turns off Excel
Summary of the Ribbon Functions: Home Tab
  • Clipboard – Cut, Copy, Paste, Format Painter
  • Font – Font, Font Size, Bold, Italicize, Underline, Color, etc.
  • Alignment – Alignment, Merge, Change Indent etc.
  • Number – Type of Number, Dollar, Percent, Increase/Decrease Decimal Point, etc.
  • Styles – Overall cell formatting using predetermined settings
  • Cells – Insert, Delete, Format
  • Editing – Sum, Sort, Find, Replace, Select
Summary of the Ribbon Functions: Insert Tab
  • Tables – Insert a Table
  • Illustrations – Pictures, Clip Art, Shapes, etc.
  • Charts – Insert a Chart and other Chart options
  • Sparklines – Insert Sparklines into a cell to show trends
  • Filter – Insert an interactive filter called a Slicer
  • Links – Hyperlink
  • Text – Word Art, Text Boxes, Signature Lines, Date, Time, Etc.
  • Symbols – Inserting Equations or Symbols
Summary of the Ribbon Functions: Layout Tab
  • Themes – Change the overall design of the document including colors, fonts, and effects
  • Page Setup – Margins, Page Orientation, Breaks, Print Area, etc.
  • Scale to Fit – Width, Height, Scale
  • Sheet Options – Guidelines, Headings
  • Arrange – Bring to front, align, etc.
Summary of the Ribbon Functions: Formulas Tab
  • Function Library – Insert Function, AutoSum, Functions grouped by type
  • Defined Names – Define Name – a name is shorthand that makes it easier to understand the purpose of a cell reference, formula, etc.
  • Formula Auditing – Trace Precedents, Trace Dependents, etc.
  • Calculations – Calculation options
Summary of the Ribbon Functions: Data Tab
  • Get External Data – Import data options
  • Connections – Create and edit connections to external data sources that are stored in a workbook
  • Sort & Filter – Ascending/Descending Sort and Filter options
  • Data Tools – Text to Columns, Consolidate, etc.
  • Outline – Group, Ungroup, Subtotal
Summary of the Ribbon Functions: Review Tab
  • Proofing – Spell Check, Thesaurus, etc.
  • Language – Translate the workbook into a different language
  • Comments – Add/delete/show comments on the spreadsheet
  • Changes – Track your revisions to the document and restrict other people from making specified changes to the document
Summary of the Ribbon Functions: view Tab
  • Workbook Views – Normal, Page Layout, Page Break Preview, etc.
  • Show/Hide – Gridlines, Formula Bar, Headings
  • Zoom – Magnify or Shrink pages
  • Windows – Open New Window, Freeze Panes, Split Window, Tile All Windows on the Screen, etc.
  • Macros – Macro options
Using the File Tab

The left-hand side of the File tab contains twelve different topics. Notice that some topics such as NEW and PRINT are larger than the others. When you click on one of the larger options, a carat appears to the right of the option. This carat indicates that there is more information related to this topic heading. Clicking the option will cause the rest of the information to appear in the right pane of the File tab. In this example, place your mouse arrow on top of PRINT. PRINT will be selected and a preview of the document will appear to the right of the pane/ribbon.

Once the additional information has appeared, move your mouse arrow on top of the desire option and click the left mouse button one time to activate the selected option. In this example, move your mouse arrow on top of the option PRINT. It will become highlighted. Once PRINT is highlighted, additional options will appear to the right of the pane. Click on the options you would like to change. Since, at this point, we are only using the PRINT option as an example, click the HOME ribbon to exit the print screen.

Using the “Formula” Bar

Located below the Quick Access Toolbar is another bar containing the Name Box and the Formula Bar.  The Name Box displays the position of a selected cell.  The Formula Bar shows the details of any data contained in a cell.  As you enter data into a cell, the Formula Bar will display what you are typing.  Immediately below the Formula bar is the Excel Work Area, displaying a blank spreadsheet where you will enter your data.

Using the Worksheets Tabs

At the bottom of your workbook is another bar which contains the worksheet tabs and a scroll bar.

Each workbook (Excel document) is divided into worksheets (commonly called spreadsheets) which can be used to separate your data into sections. The worksheets start with the generic names of Sheet1, Sheet2, and Sheet3. Take a look at the worksheet tab named Sheet1. The Sheet1 tab has a white background indicating that Sheet1 is the active sheet. The active sheet is visible on screen at the present time, waiting for you to enter data. The tabs for Sheet2 and Sheet3 are light blue, indicating that they are inactive. Position the mouse pointer over the Sheet2 tab and click the left mouse button one time. The Sheet2 tab will turn white, and the Sheet1 tab will turn light blue. Sheet2 is now the visible/active worksheet. You can enter data on multiple worksheets and use the worksheet tabs to switch easily from one sheet to another. You will learn how to rename the worksheets, add additional worksheets, and link data contained on multiple worksheets later in this guide.

Switching Worksheets: Step by Step Instructions
  • Position the mouse pointer over the desired worksheet tab located near the bottom left side of the Excel window.
  • Click on the left mouse button and the corresponding worksheet will be displayed on the screen.
Using the Worksheets Scroll Bars

To the right of the worksheet tabs is the horizontal scroll bar. The horizontal scroll bar is used to move your screen to the right and left (horizontally). When you are working on a large spreadsheet, you will not be able to see all the columns and rows on the screen at one time. The number of columns and rows you can see depends upon your screen size, settings, and the length of the data contained in the cells. You should be able to view approximately 12 columns and 24 rows on a blank worksheet. You can view the data which is not visible on the screen by using the scroll bar. By moving the horizontal scroll bar, you will be able to see the remaining 16,362 columns available on the Excel worksheet.

To the far right side of the Work Area is the vertical scroll bar. The vertical scroll bar enables you to move the viewing area of the worksheet up and down (vertically) to display additional rows of the worksheet. The vertical scroll bar provides the means to view the remaining 1,048,532 rows available on the current worksheet.

Review of the Spreadsheet Layout
  • Title Bar – displays the name of the currently active workbook.
  • Ribbon – contains pictures/icons used for easy access to the options/features of Excel including Formatting, Copy, Paste, Insert, Insert Function, etc.
  • Quick Access Toolbar – A customizable bar containing frequently used feature buttons.
  • Name Box – displays the selected cell’s position.
  • Formula Bar – displays the details of the data you have entered into the selected cell.
  • Work Area – contains the cells in which you can enter data.
    Worksheet Tabs – separates your data into sections (each spreadsheet initially has three worksheets).
  • Horizontal Scroll Bar at the bottom of the worksheet – moves your screen to the right and left so that you can see data that is outside of the screen viewing area.
  • Vertical Scroll Bar on the right side of the worksheet – moves your screen up and down so that you can see data that is above or below the screen viewing area.

Entering Data

Entering Data Overview

To understand Microsoft Excel, you must understand the worksheet layout. The basic worksheet layout of Microsoft Excel consists of columns (vertical) rows (horizontal) and cells (where the column and row join). You must select a cell to tell the computer where on the worksheet you desire to enter data.

  • Columns are named using letters, from A through XFD.
  • Rows are named using numbers, from 1 through 1,048,576.
  • Cells are named by the intersection of the column letter and the row number. For example, C5 = column C, row 5. F16 = column F, row 16.
Selecting Cells Using the Arrow Keys

First, you need to learn how to move from one cell to another. When you start a new Excel spreadsheet, your cursor will automatically be on cell A1. Look at the upper left corner of the spreadsheet – there is a black border around the first cell in Column A, Row 1. Also, the cell address “A1” is shown in the Name Box located immediately above and to the left of the main work area. Use your arrow keys to move your cursor around the screen and note how the cell address changes each time you move. The arrow keys are the simplest way to move around the spreadsheet.

NOTE: You can also move to another cell by using your mouse. Simply move your cursor to the cell you want to select, and click your left mouse button once.

What Can You Type Into A Cell?

You can type words (text), numbers, and/or symbols into a cell. A cell that contains only words or letters is considered a text cell. Text cells are commonly used to label rows and columns on a worksheet. In a few moments you are going to begin creating a budget for a typical household. Before you begin typing in the specific numbers, you will label the rows and columns. The labels will indicate what the data in each specific row or column represents. Labels are text cells placed at the top of a column or to the far left of a row. If you enter numbers without labels, when you look back later you may not know what those numbers represent.

A cell can also contain numbers. A cell which contains only numbers can be used in calculations. The majority of the data entered into a spreadsheet are numbers. In the example budget, you will enter both income and expenses for a household. After the specific numbers are input, you will calculate how much extra money is left. Only cells which consist solely of numbers can be used in the calculation.

Symbols (+ – / * @ %) can also be typed into a spreadsheet cell. Symbols are used in Excel to perform mathematical calculations. Limit the use of symbols until you are ready to perform a calculation.

NOTE: You may combine text and numbers in one cell, for instance “M28468” or “Salary 1,000” but Excel will identify the cell containing text and numbers as a text cell. A cell which contains text cannot be used in calculations. So, if you intend to use specific cells in a calculation, it is important to keep the text in other, separate cells.

NOTE: If you want to type a number or symbol into a cell and not have it treated as a typical number or calculation, you should begin by typing in a single quotation mark ‘. The single quotation mark indicates that the number or symbol in the cell should be treated as text. The single quotation mark will not appear in the cell, but can be seen in the Formula Bar.

Excel aligns the data in a cell differently depending on which type of data the cell contains. Text will be aligned to the left side of the cell. Numbers will automatically be aligned to the right side of the cell. The alignment of the data within the cell helps you identify whether the data is considered text or a number.
NOTE: If you want to type a number or symbol into a cell and not have it treated as a typical number or calculation, you should begin by typing in a single quotation mark ‘. The single quotation mark indicates that the number or symbol in the cell should be treated as text. The single quotation mark will not appear in the cell, but can be seen in the Formula Bar.

Excel aligns the data in a cell differently depending on which type of data the cell contains. Text will be aligned to the left side of the cell. Numbers will automatically be aligned to the right side of the cell. The alignment of the data within the cell helps you identify whether the data is considered text or a number.

Now that you understand the basic layout of an Excel spreadsheet, you can begin entering data for the example household budget. The first step is always to select the cell in which you desire to type. Remember the selected cell will have a thick bold black border. Use the arrow keys on the keyboard to move the cell selection indicator (cursor) to cell B1. The bold black border around the cell will indicate the cell is selected.

To begin the example, add labels at the top of columns B through E. Each column will represent one month of the year. Once cell B1 is selected, type the word “January” into the cell. Using the right arrow key on the keyboard, move to cell C1. Label column C “February” by typing the month in cell C1. Continue to use your right arrow key to move to the adjacent cells. Type “March” into cell D1 and “April” into cell E1. Later, you will learn how to automatically fill-in the dates/months instead of having to type in each one.

Entering Data into a Cell: Step by Step Instructions
  • Select the cell where you desire to type.
    • The selected cell will have the bold black border.
  • Type the data in the cell.
  • Repeat steps 1 and 2 to add additional data to the spreadsheet.
Entering Data: Try it out

Now, add labels to the rows on the spreadsheet to indicate what income and expenses will be included in the example household budget. Using the left arrow key on the keyboard, move back into the first cell in Column 1. Look at the Name Box. A1 should be displayed in the box. Use the down arrow key to move to cell A2. Type the word “Income.”

NOTE: While you are typing the data into a cell, what you are typing is also being displayed in the Formula Bar located at the top of the screen. The Formula Bar allows you to see the entire contents of the selected cell.

Repeat the steps to enter the data, listed in the following table, into the spreadsheet. Remember to use the arrow keys to move the cell selector to the appropriate cell before you begin typing.

You may notice that the word “Entertainment” does not fit into cell A14. That is okay. If there is nothing in the adjoining cell, the word “Entertainment” will overflow into the adjacent cell. If there is something in the adjoining cell, the overflowing word will be cut off at the edge of the cell. You will learn how to increase the size of the columns and rows to fit the entire text later in the book. Congratulations, you have completed the basic budget outline.

Correcting Mistakes

Occasionally you will misspell a word while you are typing and not notice until later. At that point, you have two options. The first option is to select the cell containing the misspelled word and simply retype the word. Anything currently contained within the cell will automatically be replaced when you begin typing. The newly typed word will replace the misspelled word.

If the data you have typed in the cell is long or cumbersome to retype, you may prefer a second option. Since the data contained within a selected cell is displayed in the Formula Bar, you can move the cursor into the Formula Bar to the mistake’s position. The Formula Bar is just like a sheet of paper, and the cursor can be repositioned to the appropriate location within the text. You can use the Backspace key or the Delete key on the keyboard to erase the mistake and type the correction. The best way to understand this principle is with a demonstration. If you want to change the word “Entertainment” in cell A14 to “Entertaining” without typing the whole new word, you have to use the Formula Bar.

To do this, first select cell A14 using the arrow keys. Look in the Formula Bar at the top of the screen. The word “Entertainment” will be displayed in the Formula Bar. Move the mouse pointer over the Formula Bar and position the mouse pointer between the n and m in the word “Entertainment.” The location of the mouse pointer will determine the position of the blinking cursor. Click the left mouse button. The Formula Bar will become the active work area, and the blinking cursor will appear.

If the cursor is not positioned next to the letter m, use the arrow keys on the keyboard to move the cursor to the correct position. Press the delete key until the letters “ment” are erased, and then type in the letters “ing.” When you have made the correction, and “Entertainment” has been changed to “Entertaining,” press the Enter key on the keyboard to tell the computer you are finished and to exit the Formula Bar. Your correction will now be displayed in the cell A14.

Option 1:
Correcting Mistakes within a Cell: Step by Step Instructions
  • Select the cell where the mistake occurred. The selected cell will have the bold black border.
  • Type the correct data into the cell.
Option 2:
Correcting Mistakes within a Cell: Step by Step Instructions
  • Select the cell where the mistake occurred. The selected cell will have the bold black border.
  • Position the mouse pointer over the Formula Bar at the mistake’s location.
  • Click the left mouse button to activate the Formula Bar and to drop the blinking cursor at the location of the mouse pointer.
  • Erase the mistake using the Delete or Backspace key on the keyboard. (Remember: The Backspace key erases to the left of the cursor and the Delete key erases to the right.)
  • Type the correction.
  • Press the Enter key on the keyboard.
Correcting Mistakes: Try it out

Using Option 1 for correcting mistakes, change “Entertaining” back to “Entertainment” by selecting cell C14 and retyping the word.

Selecting Cells Using the Mouse

Now, begin to enter the specific budget numbers into the spreadsheet. This time, instead of using the arrow keys to select the cell in which to type, try using the point and click method via the mouse.

The point and click method is accomplished by moving the mouse pointer over the cell you desire to select. When the mouse is in the correct location on the spreadsheet, click the left mouse button to select the cell. The bold, black selection box will appear around the chosen cell. Once the cell has been selected, you can type in the cell data. For the example, move your mouse pointer over cell B3 and click your left mouse button. Notice, cell B3 will have the thick black box surrounding the cell. Type the monthly salary — in this example 2,500. To move to another cell, simply move your mouse onto the cell and click your left mouse button to select it.

Selecting Cells and Entering Data: Try it out

To continue with the example, select cell B4. Position your mouse pointer over cell B4 and click the left mouse button. Type the number 850 in cell B4. Continue using the point and click method to select cells and enter the values found in the following table. Remember, if you not sure which cell is currently selected, look at the Name Box located at the top of the screen.

Entering Data Using the Enter Key

Move into the next column. Position the mouse pointer over the cell you desire to select, in this example cell C3, and click the left mouse button to select the cell. The bold, black selection box will appear around cell C3. Once the cell has been selected, type in the monthly salary — in this example 2,500. Now, instead of using your arrow key or cursor to select the next cell, simply press the Enter key on the keyboard. Excel is programmed to move down to the cell immediately below the selected cell each time you press the Enter key.

Continue with the example and type in the data located in the following table.  Remember to press the Enter key on the keyboard to move the next cell.  If you are not sure which cell is currently selected, look at the Name Box located at the top of the screen.

Entering Data into a Cell: Step by Step Instructions
  • Select the cell where you want to type. The selected cell will have the bold black border.
  • Type the data into the cell.
  • Press the Enter key located on the keyboard.
  • Repeat steps 1, 2, and 3 to add additional data to the spreadsheet.

Copying and Pasting

Copying and Pasting Cell Data

Assume your budget is the same for the remaining months. Instead of retyping the data in the remaining cells, you can use the Copy and Paste option in Excel. The Copy and Paste option allows you to select data, copy it into the computer’s memory, and then paste the data into another cell, or multiple cells, on the spreadsheet.

In the example, you are going to use the mouse to select and copy the data located in cell B3. Move the mouse pointer over cell B3 and press the left mouse button. The bold, black selection box will appear around the chosen cell. Once the cell has been selected, move your mouse pointer over the COPY button located in the Clipboard section of the Home ribbon. The COPY option looks like two pieces of paper, one on top of the other.

Position the mouse pointer over the COPY option and click the left mouse button one time. The selected cell data will be copied into the computer’s memory. Take a moment to look at the selected cell. The cell border is moving, indicating that you have successfully copied the data contained within the cell. The computer is now awaiting instructions to specify where the copied data should be placed.

Move your mouse pointer over cell D3 and click your left mouse button. The bold, black selection box will appear around cell D3 indicating the cell has been selected. Once the cell has been selected, move your mouse pointer over the PASTE option located in the Clipboard section of the Home ribbon. The paste option looks like a clipboard with a sheet of paper on it. Position the mouse pointer over the PASTE option and click the left mouse button once. Notice the data from cell B3 has been copied into cell D3.

NOTE: After you paste the information into the appropriate cell, a little tag may appear next to the pasted information. This tag is called a Smart Tag. Smart Tags indicate that more options are available. Most people just ignore the Smart Tags and continue working. If you ignore the tag, it will eventually disappear on its own. To force the Smart Tag to disappear, hit the Esc key on your keyboard. If you click on the Smart Tag, additional options will be displayed under the tag.

Copying and Pasting Cell Data: Step by Step Instructions
  • Select the cell containing the data you want to copy.
  • Click on the COPY option located in Clipboard section of the Home ribbon.
  • Select the cell where you want the copied text to be placed.
  • Click on the PASTE option located in the Clipboard section of the Home ribbon.
Copying and Pasting Cell Data: Try It Out

For practice, move the mouse pointer over cell E3 and click the left mouse button. Cell E3 will become highlighted. Then select the PASTE option on the ribbon using the mouse. Notice, the same data you copied from cell B3 has been pasted into cell E3. As long as the cell you copied the data from still has the rotating border, the data is still stored in the computer’s memory.

Now that you have successfully copied and pasted the value of “Salary 1” from cell B3 into cells D3 and E3, you can either repeat the process to fill in the data for the remaining cells or you can copy and paste data from multiple cells at one time. Copying and pasting data from multiple cells uses the same basic steps used to copy and paste data from one cell to another. The only exception is that you need to select multiple cells before you copy the data into the computer’s memory. Once you have selected the desired cells, the values can be copied into the computer’s memory at one time and then pasted into the spreadsheet. You will quickly see that the process of copying and pasting data from multiple cells helps save time and effort.

Selecting Data

Selecting is the process used to tell the computer what data on your spreadsheet is to be used. (Selecting information is sometimes called highlighting.) When text is selected, it may be cut, copied, bolded, italicized, and more. To select data, position your mouse arrow at the first cell you desire to select. Click and HOLD DOWN the left mouse button. While holding down the mouse button, move your mouse arrow to the last cell containing data you want to select. As the mouse moves over the cells, the background color will change. After reaching the last cell of the desired data, release the mouse button. The selected cells will remain highlighted until you click your mouse on another cell in the spreadsheet. The computer is now ready for you to work with this selected data.

When you select data, the computer is only concerned with the data between where you began holding down the left mouse button and the exact point at which you released the left mouse button. Think of these positions as two dots. Anything between these dots will be selected. How you move from one location to another (left to right, or right to left) doesn’t matter. The computer only focuses on the area between those two dots.

When you select more than one cell, you are selecting a “range” of cells. Now, try to highlight cells B4 thru B14 using the mouse. To begin the selection process, position the mouse pointer over cell B4. Click and hold down your left mouse button. While holding down the mouse button, move the mouse pointer over the desired cells ending on cell B14. The first cell will remain white, but all of the other cells will change color (become highlighted). Release the mouse button when the mouse arrow is positioned over cell B14. Cells B4 through B14 are selected.

Selecting these cells will enable you to copy the cell data into the computer’s temporary memory. Once the data is copied, you can paste it into cells D4 through D14. Copying and pasting the data into these cells will quickly enable you to fill in the values for the month of March on the example budget.

WARNING: Don’t release the left mouse button until all of the desired cells have been selected. Releasing the button will stop the selection process before you have included all the desired cell data, and you will have to start the selection process over again.

NOTE: The only thing more important than knowing how to select cell data is knowing how to un-select the cells. If you accidentally select an area, click your mouse once anywhere in the spreadsheet. Clicking in an unselected area will remove all the highlighting on the spreadsheet.

Selecting Your Data: Step by Step Instructions
  • Position the mouse at the first cell you desire to select.
  • Click and hold down the left mouse button.
  • Move the mouse to the last cell you desire to select.
  • Release the mouse button.
    • The thick black outline surrounding the cells indicates the cells have been selected.
Copying and Pasting Data from Multiple Cells

In the last section you successfully highlighted cells B4 through B14. Now use the mouse to copy the highlighted data located in cells B4 through B14. When the cells have been selected, position the mouse pointer over the COPY option (located in Clipboard section of the Home ribbon) and click the left mouse button one time. The menu will close and the selected cell data will be copied into the computer’s memory. Take a moment to look at the selected cells. The cell border is moving, indicating that you have successfully copied the data contained within the cells. The computer is now awaiting instructions to specify where the copied data should be placed.

Move your mouse pointer over cell D4 and click your left mouse button. The bold, black selection box will appear around the cell D4 indicating the cell has been selected. Once the cell has been selected, position the mouse pointer over the PASTE option (located in Clipboard section of the Home ribbon) and click the left mouse button one time. Notice the data from cells B4 through B14 has been copied into cells D4 through D14.

Copying and Pasting Data from Multiple Cells: Step by Step Instructions
  • Select the cells containing the data you want to copy.
  • Click the COPY option located in Clipboard section of the Home ribbon.
  • Select the cell where you want the copied data to be placed.
  • Click the PASTE option located in Clipboard section of the Home ribbon
Copying and Pasting Data from Multiple Cells: Try It Out

You still need to enter the data into the cells for the month of April in the example budget. Since the data contained in cells B4 thru B14 is still in the computer’s memory, move the mouse pointer over to cell E4 and click the left mouse button. The bold, black selection box will appear around cell E4 indicating the cell has been selected. Once the cell has been selected, position the mouse pointer over the Paste option and click the left mouse button one time. Notice the data from cells B4 through B14 has been copied into cells E4 through E14.

The data in the example spreadsheet is perfect, with one exception. Row 14 (Entertainment) should not repeat the same data each month. It increases monthly, as often happens with monthly expenses. You previously entered 20 in cell B14 and 40 in cell C14. Cell D14 should contain the number 60 and cell E14 should contain the number 80. Utilizing what you learned in Chapter 2, use your mouse to select the cell in which you desire to enter or edit data. Once the cell is selected, type in the correct value. Type 60 in cell D14; then type 80 in cell E14.

Review: Entering Data into a Cell: Step by Step Instructions
  • Select the cell where you desire to type. The selected cell will then have the bold black border.
  • Type the data into the cell.
  • Repeat steps 1 and 2 to add additional data to the spreadsheet

Formulas and Calculations

Basic Calculations

The true power of the Microsoft Excel becomes apparent when you perform calculations on data entered into a spreadsheet. Excel can perform basic calculations like addition and subtraction or very complicated calculations such as payment schedules and financial ratios. The level of complexity depends on what information you need to obtain from the data.

Before you can begin to create a formula, you must first decide which cell will display the answer. Don’t be intimidated with the term formula – it is simply a set of instructions, usually directing the computer to calculate data. It is not uncommon for Excel enthusiasts to use the terms formula and calculation (or calc) interchangeably. Using the mouse, position the mouse pointer over the desired cell and click the left mouse button. A thick black boarder will appear around the cell indicating the cell has been selected. After the cell has been selected, you can begin to enter the formula.

Begin the new formula by typing either an equal = sign or a plus + sign. Either of these symbols at the beginning of the line indicates that you are about to create a formula. The equal = sign and plus + sign are located on the top line of your keyboard (just to the left of your backspace key). You may also use the plus + key located on your number pad at the far right of your keyboard. Once you have typed in the equal = sign or a plus + sign, you can enter or select the values you want to use in the calculation.

NOTE: Although it does not matter whether you use an equal = or plus + sign to start your formula, Excel will automatically change the first symbol to an equal = sign when you leave the cell.

NOTE: The plus + sign is called a mathematical operator. It instructs the computer to add the numbers. Other mathematical operators are the minus – sign (subtract), the asterisk * (multiply), and the slash / (divide).

Creating a Formula by Manually Typing in Values

The easiest way to learn how to create a formula in Excel is to walk through an example. Using the sample budget, you will calculate the total income for January. Move your mouse pointer to cell B7 and click the left mouse button to select the cell. You will calculate the answer by typing in the income values (numbers) manually. To tell the computer you are going to create a formula, type in an equal = sign. After the equal sign, type in the values for the calculation manually. Between each number, type a plus sign to indicate you want to add the numbers together. The example calculation for the month of January is =2500+850+50+0. After you have typed in all the values, press the Enter key on the keyboard to tell the computer to perform the calculation. The total 3400 will appear in cell B7.

To view the equation for the cell, click the mouse on the cell B7. Look at the Formula Bar near the top of the Excel window and to the right of the Name Box. The Formula Bar will display the entire mathematical equation you typed in the cell.

Manually Typing a Formula: Step by Step Instructions
  • Click on the cell in which you want to place a formula.
  • Type an “equal sign” (=) to indicate that you are creating a formula.
  • Type in the formula manually (example: =300+50+200+800).
  • Press the Enter key on the keyboard. The computer will calculate the answer.
Viewing a Formula: Step by Step Instructions
  • Click on the cell displaying the total/answer. (This is the same cell in which the formula was initially typed.)
  • Look at Formula Bar to view the equation.
Manually Typing a Formula: Try It Out

Congratulations you have created your first formula and completed your first calculation in Excel. The total income for January is 3400. Manually typing in the values used in a mathematical equation is very easy, but can lead to future problems on your spreadsheet. The problem arises when one of the values used in the formula changes. Consider the example budget, what if Salary 1 changed to 3500 instead of 2500. The total income calculated for January would be incorrect unless you remembered to manually correct the formula.

Test this out. Go back and select cell B3 with your mouse. Change the number 2500 to 3500. Note that the total income displayed in cell B7 did not change. The total for January still shows 3400 even though the correct answer is now 4400. To obtain the correct total, you must retype the formula to include the correct value for Salary 1. The mathematical formula in cell B7 must be changed to =3500+850+50+0 to calculate the correct total income for January. To avoid the problems associated with manually entering values (numbers) into calculations, Excel allows you to use cell references, rather than values when creating formulas. If you changed the value in cell B3 to 3500, please change cell B3 back to 2500 before continuing.

NOTE: When you type values (numbers) into a formula, the answer will not change automatically when you change one of the numbers in the cells you are totaling.

Creating a Formula by Manually Inputting Cell References

When creating a formula, the best practice is to reference the cells containing the values, rather than to type in the actual numbers. Remember each cell on a spreadsheet has a name. The cell name is the combination of the letter of the column and the number of the row where the cell is located. The cell’s name can be used to reference any cell on the spreadsheet. If you use cell references in a formula, you can change the values in the cells, and the system will automatically update the answer. This concept might seem confusing, so it’s best to look at an example.

To calculate the total income for the month of February, you will add the income values 2500, 850, 55, and 0. These values are located on the spreadsheet in cells C3, C4, C5, and C6 respectively. If you were going to calculate the total by typing in the values, the formula would be =2500+850+55+0. The answer for the calculation is 3405. If, instead, you use the cell reference method, you would type in the cell names instead of the specific values. The formula using cell references would be =C3+C4+C5+C6. The answer for the calculation is still 3405. The answers are identical. However, if you have to go back and change the value for Salary 1 located in cell C3 from 2500 to 3500, your calculation will automatically update to 4405 because you used cell references when you created the formula.

Using the sample budget, calculate the total income for February. Move your mouse pointer to cell C7 and click the left mouse button to select the cell. You are going to calculate the answer by manually typing the income cell references. To tell the computer you are creating a formula, type in an equal = sign. After the equal sign, type the names of the cells for the calculation. Between each cell name, type a plus sign to indicate you want to add the numbers together. The example formula for February is =C3+C4+C5+C6. Press the Enter key on the keyboard when done to tell the computer to perform the calculation. The total 3405 will appear in cell C7. Now go back to cell C3 and change the number to 3500. Note that the total automatically changes to 4405 as soon as you change the number in cell C3. This is your first encounter with the extraordinary power of Excel.

Creating a Formula by Selecting Cells

A slight variation to the previous method of creating a formula is to use the mouse to select each cell you want included in your calculation. You can try this method to calculate the total income for the month of March.

Move your mouse pointer to cell D7, and click the left mouse button to select the cell. You are going to select the cells used in the formula. To tell the computer you are going to create a formula, type an equal = sign. Instead of typing in the values or cell references, move the mouse to cell D3, the first cell you want to add, and press your left mouse button. The cell reference will now appear in the formula. After each cell selection, type a plus + sign to indicate you want to add the numbers together. Position the mouse over cell D4, the next cell you want to add to the calculation, and click the left mouse button. The cell reference has been added to the formula. Type a plus + sign. Continue to add cells D5 and D6 to the formula. Remember to type the plus sign between the cell references. After you have chosen the last cell, press the Enter key on the keyboard. (Make sure you end the formula with a cell reference and NOT a plus sign. If you end with a plus sign, the computer will tell you that you made an error and will not perform the calculation.) Once you have completed the formula and hit the Enter key, your total, 3500, will appear in cell D7.

Selecting the cells using the mouse is typically easier and faster than typing the cell references manually, but you can use whichever process is easier for you. The results are the same.

Creating a Formula by Selecting Cells: Step by Step Instructions
  • Click on the cell in which you want to place a formula.
  • Type in an “equal sign” (=) to indicate that you are entering a formula.
  • Select a cell with your mouse.
  • Type in a mathematical operator (+,-,*, /).
  • Repeat steps 3 and 4 to add the remainder of the cells for the calculation.
  • Press the Enter key on the keyboard. The computer will calculate the answer.
Practice Creating Formulas by Selecting Cells

It is important to become comfortable creating formulas using the data on the spreadsheet. Before moving on to the next topic, use what you have learned to create new formulas to finish the example spreadsheet. First, you will total the expenses for each month and then calculate the net income for each month. Since selecting cells using the mouse is the most common way to create a formula, you should practice using this technique.

To begin, position the mouse over cell B15 and click the left mouse button. Cell B15 is now selected to display the answer to the calculation totaling January’s expenses. To tell the computer you are going to create a formula, type an equal = sign. Move the mouse to cell B10, the first cell you want to add, and press your left mouse button. The cell reference will appear in the formula. Between each additional cell, type a plus + sign to indicate you want to add the cells together. Position the mouse over cell B11, the next cell you want add to the equation and click the left mouse button. The cell reference will be added to the formula. Continue to add cells B12, B13, and B14 to the formula. Remember to type the plus + sign between each cell reference. After you have chosen the last cell, press the enter key on the keyboard. Your total, 3500, will appear in cell B15.

Repeat the steps to calculate the total expenses for February, March, and April. February’s total expenses should be placed in cell C15, March’s total should appear in D15, and April’s total should be placed in cell E15.

You have successfully completed totaling the expenses for each month on the example spreadsheet. With the revenue and expenses totaled, the last formula you need to add to the sample spreadsheet will calculate the Net Income for each month. To correctly calculate the Net Income, you have to subtract your total monthly expenses from your total monthly revenue. For example, for the month of January you will subtract the expense total in cell B15 from the revenue total displayed in cell B7.

Position the mouse over cell B16 and click the left mouse button. Tell the computer you are going to create a formula by typing an equal = sign. Using the mouse, select cell B7 which contains the total revenue for January. The cell reference will appear in the formula. Since you are going to subtract the total expenses, type a minus (-) sign to indicate you want to subtract a number. Position the mouse over cell B15 and click the left mouse button. Press the Enter key. A Net Income of 1980 will appear in cell B16.

Copying and Pasting Formulas

The budget is going to utilize the same formula to generate the net income for each month as was used to calculate January’s net income. Instead of retyping the formula in cells C16, D16, and E16, you can use the Copy and Paste option. The copy and paste option will allow you to select a cell containing a formula, copy the formula into the computer’s memory, and then paste the copied formula into another cell on the spreadsheet. Since you used cell references in the formula, the computer will automatically adjust the cell references to the column/row they are related to when the formula is pasted into the new cells.

In the example, you are going to use the mouse to select and copy the formula in cell B16. Move the mouse pointer over cell B16 and press the left mouse button. Once the cell has been selected, position the mouse pointer over the COPY option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time. The selected cell’s formula will be copied into the computer’s memory. Take a moment to look at the selected cell. The cell border is moving, indicating that you have successfully copied the data contained in the cell.

Move your mouse pointer over cell C16 and click your left mouse button. The bold, black selection box will appear around cell C16 indicating the cell has been selected. Once the cell has been selected, position the mouse pointer over the PASTE option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time. Notice a value appears in cell C16. The formula used in cell B16 has been copied to cell C16 and the net income for February has been calculated. Remember the net income for February will not be the same as the net income for January because the income and expenses for February are different from January’s numbers.

To verify that the formula was pasted into the cell, look at the Formula Bar located at the top of the screen. You will see the equation used to calculate the net income for February. Notice the cell references automatically updated to use the cells C7 and C15 in the calculation for February. Copying and Pasting calculations is another example of the power of Excel.

You still need to calculate the net income for the months of March and April.  Since the calculation used in cell B16 is still in the computer’s memory, move the mouse pointer to cell D16 and click the left mouse button.  The bold, black selection box will appear around the cell D16 indicating the cell has been selected.  Once the cell has been selected, position the mouse pointer over the Paste option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time.  Notice the calculation from cell B16 has been copied into cell D16.

Repeat the steps to paste the net income calculation in cell E16, and you will have completed the calculations on the example budget.

NOTE: Mathematical operations used in calculations:

  • Addition: Use the plus + symbol
  • Subtraction: Use the minus – symbol
  • Multiplication: Use the asterisk * symbol
  • Division: Use the slash / symbol

NOTE: Each time you change one of the values in a calculation which utilizes cell references, the answer will update automatically.

Review: Copying and Pasting: Step by Step Instructions
  • Select the cell containing the formula you want to copy.
  • Click the COPY option located in the Clipboard section of the Home ribbon.
  • Select the cell where you want the copied formula to be placed.
  • Click the PASTE option located in the Clipboard section of the Home ribbon.