Microsoft Excel 1

Introduction to Editing Spreadsheets

Introduction

About this Class

Welcome to Excel 1! Excel is a program that allows users to create and manipulate spreadsheets, create tables, perform calculations, and chart data. This class will cover the basics and allow you to begin to explore the powerful features of Excel.

Excel is a program that is apart of the Microsoft Office Suite. Every few years, the company releases new versions of the Microsoft Office Suite. This manual is designed from Excel 2013 for the PC. If you are working on a different version of Microsoft Office, you are welcome to try the exercises, but some features may be located in a different location or may not be available.

Prerequisites and Requirements

Navigating the Excel Environment

The Excel Interface from Excel 2010 to Excel 2013 for PC has changed aesthetically, but it has a similar layout. If you are familiar with Excel 2010, there should not be problems for you to adapt to the Excel 2013 layout.

Excel Interface

To begin, open Excel on your computer. If you are having any problems finding Excel, you can use the search bar under the start menu.

The following terms describe the basic features of the Excel environment that we will use today:

Getting Familiar with Excel

  1. Click in a few different cells to see the active cell change. Notice how the Name Box (left of the Formula Bar) changes depending on the cell selected.

  2. Press the up, down, left, and right arrow keys to move one cell in that direction at a time.

  3. Use the scroll bars (the actual bars of the arrows) to move to a different area of the worksheet that is off-screen. Also, you can use the scroll and click-wheel of the mouse.

  4. Use the view magnification to try changing the zoom level of the worksheet.

  5. Press Ctrl + Home to return to cell A1

When we refer to cells in Excel, they are given a letter and a number (like how A1 is referenced above). The letter (A) describes the column A and the number 1 refers to row 1.

Similarly, when we refer to a set of cells, we use the colon (:) to describe them. For example, if we want to refer to cells A1 to A26, we would describe it as A1:A26. If we want to refer to a box of cells, we would describe them as [upper left cell]:[lower right cell]. So, if we looked at cells A1, A2, A3 B1, B2, and B3; we would refer to them as A1:B3.

Basic excel interface

Workbooks vs. Worksheets

A single Excel file is called a "workbook" and by default contains three "worksheets". Each worksheet contains a unique workspace where data can be stored and manipulated. Data can be linked between worksheets if desired. All the worksheets within a workbook can be viewed near the bottom of the workbook in an area called the "worksheet tabs".


Inserting and Deleting a Worksheet

  1. Click on the small insert worksheet icon.

  2. Double-click on the worksheet tab and type in the desired name to rename the worksheet.

  3. Right-click on the worksheet tab and select Delete to permanently remove that worksheet.

Entering and Editing Data

In its most basic form, Excel is an interface that allows you to store data in an organized manner. The word "data" is used here in a broad sense; it can apply to any combination of numeric and textual information. In order to use any of Excel's abilities, you must understand how to insert data in an easy to access and logical manner.

Inserting Data into a Cell

For any tool in Excel, there are most likely multiple ways to access it. This may include buttons on the ribbon, keyboard shortcuts, and quick-access buttons within the interface. There is often not a right or wrong way to use Excel's tools and functions and instead it is left up to user preference. You are encouraged to try each exercise in this manual using multiple methods to determine the way that works best for you.

  1. Open the Excel1-class-file.xlsx file


There are three different ways to enter or edit data in a cell. Each will be briefly discussed and you can choose the method you like best;

After the desired data is typed in, the Enter key can be pressed to accept the entry into the cell. Alternatively, you may click the small check-mark button to the left of the formula bar to accept the data as well. If you change your mind about inserting this data, you can instead press the escape key or click the X button next the formula bar to revert the cell to its previous state. Now that these methods have been explored, let’s give our Study Abroad Budget spreadsheet a title.

Adding a Title

  1. In A1, enter the text: "Your Name"'s Study Abroad Budget. Notice that this text extends well beyond the extent of cell A1.

  2. Select cells A1 through H1 by clicking and dragging the mouse from A1 to H1.

  3. In the Home Tab navigate to the Alignment section and click on the Merge & Center button. This button will take all of the highlighted cells and merge them into one cell and center the input inside the cell.

  4. Merge and Center
  5. To change the color, font type, border, and other aesthetic qualities of this cell, navigate to the Font Panel of the Home Tab.

  6. Feel free to change the text font, fill color, font color, and the alignment of the text using the options in the Font Panel. If you have used a word processing system before, it should have a similar layout.

  7. Merged cells

Using Formulas and Functions

Excel's main features are its ability to work with mathematical and logical operations to do calculations for you. This allows the user to not only organize data in a spreadsheet but also to perform calculations, in order to create new and more useful data. Excel is a powerful utility that is able to update calculations and formulas as your data changes. We will soon see that this happens quickly and easily without a noticeable delay

Basic Formulas

Excel's calculation abilities are split into two subsets: formulas and functions. A formula is a basic mathematical statement that consist of addition, subtraction, multiplication, and division. These are things you could do on a basic calculator but Excel has the functionality built-in.


The basic operations used for Excel's formulas are:

The equals sign is a key part of every formula or function. By putting an "=" as the first character in a cell, Excel enters its calculation mode and treats anything that appears after it as an expression that it can evaluate. It will become second nature for you to automatically put the "=" as the first thing whenever you want Excel to do a calculation for you.

Viewing and Entering a Formula

  1. To view a formula, select a cell that has a formula with it. Since we have not entered any formulas into our excel sheet yet, there are no formulas to view.

  2. Click the New Worksheet button at the bottom of the screen.

  3. Making a new worsheet
  4. Enter a numerical value for cells A1, A2, A3, A4, B1, B2, B3, and B4.

  5. Select cell C1.

  6. Type =A1+B1 into the cell and press enter.

  7. You have just successfully entered your first Excel Formula! You should get the sum of A1 and B1 in cell C1. Now let’s try a different way of entering a formula.

  8. Select cell C2

  9. Type =

  10. Click on cell A2

  11. Type *

  12. Select cell B2

  13. A2 should now appear in our formula and appear blue while the same thing happens to B2 but in green. The blue color is meant to help you visually see what the formula is referencing. As you add more cell references in the same formula, each will be color coded differently.

    Example of using formulas
  14. Press Enter to see the result. It should be the product of cells A2 and B2.

  15. Try the subtraction and division formulas for cells C3 and C4.

Basic Functions

Functions can serve the same purpose as formulas but are usually shorter and easier to enter. Functions are built-in calculations that Excel comes packaged with that allow you to execute math & trig functions, logical functions, text manipulation, financial functions, and much more. Here, we will explore the most commonly used and basic functions.

Viewing and Entering Functions

There are three common ways to enter functions that will be reviewed here: typing the complete function in, using the drop-down menu suggestions, and using the insert function library. We're going to enter a SUM function, which adds the integer values of the cells highlighted. Note that this function only works if there are integer values within the cell.

  1. Select the Budget worksheet in the Worksheet tab

  2. Select cell B21

  3. Type =SUM(into that cell

  4. Highlight cell B3 to B15

  5. Press Enter

Using sum in example
  1. Navigate to cell K7 and type =SUM

  2. A drop down menu will appear with all the functions in Excel's library that start with the letter "S". When a function is selected, a brief description of what the function does is provided.

  3. Find the SUM function

  4. Double click it to select it

  5. Select cells K4:K6

  6. Press Enter

  7. There should be a small green triangle in the top left corner of cell K7. We will address this later in the manual.

  8. Using dropdown for functions

    Now we are going to enter a function using the Insert Function button

    Using insert function button
  9. Click the Insert Function button.

  10. A window will appear and allows you to search for any given function in Excel's complete function library. After a function is selected, it brings up a function arguments window that prompts for the necessary information.

  11. Type "maximum" in the search for a function field at the top of the window

  12. Click Go to search

  13. Select MAX from the search results

  14. Click OK

  15. The MAX function reports the largest number from the select range

  16. Delete the content in the Number1 field

  17. Select cells B12:H15 in the workspace (move the Insert Function dialog box to the side if you can’t see the cells you need to select)

  18. Click OK

  19. Now try any of the different ways to enter a function that you just learned to fill out cells K17 (minimum) and K18 (average), using the minimum function for cells B3:H5 and the average function for cells B8:H9, respectively. (K17 should be $9.54 and K18 should be $16.81)

  20. The minimum function reports the smallest number from the selected range and the average function reports the sum of the selected range divided by the number of cells in that range.

Entering Functions Using Auto-fill

AutoFill is a convenient feature that allows you to repeat a common formula multiple times without having to type it in again and again.

  1. Select cell B21

  2. Place your mouse over the dark green square in the bottom right corner of the active cell.

  3. Once you see the black cross icon (changed from the white cross icon), click and drag the selection across to cell H21

  4. Release the mouse

Using AutoFill

Notice how the function has been copied into the selected cells. This function also works for the formulas we mentioned previously. What we just did was a shortcut for a copy-and-paste operation. If you inspect the entries we filled into cells H5:H9 you will notice that we didn't copy the exact formula from cell H4. Instead, each different formula contains a reference to two cells in the same row. This is because the cells in the formulas are relatively addressed to the cells in column H, not by their absolute location. We will discuss the issues of relative versus absolute referencing later.

Absolute Cell Functions

Up to this point, we have been using relative cell addresses to reference cells. This allowed us to use AutoFill in order to create the same formula for different data points. Now we will focus on absolute cell references. Absolute references are good when you need to reference the same cell from multiple locations, something that is not possible when using relative cell addressing. Let's try an example using relative cell addressing and see why we need to learn absolute.

Calculating Amount in Bank (per month)

  1. In cell C23, type =(B23+C22-C21) + (B23+C22-C21) *K20. From here, we can use the AutoFill to fill in the cells horizontally.

  2. Click on cell D23. You will notice that cell K20 has become cell K21.

  3. Click on cell C23

  4. Type $'s around the K.

  5. Your function should look like this.

= (B23+C22-C21) + (B23+C22-C21) *$K$20

The $'s are used to declare the absolute cell functions. A $ behind the alphabet letter signifies you wish to keep the references within the K column, and a $ behind the numerical value signifies that you wish to keep the reference within the 20 column.

Understanding Functions

It is easy to copy and paste functions, but being able to write them on your own will help you in the long run. The first step towards being able to write your own function is being able to understand them and what they are telling Excel to do. Let’s start with a basic IF function:

=IF(logical_test_value,value_if_true,value_if_false)

We can break down this function into several parts:

Calculating if you are going to England

  1. Click on cell K11, for 'Saved', and enter in the value of H23

  2. Now, to calculate 'Need', in cell K12 type = K10-K11

Now we will see how the IF function in K13 works in our budget sheet.

=IF(K11>=K10,"YES!","NO!")

Note that not all functions follow this exact format. This is just used as a common example that you may encounter as you use Excel.

Basic Charts

Occasionally, you may also want to look at your data in the form of a graph or a chart. Excel offers several different types of graphs and charts for you to interpret your data.

Creating Charts (Including Styles and Moving)

Let's create a graph using the data from the Budget worksheet.

  1. Select the data from B23 horizontally to H23.

  2. Select the Quick Analysis button on the bottom right corner of your data.

  3. Select Line Chart.

  4. You can also create charts by using the Insert tab in the ribbon.

    Quick analysis for making charts

    A line chart will appear on-screen. Notice that the Y axis is the data that we selected earlier. The X axis, as we will name in a bit, are the months of the year. Now, let’s edit the chart title.

  5. Select “Chart Title” and rename it "Money in the Bank".

  6. Right click anywhere on the graph and select "Select Data". A new window should appear.

  7. On the right side of the window, press the Edit button below Horizontal (Category) Axis Labels.

  8. Highlight cells B2 to H2 horizontally. This is the range that you would like to name your axis with. Since these are bank statements, it would make sense to use the names of the months.

  9. Hold Control as you select to highlight cells B3:B5, B8:B9, and B12:B15.

  10. Select the Insert Tab and click on the pie/doughnut.

  11. Click 2D Pie

  12. Inserting charts
  13. Now let's try labelling the X axis properly. Double-click (or right-click) on the legend and click on the edit horizontal axis button.

  14. Hold control and select cells A3:A5, A8:A9, and A12:A15. (It is important to highlight the cells in the same order vertically as you chose the numeric values to ensure that each x-axis label corresponds to the correct value color-wise in the chart.)

  15. Pie chart

    Let’s say that we want to move this chart to a new worksheet for further analysis. We can do that as well.

  16. Select the June Spending Chart and navigate to the Design Tab.

  17. On the right side, locate the Move Chart button in the Location panel.

  18. Select Move Chart. You have two options to choose from. You can either place an object in a different worksheet (Object in:) or make a new worksheet altogether (New Sheet:).

  19. Click on New Sheet:

  20. Name it "June Spending".

Editing Source Data

This is especially helpful if you would like more than one line of data on a chart. For our exercises, we will walk through it, but we will not implement it.

  1. Select your chart by clicking on it.

  2. Click on the Design Tab underneath Chart Tools.

  3. Within the Design Tab, you can click on Select Data from the Data panel. A window should appear on-screen.

  4. On the left side, you have several options to select your data.

  5. Editing chart data
  6. Select Edit and then another window should pop up. From there, you can highlight your data.

Chart Aesthetics

Now that the charts are formatted the way we like them. Let's make them aesthetically appealing. This will be especially useful for charts that have a lot of data points or have a lot to present.

Editing the Chart Style and Layout

  1. Select the chart you want to edit (either chart will do).

  2. On the right-hand side, there should be three chart buttons.

  3. Click Chart Styles

  4. Choose a fantastic chart style and color scheme

Editing Axis

You will probably notice that a lot of the chart elements can be edited by simply double-clicking the point of interest and using the sidebar to fine tune your element.
  1. Select Add Chart Element in the Chart Layouts panel within the Design Tab.

  2. From the drop down menu, select Axis Title and choose Primary Horizontal.

  3. Repeat steps 1 and 2 to choose Primary Vertical.

  4. Double click the horizontal axis title and type "Time (in months)"

  5. Do the same for the vertical axis title and type "Money accumulated"

  6. To adjust the minimum and the maximum values of the axis, double-click the left axis. A panel should appear on the right.

  7. Select the Axis Options button.

  8. Select the drop down arrow to the left of Axis Options

  9. Change the minimum and maximum bounds to make the graph look nice. Changes in the minimum and maximum units will change the amount of space between each horizontal line.

Editing Gridlines

  1. Double click the gridlines on the Money in the Bank graph. If the right-hand panel is still on screen, it should have the title "Format Major Gridlines".

  2. Select the Gradient line bullet point.

  3. Change the Preset Gradients to something that looks nice to you.

  4. There are many other options to optimize your gridlines, but these options are some basic choices.

Editing the Legend

  1. Double click the legend on the June Spending pie chart and a right-hand panel should show on screen if it isn't already.

  2. Select the Legend Options button.

  3. Change the Legend Position by selecting the Right option. The legend should appear on the right side.

  4. Select the Fill & Line button.

  5. Change the Border to Solid Line.

  6. Change the color by selecting the drop down paint bucket icon.

Displaying Values on the Chart

  1. Select the June Spending pie chart.

  2. From the chart buttons, choose the Chart Elements button.

  3. Select Data Labels.

  4. Select the Data Labels on the chart.

  5. Select the Label Options in the right hand sidebar.

  6. Change the position of the Data Labels to Below.

Viewing, Sorting, & Filtering Data

With Excel capable of holding millions of pieces of data, it can become overwhelming to use its features without having knowledge of its viewing and sorting options. Viewing an overwhelming data set can become manageable and efficient by utilizing a few of Excel's simple features.

Freezing Panes

You will notice that as you scroll down the spreadsheet, you lose the column headings at the top of the worksheet (Store Name, Item Cost, etc.). These would be helpful references if you are viewing an item at the bottom of the list but unfortunately, since our list is so large, it isn't possible to view them. Freezing panes is a solution to this problem

  1. Click in cell A1

  2. Select the View Tab

  3. Select the Freeze Panes button from the Window panel

  4. Click Freeze Panes

This "freezes" everything above the active cell A3; so now, when we scroll down the worksheet, all of our column headings remain at the top of our screen. To unfreeze the panes, select the Freeze Panes button from the Window panel again and click Unfreeze Panes.

Filtering Data

Sorting is useful but can be a little inconvenient if you just need quick looks at data in a certain way. Filters can be applied to our data to make it easier to view specific categories of information. A filter shows only the information that we ask for and hides the other data. Let's explore a few Filter options with our class list.

  1. Select cells A1:G1.

  2. From the Data Tab, click the Filter button. You should now see small arrow in each column header. Clicking on these arrows will open a filter menu for that header.

  3. Filter dropdown
  4. Click on the drop-down arrow for "Semester"

  5. Uncheck Select All.

  6. Then select Fall

  7. Click OK.

  8. You should now only see all of the classes offered in the Fall semester. Note that all of your data is still present, but it is currently hidden.

  9. To see everything again, click Select All on the Category filter menu

  10. Click OK.

  11. A variety of filters can be applied to your data depending on your needs or desires. We can also filter our data based on specific criteria applied a column or in an alphabetical or numerical order. Take a few moments to explore some options or ask your instructor for more information.

  12. To remove all filters, click the Filter button in the Data Tab.

Sorting Data

Often times you will have a large amount of data and you will want to sort it in some logical way (alphabetically, numerically, etc.). We can sort our data in multiple levels by using the Sort feature.

  1. Select cells A1:G47 (for all class titles and descriptions)

  2. On the Data Tab, click the Sort button.

  3. Check the option "My data has headers" box in the "Sort" dialog box that appears. This allows us to user our header names as the sort options.

  4. Sort button
  5. Under the “Column: Sort by” menu choose Semester.

  6. Under the “Sort On” menu choose Values

  7. Under the “Order” menu choose A to Z.

  8. Click OK

  9. We have organized our data alphabetically by Department. We can add as many levels of sorting as desired.

  10. Try to add another level of sorting, such as by Credit from Largest to Smallest.

Saving and Printing

From the File tab you can save, print, and open workbooks. You can also access special options such as add-ins and customizing your ribbons.

Saving Workbooks

  1. To save your workbook, click the File Tab

  2. Click Save As to save your workbook as new, unique file

  3. Under the "Save as type", select "Excel Workbook".

  4. Click Save

Saving menu

Printing

Printing in Excel can often be a painstaking process without the proper precautions. We will be using the Classes worksheet for this exercise.

The first step in printing an Excel file is to preview how the page will look before it is sent to the printer. To accomplish this, we can use the Page Layout View.

  1. In the View Tab select Page Layout from workbook Views panel. Notice that, by default, our data will be separated when printed. To change the default let's switch to a different view.

  2. Select Page Break Preview from the workbook views panel.

  3. Click and hold on the vertical blue dotted lines.

  4. Click and Drag the line right until it meets the solid single blue line at the edge of the spreadsheet We are now left with one page containing all of our information. We can split it into two pages to make the data more readable. This is more preferable for pages that have longer lists of information.

  5. Click and Drag the bottom blue line down and then release

  6. Continue this process until you can see the dotted line and two pages are displayed.

  7. Now, resize the pages so that you have Fall classes on page 1 and Spring classes on page 2.

Page Setup

If we printed as is, the data on the second page would not have headers. This would lead to the same problem we had before we used 'freeze panes'; it would be difficult to know which header the column of data belongs to. We will instead configure our plot so that the first and second rows appear on every sheet.

  1. In Page Layout Tab, select Print Titles

  2. Under the Sheet Tab, click in the Rows to repeat at top field.

  3. In the spreadsheet, select Row 1

  4. Click OK

  5. Select the Page Layout view to see your changes Since this isn't ideal for our data set, return to the page break preview in the View tab and place everything back on one page. From there, we can still make the page look more professional by changing the page orientation, print scale and adding a footer.

  6. Select the Page Layout Tab

  7. In the Page Setup panel, click on the Orientation button and choose Landscape from the drop down menu.

  8. Enter a value of 85% in the Scale field in the Scale to Fit panel to change the print size.

  9. Scroll to the bottom of a page in Page layout view and click the Click to add footer

  10. In the Design tab, select Page Number from the Header and Footer Elements panel

  11. Select the empty space next to the right of the footer box and select the Current Date element

Page Setup ribbon

We finally have a good scale and orientation. Also, our headings appear on both pages and we can see our footer. The document can now be printed from the File tab where there are a few other settings that can be controlled as well

Closing Excel

  1. Select the File Tab (backstage view)

  2. Save your workbook

  3. Exit Excel by selecting the Exit button at the bottom of the left-side menu





For Excel support and more information visit support.office.com/en-us/excel. Thank you for attending Excel 1. We look forward to seeing you in Excel 2: Functions, Excel 2: Analysis, Excel 2: Data Visualization, and many other Software Training for Students classes in addition to our Ask a Trainer sessions. All of these services can be accessed at our website, wisc.edu/sts.