Use Excel
Excel is a powerful spreadsheet program made by Microsoft Office. You can create and format spreadsheets and workbooks (collections of spreadsheets), build models for analyzing data, write formulas, perform many calculations, and present professional charts. Cash flow statements, income statements, budgets, calendars, or profit and loss statements can all be easily created if you know how to use Excel.
Contents
Steps
Setting Up Your Spreadsheet
- Open the Excel program. There may be a shortcut on your desktop, or you may need to go to “Start” and then “Programs” to locate the Excel icon.
- Start a new workbook (an Excel file). Click “File” and “New.” Under “Available templates,” click “Blank Workbook,” then “Create.” A blank workbook will open.
- Save the workbook. Click the Office button (or File tab if you have an earlier version of Excel) and select “Save as.” Choose a location on your computer to save your file (such as the “my documents” folder), type the name of your workbook in the “File name” box, and be sure your file type is set to “Excel Workbook”.
- Familiarize yourself with the tabs on the ribbon at the top of your workbook. They are File, Home, Insert, Page Layout, Formulas, Data, Review and View.
- Familiarize yourself with the language needed to use Excel. Knowing the terminology of the technology is important for being able to use it easily and understand step-by-step guides.
- A row is a section that goes right to left across the screen (indicated by numbers along the left side of the screen).
- A column is a set of data that goes from the top to the bottom of the worksheet, and it is identified with a letter at the top of the sheet.
- A cell is any individual square of the worksheet that data may be placed in.
- Prepare your worksheet for data storage. Every Excel workbook has 3 worksheets by default. Sheet 1 opens by default and you can see this tab at the bottom of the window.
- Rename a sheet by right clicking on the Sheet 1 tab. Select “Rename” and type the new name for your sheet.
- If necessary, add sheets by clicking the button to the right of “Sheet 3” that shows a sheet of paper with a star in the corner.
- On the top row of your sheet, type a title in each cell to identify what will be placed in each column. For example, you might type Name, Date, and Amount. The rows underneath these titles are for your data.
- Save frequently. When you are entering data, you may want to save your work frequently by clicking the floppy disk symbol at the top left of your screen or clicking on the office button and selecting “Save.” Alternately, you can hold down the control “Ctrl” key on your keyboard while you type “S.”
Entering and Managing Data in Excel
- Begin entering data into the cells of your worksheet. You may want to start with one or two practice columns or rows before entering all of your data in the sheet.
- Click on a cell and type the data into the cell.
- To edit data after it has been entered into the cell, double-click the cell or edit the data in the edit bar on the top of the spreadsheet (just above the column letters).
- Get to know how cells are formatted. By default, the “General” format is used, but you can change the settings of each cell, row, or column.
- The formatting can be changed to a pre-set format such as a number, date, time, or currency by selecting the drop-down arrow next to “General” from the “Home” tab on the ribbon at the top.
- You can also change the font and style as well as the alignment of the numbers or text by using the “Font” and “Alignment” sections of the “Home” tab.
- Change the formatting of an entire column by selecting the letter at the top and then making the changes.
- Change the formatting of an entire row by selecting the number on the left side of the screen and then making the changes.
- Enter your data. Add all data to your spreadsheet.
- Press enter to move to the next cell below your current cell, press tab to move to the cell to the right of your current cell, or use one of your arrow keys to change cells.
- Remember to save your work!
- Sort your data. Select the data you wish to sort. You can select single columns or multiple columns and include text titles if you want.
- Be sure to select multiple columns if you wish to keep the data rows together. Sorting a single column will change the order of that column, but will leave the adjacent columns unsorted.
- Select the Data tab and click “Sort.” The sort dialog box will appear.
- Select the column you want to sort in the “Sort by” list. If you have entered titles in the top row, the titles of your columns will appear in the “Sort by” box.
- Select either “Values,” “Cell Color,” “Font Color,” or “Cell Icon.” If you have entered text, you probably want to select Sort On “Values.”
- Select the order that you want to apply to the sort operation. This can be either ascending or descending (A to Z or Z to A for text or largest to smallest or smallest to largest for numbers).
- Filter your data. Selecting the data you wish to filter by highlighting one or multiple columns.
- Select the Data tab, and click “Filter” (the funnel icon) in the Sort and Filter section.
- Arrows will appear at the top of each column. Click the arrow to see the list of options in the column header.
- Select the values you want to use and click “OK” to see the results. The rest of your data will be hidden so that you can view the filtered data.
- Restore the rest of your data by selecting the “Clear” option (the icon shows a funnel with a red X next to it) in the Sort & Filter section of the Data tab.
- Find specific text in your workbook. Click the “Find and Select” icon (binoculars) on the Home tab.
- Click “Find” and type the text you are looking for.
- Select “Find All,” and a list will appear with all of the instances of that text in your worksheet. (Note: to search the entire workbook, choose the “Options” button in the Find and Replace pop-up and change “Within” from “Sheet” to “Workbook,” then click “Find All.”)
- Print your worksheet by clicking “File” and “Print” or by holding down the control button on your keyboard while you type “P” (shortcut Ctrl + P).
- You can preview the file by clicking the Office button then hovering your mouse over “Print.” This should give you the option to select “Print Preview.” From there, you can select the Print icon near the top left of the screen.
- You can change the setup of the page and page size, margin and other options by accessing the “Page Setup” menu under the “Page Layout” tab. Select the small arrow at the corner of the “Page Setup” box to expand the menu.
- Scale the whole worksheet to fit on a single printed page by going to the “Page Layout” tab, then clicking the small arrow next to “Scale to Fit.” Under “Scaling” on the “Page” tab, select “Fit to” and change settings to “1 page wide by 1 tall.” Click OK.
- To print a portion of a worksheet, click the worksheet, and then select the range of data that you want to print by clicking, holding down the button on your mouse, and dragging it to cover the selection you want. Then click the Office button, select “Print” and select “Selection” under “Print what.” Click OK.
- Use the drop down box under “Printer” to view the printers currently installed on your computer and select the printer you want to use.
Performing Basic Calculations
- Use the sum function for basic column adding. Click in the empty cell below a column of numbers you want to add together.
- Click the “AutoSum” symbol to the right on the home tab ribbon that looks like a sideways “M.”
- Click a second time and the previously empty cell will now display the total of the column of numbers.
- Use an equals sign (=) to begin every formula. If you are going to type in a formula by hand (instead of using the Sum button), you must begin the formula with an equals sign. Place the equals sign first in the cell where you want the answer to appear.
- Add a whole column of numbers. This can be done by using the AutoSum (see Step 1 of this section), but you can also write the formula yourself.
- Type = in an empty cell that you wish for your sum to appear in, then type SUM.
- Type an open parenthesis “(“, then the top (first) column letter and row number of the range you wish to add, then type a colon.
- Type the bottom (last) column letter and row number of the range you wish to add, then close the parenthesis to enclose the letters and numbers parentheses. For example, your formula may say =SUM(B5:B9). Press enter. Your column of numbers will be added up.
- Add numbers using your own formula. If you want to add numbers that are not all lined up in one column, you can create your own formula for adding.
- Click in the empty cell that you would like for the sum to appear in.
- Type = then either click on the first cell you would like to add or type the column letter and row number corresponding to your first number (e.g., B2).
- Type + and then click on the next cell you want to add or type the column letter and row number corresponding to your second number. For example =B2+C5. Repeat until you have selected all of the numbers you wish to add together. Press enter and see the answer.
- Subtract using the same method as adding, but use a minus sign instead of a plus sign. In an empty cell, type = then the desired first column letter and row number. Type – then the second number. For example =D5-C5. Press enter and your calculation will be displayed.
Creating Visuals
- Create a chart by selecting the data you would like to chart.
- Find the Insert tab and the Charts group.
- Click the type of chart and a chart sub-type you would like to use.
- Create a table by selecting a range of cells you want to make into a table. These cells can either all contain your data or some of them can be empty.
- Find the Home tab and the Styles group. On the Home tab, in the Styles group, click “Format as Table”
- Select the table style from the many options that appear.
- Make your cells shaded or with borders. Select the range of cells you want to apply this to.
- Go to the Home tab and then the Font group.
- Click “Font” and “Borders” tab.
- In the dialog box, select the required style.
- To shade cells, go to the Home tab and the Font group
- Click “Font” and the “Fill” tab and select the shading color you like.
Tips
- Be aware that the above is only the basics of Excel. Formulas and many other functions are available in this powerful program.
- This guide is based on Excel 2007, and there may be slight variations between versions. Your settings and operating system may cause your screen to look slightly different as well.
- If you need more space on the computer screen, minimize the ribbon by right clicking on the ribbon and click “Minimize the Ribbon.”
