Find and Replace Text Occasionally, you'll have a word or number you need to find somewhere in your spreadsheet, but you're not sure where it is. Excel’s find and replace commands scan a worksheet for labels and values with just a few clicks of the mouse. Finding Text Click the Find & Select button on the Home tab. Select Find. Shortcut: Press Ctrl + F. Type the text you want to find in the Find what box. Click Find Next. The first instance is selected. Click Find Next again to move on to other occurrences. Tip: Click Find All to find the cell location of all instances of at once. Click Close when you’re finished.
Edit a Formula After a formula is complete, you can still easily update what the formula is calculating. Select the cell with the formula you want to edit. Click in the Formula bar and edit the formula as necessary. Tip: You can also double-click a cell to switch to edit mode, then edit the formula right in the cell. Click the Enter button when you’re finished. Shortcut: Press Enter. The formula is updated to reflect the changes. Use a Function Function Categories Financial Functions that are used to calculate interest, payments, loans, etc. Date & Time Functions to calculate date and times values. Math & Trig Math and trigonometry functions, such as SUM, COS, and TAN. Statistical Functions that calculate averages, standard deviations, etc. Lookup & Reference Functions that lookup or reference values. Database Functions that lookup or calculate values in a list or database. Text Functions that can be used with text or labels. Logical Conditional functions (IF…THEN). Information Functions that return information about values and the worksheet itself. Engineering Functions used in engineering calculations. Cube Functions that extract data from OLAP cubes. In Excel, a function is a preset formula. There are several hundred functions available. Some are simple, such as the Sum function. Others are much more complex and contain several different arguments. Arguments are the data used by the function to make a calculation. They appear within the parentheses in the function.
Lookup Functions When you have spreadsheets with tons of data, finding just what you need can be daunting. Using Lookup functions can be a huge time-saver. For example, VLOOKUP searches vertically down the left-most column of a cell range to find a value specified. When it finds the value, it then looks across the row and returns the value in the column specified. VLOOKUP Click the cell where you want to add the VLOOKUP formula. Click Insert Function. Select Lookup & Reference from the list of categories. Select the VLOOKUP function. Click OK. Fill in the function arguments. Lookup_value: The number you want to search for in the left-most column. Table_array: The cell range you want to search in. Col_index_num: The column number for the value you want to look up. For this formula, each column has a number assigned. Column A=1, B=2, etc. Click OK. The formula is added and it returns the data for the lookup value. You can update the lookup value as needed to find the necessary information. Tip: The HLOOKUP function is similar to VLOOKUP, except it searches horizontally across the top row of cells until it finds the value specified. When it finds the specified value it looks down the column to find the specified value.
View and Manage Worksheets Once you start filling a workbook with data, you’ll notice that it can be tricky to organize and view it all at once. Luckily, Excel gives you several options for viewing and working with data and windows. You can split windows, insert new worksheets, copy worksheets, work with multiple workbooks at once, hide data, and hide worksheets and gridlines. In this chapter, we’ll look at ways to make viewing and working with data easier. Using Exercise Files This chapter suggests exercises to practice the topic of each lesson. There are two ways you may follow along with the exercise files: Open the exercise file for a lesson, perform the lesson exercise, and close the exercise file. Open the exercise file for a lesson, perform the lesson exercise, and keep the file open to perform the remaining lesson exercises for the chapter. The exercises are written so that you may “build upon them”, meaning the exercises in a chapter can be performed in succession from the first lesson to the last.