Excel is a spreadsheet program that allows you to store, organize, and analyze information. In this lesson, you will learn your way around the Excel 2010 environment, including the new Backstage view, which replaces the Microsoft Button menu from Excel 2007.
The Ribbon
The Ribbon contains multiple tabs, each with several groups of commands. You can add your own tabs that contain your favorite commands.
Backstage View Backstage view gives you various options for saving, opening a file, printing, or sharing your document. It is similar to the Office Button menu from Excel 2007 or the File menu from earlier versions of Excel. However, instead of just a menu, it is a full-page view which makes it easier to work with. To Get to Backstage View: On the Ribbon, click the File tab.
Click the buttons in the interactive below to learn about the different things you can do in Backstage view.
When you open a new, blank workbook, the cells are set to a default size.You do have the ability to modify cells, and to insert and delete columns, rows, and cells, as needed. In this lesson, you will learn how to change row height and column width; insert and delete rows and columns; wrap text in a cell; and merge cells.
Click and drag the column to the right to increase the column width or to the left to decrease the column width.
The Column Width dialog box appears. Enter a specific measurement.
Select AutoFit Column Width from the format drop-down menu and Excel will automatically adjust each selected column so that all the text will fit.
Click and drag the row downward to increase the row height or upward decrease the row height.
Click and drag the row downward to increase the row height or upward decrease the row height.
The Row Height dialog box appears. Enter a specific measurement.
Select AutoFit Row Height from the format drop-down menu and Excel will automatically adjust each selected row so that all the text will fit.
Click the Insert command on the Home tab.
The new row appears in your worksheet.
When inserting new rows, columns, or cells, you will see the Insert Options button by the inserted cells. This button allows you to choose how Excel formats them. By default, Excel formats inserted rows with the same formatting as the cells in the row above them. To access more options, hover your mouse over the Insert Options button and click on the drop-down arrow that appears.
Click the Insert command on the Home tab.
The new column appears in your worksheet.
If a cell contains more text than can be displayed, you can choose to wrap the text within the cell or merge the cell with empty, adjoining cells. Wrap text to make it display on multiple lines of the cell. Merge cells to combine adjoining cells into one larger cell.
Select the Wrap Text command on the Home tab.
The text in the selected cells will be wrapped in your worksheet.
Select the Merge & Center command on the Home tab.
The selected cells will be merged and the text will be centered.
If you change your mind, re-click the Merge & Center command to unmerge the cells.
Click the drop-down arrow next to the Merge & Center command on the Home tab. The merge drop-down menu appears.
The text is now highlighted by a black box. Type the name of your worksheet.
Click anywhere outside of the tab. The worksheet is renamed.
A formula is an equation that performs a calculation. Like a calculator, Excel can execute formulas that add, subtract, multiply, and divide.
One of the most useful features of Excel is its ability to calculate using a cell address to represent the value in a cell. This is called using a cell reference.
In order to maximize the capabilities of Excel, it is important to understand how to create simple formulas and use cell references.
Excel uses standard operators for equations, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
The key thing to remember when writing formulas for Excel is that all formulas must begin with an equal sign (=). This is because the cell contains, or is equal to, the formula and its value.
When a formula contains a cell address, it is called a cell reference. Creating a formula with cell references is useful because you can update data in your worksheet without having to rewrite the values in the formula.
With over 17 billion cells in a single worksheet, Excel 2010 gives you the ability to work with an enormous amount of data. Arranging your data alphabetically, from smallest to largest, or other criteria, can help you find the information you're looking for more quickly.
Sorting is a common task that allows you to change or customize the order of your spreadsheet data. For example, you could organize an office birthday list by employee, birthdate, or department, making it easier to find what you're looking for. Custom sorting takes it a step further, giving you the ability to sort multiple levels (such as department first, then birthdate, to group birthdates by department), and more.
The data in the spreadsheet will be organized alphabetically.
Sorting options can also be found on the Home tab, condensed into the Sort & Filter command.
You can use a Custom List to identify your own sorting order, such as days of the week, or, in this example, t-shirt sizes from smallest to largest (Small, Medium, Large, and X-Large).
Identify the column you want to Sort by by clicking the drop-down arrow in the Column field. In this example, we will choose T-Shirt Size.
Click OK to close the Sort dialog box and sort your data.
The spreadsheet will be sorted in order of Small, Medium, Large, and X-Large.
In the Order field, click the drop-down arrow to choose a color, then decide whether you want it ordered On Top or On Bottom.
Click OK. The data is now sorted by attribute rather than text.
Excel is a spreadsheet application that can help you calculate and analyze numerical information for household budgets, company finances, inventory, and more. To do this, you need to understand complex formulas.
In this lesson, you will learn how to write complex formulas in Excel following the order of operations. You will also learn about relative and absolute cell references and how to copy and fill formulas containing cell references.
Excel calculates formulas based on the following order of operations:
A mnemonic that can help you remember the order is Please Excuse My Dear Aunt Sally.
The following example demonstrates how to use the order of operations to calculate a formula:
In this example, we will review how Excel will calculate a complex formula using the order of operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.
Based on this complex formula, the result will show that 57.58% of the total Pete Lily seeds sold were white. You can see from this example, that it is important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately.
In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.
Excel will interpret cell references as either relative or absolute. By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).
Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.
Relative references can save you time when you are repeating the same kind of calculation across multiple rows or columns.
In the following example, we are creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.) For convenience, we can copy the formula in B4 into the rest of row 4 and Excel will calculate the value of the bills for those months using relative references.
Enter the formula to calculate the value you want (for example, add B2+B3).
Press Enter. The formula will be calculated.
Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.) and the values are calculated.
There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula.
An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.
In the below example, we want to calculate the sales tax for a list of products with varying prices. We will use an absolute reference for the sales tax ($B$1) because we do not want it to change as we are copying the formula down the column of varying prices.
Type the dollar sign ($) and enter the row number of the same cell you are making an absolute reference to (for example, 1).
Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.) and your values are calculated.
Figuring out formulas for calculations you want to make in Excel can be tedious and complicated. Fortunately, Excel has an entire library of functions or predefined formulas that you can take advantage of. You may be familiar with common functions like sum, average, product or count, but there are hundreds of functions in Excel, even for things like formatting text, referencing cells, calculating financial rates, analyzing statistics, and more.
A function is a predefined formula that performs calculations using specific values in a particular order. One of the key benefits of functions is that they can save you time since you do not have to write the formula yourself. Excel has hundreds of different functions to assist with your calculations.
In order to use these functions correctly, you need to understand the different parts of a function and how to create arguments in functions to calculate values and cell references.
The order in which you insert a function is important. Each function has a specific order, called syntax, which must be followed for the function to work correctly. The basic syntax to create a formula with a function is to insert an equal sign (=), a function name (SUM, for example, is the function name for addition), and an argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.
Arguments must be enclosed in parentheses. Individual values or cell references inside the parentheses are separated by either colons or commas.
Enter the cells for the argument inside the parenthesis.
Press Enter and the result will appear.
The AutoSum command allows you to automatically return the results for a range of cells for common functions like SUM and AVERAGE.
A formula will appear in the selected cell E24. If logically placed, AutoSum will select your cells for you. Otherwise, you will need to click on the cells to choose the argument you desire.
The AutoSum command can also be accessed from the Formulas tab.
There are hundreds of functions in Excel, but only some will be useful for the kind of data you are working with. There is no need to learn every single function, but you may want to explore some of the different kinds to get ideas about which ones might be helpful to you as you create new spreadsheets.
A great place to explore functions is in the Function Library on the Formulas tab. Here you may search and select Excel functions based on categories such as Financial, Logical, Text, Date & Time, and more. Click the buttons in the interactive below to learn more.
To Insert a Function from the Function Library:
The Function Arguments dialog box will appear. Insert the cursor in the first field and then enter or select the cell(s) you desire (G6, for example).
Insert the cursor in the next field and then enter or select the cell(s) you desire (H6, for example).
Click OK and the result will appear. Our results show that it took 5 days to receive the order.
The Insert Function command is convenient because it allows you to search for a function by typing a description of what you are looking for or by selecting a category to peruse. The Insert Function command can also be used to easily enter or select more than one argument for a function.
In this example, we want to find a function that will count the total number of supplies listed in the Office Supply Order Log. The basic COUNT function only counts cells with numbers; we want to count the cells in the Office Supply column, which uses text. Therefore, we will need to find a formula that counts cells with text.
Review the results to find the function you desire. We will use COUNTA. Then click OK.
The Function Arguments dialog box will appear. Insert the cursor in the first field and then enter or select the cell(s) you desire (A6:A14, for example).
Insert the cursor in the next field and then enter or select the cell(s) you desire (A19:A23, for example). (You may continue to add additional arguments if needed.)
Click OK and the result will appear. Our results show that 14 Total Supplies were ordered from our log.
Once you have entered information into a spreadsheet, you may want to format it. Formatting your spreadsheet can not only improve the look and feel, but also make it easier to use. In a previous lesson, we discussed many manual formatting options such as bold and italics. In this lesson, you will learn how to format as a table, to take advantage of the tools and predefined table styles available in Excel 2010.
Just like regular formatting, tables can help to organize your content and make it easier for you locate the information you need. To use tables effectively, you'll need to know how to format information as a table, modify tables, and apply table styles.
Click the Format as Table command in the Styles group on the Home tab.
A list of predefined table styles will appear. Click a table style to select it.
Click OK. The data will be formatted as a table in the style that you chose.
Tables include filtering by default. You can filter your data at any time using the drop-down arrows in the header. To learn more, review our Filtering Data lesson.
To convert a table back into "normal" cells, click the Convert to Range command in the Tools group. The filters and the Design tab will then disappear, but the cells will retain their data and formatting.
Modifying Tables
To Add Rows or Columns:
Click OK. The new rows and/or columns will be added to your table.
To Change the Table Style Options:
When using an Excel table, you can turn various options on or off to change its appearance. There are six options: Header Row, Total Row, Banded Rows, First Column, Last Column, and Banded Columns.
Depending on the Table Style you're using, certain Table Style Options may have a different effect. You may need to experiment to get the exact look you want.
Imagine you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One of these tools is called conditional formatting. With conditional formatting, you can apply formatting to one or more cells based on the value of the cell. You can highlight interesting or unusual cell values, and visualize the data using formatting such as colors, icons, and data bars.
Conditional formatting applies one or more rules to any cells that you want. An example of a rule might be "If the value is greater than 5,000, color the cell yellow." By applying this rule to the cells in a worksheet, you'll be able to see at a glance which cells are over 5,000. There are also rules that can mark the top 10 items, all cells that are below the average, cells that are within a certain date range, and many more.
The formatting will be applied to the selected cells.
If you want, you can apply more than one rule to your cells.
Excel has a number of presets that you can use to quickly apply conditional formatting to your cells. They are grouped into three categories:
Color Scales change the color of each cell based on its value. Each color scale uses a two or three color gradient. For example, in the Green - Yellow - Red color scale, the highest values are green, average values are yellow, and the lowest values are red.
Icon Sets add a specific icon to each cell based on its value.
The conditional formatting will be applied to the selected cells.
You can edit or delete individual rules by clicking on the Conditional Formatting command and selecting Manage Rules. This is especially useful if you have applied multiple rules to the cells.
To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.
Note: Date is in US Format. Months first, Days second. This type of format depends on your windows regional settings.
Dates are stored as numbers in Excel and count the number of days since January 0, 1900. Times are handled internally as numbers between 0 and 1. To clearly see this, change the number format of cell A1, B1 and C1 to General.
Apparently, 41083 days after January 0, 1900 is the same as June 23, 2012. 6:00 is represented as 0.25 (quarter through the day).
To get the year of a date, use the YEAR function.
Note: use the MONTH and DAY function to get the month and day of a date.
1. To add a number of days to a date, use the following simple formula.
2. To add a number of years, months and/or days, use the DATE function.
ote: the DATE function accepts three arguments: year, month and day. Excel knows that 6 + 2 = 8 = August has 31 days and rolls over to the next month (23 August + 9 days = 1 September).
To get the current date and time, use the NOW function.
Note: use the TODAY function to get the current date only. Use NOW()-TODAY() to get the current time only (and apply a Time format).
To return the hour, use the HOUR function.
Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.
LEFT | Extracts one or more characters from the left side of a text string |
RIGHT | Extracts one or more characters from the right side of a text string |
MID | Extracts characters from the middle of a text string; you specify which character position to start from and how many characters to include |
CONCATENATE | Assembles two or more text strings into one |
REPLACE | Replaces part of a text string with other text |
LOWER | Converts a text string to all lowercase |
UPPER | Converts a text string to all uppercase |
PROPER | Converts a text string to proper case |
LEN | Returns a text string’s length (number of characters) |
Left
To extract the leftmost characters from a string, use the LEFT function.
To extract the rightmost characters from a string, use the RIGHT function.
To extract a substring, starting in the middle of a string, use the MID function.
To get the length of a string, use the LEN function.
The CONCATENATE Function joins several text strings into one text string. You can also use the Ampersand (&) calculation operator instead of the CONCATENATE Function.
The CONCATENATE Function has one required argument and up to 255 arguments, all separated by commas. The arguments can be text strings, numbers, or single-cell references.
In my spreadsheet example, cell B2 has the Month and Day (Jun 30), while cell C2 has the Year (2010). The following formula will combine these two in cell D2:
=CONCATENATE(B2,”, “,C2)
Notice that “, ” is the second argument, which is a comma followed by the space character, all in quotes. The result is: Jun 30, 2010. You get a better sense of the arguments by looking at the Function Arguments dialog box.
While the CONCATENATE Function is all nice and proper and well documented, the Ampersand (&) operator is much easier to use in practice.
The same formula in cell E2 using the Ampersand operator:
=B2 & “, ” & C2
The Ampersand operator separates the different arguments to be combined in a text string. Much easier to use than typing out CONCATENATE, don’t you think?
In any event, both the CONCATENATE Function and Ampersand Operator return a Text String. Excel will recognize this text string as a Date, but there are problems associated with Dates entered as Text. You can’t change it’s numeric formatting, to name just one.
Statistical functions
These functions calculate averages, minima, maxima, and so on. The required syntax is:
The count function counts cells containing numbers, and the counta counts alphanumeric.
MODE | Mode of a set of numbers |
MEDIAN | Median of a set of numbers |
To calculate the average of a range of cells, use the AVERAGE function.
To find the median (or middle number), use the MEDIAN function.
To find the most frequently occurring value, use the MODE function.
To find the minimum value, use the MIN function.
To find the maximum value, use the MAX function.
To find the third largest number, use the following LARGE function.
To find the second smallest number, use the following SMALL function.
The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.
1. Select cell C2 and enter the following function.
The IF function returns Correct because the value in cell A1 is higher than 10.
The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.
1. Select cell D2 and enter the following formula.
The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.
1. Select cell E2 and enter the following formula.
The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.
General note: the AND and OR function can check up to 255 conditions.
The IF function can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another If function to make a further test. For example, look at the formula below.
1a. If cell A1 equals 1, the function returns Bad.
1b. If cell A1 equals 2, the function returns Good.
1c. If cell A1 equals 3, the function returns Excellent.
1d. If cell A1 equals another value, the function returns No Valid Score.
Here's another example.
2a. If cell A1 is less or equal to 10, the function returns 350.
2b. If cell A1 is greater than 10 and less or equal to 20, the function returns 700.
2c. If cell A1 is greater than 20 and less or equal to 30, the function returns 1400.
2d. If cell A1 is greater than 30, the function returns 2000.
Note: to slightly change the boundaries, you might want to use "<" instead of "<=" in your own formula.
It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.
The syntax for the nesting the IF function is:
IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
This would be equivalent to the following IF THEN ELSE statement:
IF condition1 THEN value_if_true1 THEN value_if_true2 ELSE value_if_false2 END IF
This syntax example demonstrates how to nest two IF functions. You can nest up to 7 IF functions.
condition is the value that you want to test.
value_if_true is the value that is returned if condition evaluates to TRUE.
value_if_false is the value that is return if condition evaluates to FALSE.
SUM | Calculates the sum of a group of values |
AVERAGE | Calculates the mean of a group of values |
COUNT | Counts the number of cells in a range that contains numbers |
INT | Removes the decimal portion of a number, leaving just the integer portion |
ROUND | Rounds a number to a specified number of decimal places or digit positions |
IF | Tests for a true or false condition and then returns one value or another |
NOW | Returns the system date and time |
TODAY | Returns the system date, without the time |
SUMIF | Calculates a sum from a group of values, but just of values that are included because a condition is met |
COUNTIF | Counts the number of cells in a range that match a criteria |
Count
To count the number of cells that contain numbers, use the COUNT function.
To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.
To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.
To sum a range of cells, use the SUM function.
To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).
To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).
To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).
1. The COUNTBLANK function counts the number of blank cells.
The COUNTA function counts the number of nonblank cells. COUNTA stands for count all.
Basically, VLOOKUP lets you search for specific information in your spreadsheet. For example, if you have a list of products with prices, you could search for the price of a specific item.
We’re going to use VLOOKUP to find the price of the Photo frame. You can probably already see that the price is $9.99, but that’s because this is a simple example. Once you learn how to use VLOOKUP, you’ll be able to use it with larger, more complex spreadsheets, and that’s when it will become truly useful.
We’ll add our formula to cell E2, but you can add it to any blank cell. As with any formula, you’ll start with an equal sign (=). Then, type the formula name. Our arguments will need to be in parentheses, so type an open parenthesis. So far, it should look like this:
=VLOOKUP(
Now, we’ll add our arguments. The arguments will tell VLOOKUP what to search for and where to search.
The first argument is the name of the item you are searching for, which in this case is Photo frame. Since the argument is text, we’ll need to put it in double quotes:
=VLOOKUP(“Photo frame”
The second argument is the cell range that contains the data. In this example, our data is in A2:B16. As with any function, you’ll need to use a comma to separate each argument:
=VLOOKUP(“Photo frame”, A2:B16
Note: It’s important to know that VLOOKUP will always search the first column in this range. In this example, it will search column A for “Photo frame”. In some cases, you may need to move the columns around so that the first column contains the correct data.
The third argument is the column index number. It’s simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the price of the item, and the prices are contained in the second column. That means our third argument will be 2:
=VLOOKUP(“Photo frame”, A2:B16, 2
The fourth argument tells VLOOKUP whether to look for approximate matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Since we’re only looking for exact matches, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses:
=VLOOKUP(“Photo frame”, A2:B16, 2, FALSE)
And that’s it! When you press enter, it should give you the answer, which is 9.99.
Let’s take a look at how this formula works. It first searches vertically down the first column (VLOOKUP is short for “vertical lookup”). When it finds “Photo frame”, it moves to the second column to find the price.
If we want to find the price of a different item, we can just change the first argument:
=VLOOKUP(“T-shirt”, A2:B16, 2, FALSE)
or:
=VLOOKUP(“Gift basket”, A2:B16, 2, FALSE)
OK, are you ready for a slightly more complicated example? Let’s say we have a third column that has the category for each item. This time, instead of finding the price, we’ll find the category.
To find the category, we’ll need to change the second and third arguments in our formula. First, we’ll change the range to A2:C16 so that it includes the third column. Next, we’ll change the column index number to 3, since our categories are in the third column:
=VLOOKUP(“Gift basket”, A2:C16, 3, FALSE)
When you press Enter, you’ll see that the Gift basket is in the Gifts category.
If you’d like more practice, see if you can find the following:
• The price of the coffee mug
• The category of the landscape painting
• The price of the serving bowl
• The category of the scarf
Now you know the basics of using VLOOKUP. Although advanced users sometimes use VLOOKUP in different ways, you can do a lot with the techniques that we’ve covered. For example, if you have a contact list, you could search for someone’s name to find their phone number. If your contact list has columns for the email address or company name, you could search for those by simply changing the second and third arguments, as we did in our example. The possibilities are endless!
A chart is a tool you can use in Excel to communicate your data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends a lot easier. In this lesson, you will learn how to insert charts and modify them so that they communicate information effectively.
Charts
Excel workbooks can contain a lot of data, and that data can often be difficult to interpret. For example, where are the highest and lowest values? Are the numbers increasing or decreasing?
The answers to questions like these can become much clearer when the data is represented as a chart. Excel has many different types of charts, so you can choose one that most effectively represents the data.
Types of Charts
Click the arrows in the slideshow below to view examples of some of the types of charts that are available in Excel.
Identifying the Parts of a Chart
Click the buttons in the interactive below to learn about the different parts of a chart.
To Create a Chart:
Click the Insert tab.
Select the desired chart type from the drop-down menu (Clustered Column, for example).
The chart will appear in the worksheet.
Chart Tools
Once you insert a chart, a set of Chart Tools, arranged into three tabs, will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.
To Change the Chart Type:
Select the desired chart type and click OK.
To Switch Row and Column Data:
Sometimes when you create a chart, the data may not be grouped the way you want it to be. In the clustered column chart below, the Book Sales statistics are grouped by Fiction/Non-Fiction, with a column for each year. However, you can also switch the row and column data so that the chart will group the statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart contains the same data; it's just organized differently.
The chart will then readjust.
Select the desired layout.
The chart will update to reflect the new layout.
Some layouts include chart titles, axes, or legend labels. To change them, just place the insertion point in the text and begin typing.
Select the desired style.
The chart will update to reflect the new style.
Select the desired location for the chart (i.e., choose an existing worksheet, or select New Sheet and name it).
Click OK. The chart will appear in the new location.