slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Excel 2010 PowerPoint Presentation
Download Presentation
Excel 2010

Loading in 2 Seconds...

play fullscreen
1 / 69

Excel 2010 - PowerPoint PPT Presentation


  • 181 Views
  • Uploaded on

Excel 2010 . What is an Excel program ? . Excel is a spreadsheet program that allows you to store, organize, and analyze information . The Excel Interface. Ribbon. The Excel Interface. Quick Access Toolbar Worksheets:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Excel 2010' - luana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
what is an excel program
What is an Excel program ?
  • Excel is a spreadsheet program that allows you to store, organize, and analyze information.
the excel interface1
The Excel Interface
  • Quick Access Toolbar
  • Worksheets:
    • Excel files are called workbooks. Each workbook holds one or more worksheets (also known as "spreadsheets").
    • Three worksheets appear by default when you open an Excel workbook. You can rename, add and delete worksheets.
the excel interface2
The Excel Interface
  • Horizontal Scroll Bar
  • Zoom Control
  • Page View
    • Normal view is selected by default, and shows you an unlimited number of cells and columns. It is highlighted in the image below.
    • Page Layout view divides your spreadsheet into pages.
    • Page Break view lets you see an overview of your spreadsheet, which is helpful when you are adding page breaks.
the excel interface3
The Excel Interface
  • Row
    • A row is a group of cells that runs from the left of the page to the right. In Excel, rows are identified by numbers. Row 15 is selected in the image below.
  • Column
      • A column is a group of cells that runs from the top of the page to the bottom. In Excel, columns are identified by letters. Column L is selected in the image below.
the excel interface4
The Excel Interface
  • Name Box
    • The Name box tells you the location or the "name" of a selected cell.
  • Formula Bar
    • In the formula bar, you can enter or edit data, a formula, or a function that will appear in a specific cell.
    • Note how the data appears in both the formula bar and in selected cell.
cell basics
Cell Basics
  • Cells are the basic building blocks of a worksheet. Cells can contain a variety of content such as text, formatting attributes, formulas, and functions.
    • cell address : based on which column and row it intersects
cell basics1
Cell Basics
  • To Select a Cell
  • To Select Multiple Cells
  • Cell Content:
    • TextCells can contain letters, numbers, and dates.
    • Formatting attributesCells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, dates can be formatted as MM/DD/YYYY or Month/D/YYYY.
    • CommentsCells can contain comments from multiple reviewers.
    • Formulas and FunctionsCells can contain formulas and functions that calculate cell values. For example, SUM(cell 1, cell 2...) is a formula that can add the values in multiple cells.
cell basics2
Cell Basics
  • To Insert Content
  • To Delete Content Within Cells:
cell basics3
Cell Basics
  • To Delete Cells
  • To Drag and Drop Cells
  • To Copy and Paste Cell Content
  • To Cut and Paste Cell Content
  • To Use the Fill Handle to Fill Cells
cell basics4
Cell Basics
  • To Access More Paste Options
  • To Access Formatting Commands by Right-Clicking
modifying columns rows and cells
Modifying Columns, Rows, and Cells
  • To Modify Column Width
  • To Set Column Width with a Specific Measurement:
    • Select the columns you want to modify.
    • Click the Format command on the Home tab. The format drop-down menu appears.
    • Select Column Width.

Note : 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.

modifying columns rows and cells1
Modifying Columns, Rows, and Cells
  • To Modify the Row Height
  • To Set Row Height with a Specific Measurement:
modifying columns rows and cells2
Modifying Columns, Rows, and Cells
  • To Insert Rows
    • Select the rowbelow where you want the new row to appear.
    • Click the Insert command on the Hometab
  • To Insert Columns
    • Select the column to the right of where you want the new column to appear.
    • Click the Insert command on the Hometab
  • When inserting new rows, columns, or cells, you will see the Insert Options button by the inserted cells.
modifying columns rows and cells3
Modifying Columns, Rows, and Cells
  • To Delete Rows
    • Select the rows you want to delete
    • Click the Delete command on the Hometab
  • Do the same thing to Delete Columns
modifying columns rows and cells4
Modifying Columns, Rows, and Cells
  • Wrapping Text and Merging Cells:
    • Select the cells with text you want to wrap.
    • Select the Wrap Text command on the Hometab
    • If you change your mind, re-click the Wrap Text command to unwrap the text.
modifying columns rows and cells5
Modifying Columns, Rows, and Cells
  • To Merge Cells Using the Merge & Center Command:
    • Select the cells you want to merge together.
    • Select the Merge & Center command on the Home tab.
modifying columns rows and cells6
Modifying Columns, Rows, and Cells
  • To Access More Merge Options:
    • Merge & Center: Merges selected cells into one cell and centers the text.
    • Merge Across: Merges each row of selected cells into larger cells. This command is useful if you are merging content across multiple rows of cells and do not want to create one large cell.
    • Merge Cells: Merges selected cells into one cell.
    • Unmerge Cells: Unmerges the selected cells.
formatting cells
Formatting Cells
  • Formatting Text
    • Font, Color, Size, Alignment
  • To Add a Border
  • Formatting Numbers and Dates
    • Select the cells you want to modify.
    • Click the drop-down arrow next to the Number Format command on the Home tab.
formatting cells1
Formatting Cells
  • Formatting Numbers and Date:
    • General is the default format for any cell. When you enter a number into the cell, Excel will guess the number format that is most appropriate.
    • Number formats numbers with decimal place "4.00".
    • Currency formats numbers as currency with a currency symbol "$4.00".
    • Accounting formats numbers as monetary values like the Currency format, but it also aligns currency symbols and decimal places within columns”$ 12.5”.
    • Short Date formats numbers as M/D/YYYY "8/8/2010".
    • Long Date formats numbers as Weekday, Month DD, YYYY "Monday, August 01, 2010".
    • Time formats numbers as HH/MM/SS and notes AM or PM"10:25:00 AM".
    • Percent formats numbers with decimal places and the percent sign. "75.00%".
    • Fraction formats numbers as fractions separated by the forward slash "1/4“
    • Scientific formats numbers in scientific notation. "140000“ "1.40E+05".
    • Text formats numbers as text, meaning that what you enter into the cell will appear exactly as you wrote it.
saving
Saving
  • Save AS
  • Save
  • AutoRecover
  • Save As PowerPoint 97 - 2003 Presentation and PDF
    • To save as PDF, Excel defaults to saving the active worksheet only. If you have multiple worksheets and want to save all of them in the same PDF file, click on Options. The Options dialog box will appear. Select Entire workbook from the Options dialog box and click OK.
creating simple formulas
Creating Simple Formulas
  • To Create a Simple Formula in Excel:
    • Select the cell where the answer will appear
    • Type the equal sign (=)
    • Type in the formula you want Excel to calculate
    • Press Enter. The formula will be calculated and the value will be displayed in the cell
creating simple formulas1
Creating Simple Formulas
  • To Create a Formula Using Cell References:
    • Select the cell where the answer will appear
    • Type the equal sign (=).
    • Type the cell address that contains the first number in the equation
    • Type the operator you need for your formula.
    • Type the cell address that contains the second number in the equation
    • Press Enter. The formula will be calculated and the value will be displayed in the cell.

Note: If you change a value in either B1 or B2, the total will automatically recalculate

creating simple formulas2
Creating Simple Formulas
  • To Create a Formula using the Point and Click Method:
    • Select the cell where the answer will appear.
    • Type the equal sign (=).
    • Click on the first cell to be included in the formula .
    • Type the operator you need for your formula.
    • Click on the next cell in the formula .
    • Press Enter. The formula will be calculated and the value will be displayed in the cell.
creating simple formulas3
Creating Simple Formulas
  • To Edit a Formula:
    • Click on the cell you want to edit.
    • Insert the cursor in the formula bar and edit the formula as desired. You can also double-click the cell to view and edit the formula directly from the cell.
    • When finished, press Enter or select the Enter command √
    • The new value will be displayed in the cell.
worksheet basics
Worksheet Basics
  • To Rename Worksheets
  • To Insert New Worksheets
  • To Delete Worksheets
worksheet basics1
Worksheet Basics
  • To Copy a Worksheet
worksheet basics2
Worksheet Basics
  • To Move a Worksheet
    • Click on the worksheet you want to move. The mouse will change to show a small worksheet icon .
    • Drag the worksheet icon until a small black arrow appears where you want the worksheet to be moved.
  • To Color-Code Worksheet Tabs
worksheet basics3
Worksheet Basics
  • Grouping and Ungrouping Worksheets
    • Select the first worksheet you want in the group
    • Press and hold the Ctrl key on your keyboard
    • Select the next worksheet you want in the group. Continue to select worksheets until all of the worksheets you want to group are selected.
    • Release the Ctrl key. The worksheets are now grouped. The worksheet tabs appear white for the grouped worksheets.
  • To Ungroup All Worksheets:
    • Right-click one of the worksheets. The worksheet menu appears.
    • Select Ungroup. The worksheets will be ungrouped.
worksheet basics4
Worksheet Basics
  • To Freeze Rows
    • Select the row below the rows that you want frozen. For example, if you want rows 1 & 2 to always appear at the top of the worksheet even as you scroll, then select row 3
    • Click the Viewtab
    • Click the Freeze Panes command. A drop-down menu appears.
    • Select Freeze Panes
  • To Freeze Columns
    • Select the column to the right of the columns you want frozen. For example, if you want columns A & B to always appear to the left of the worksheet even as you scroll, then select column C , then do the same thing above.
  • To Unfreeze Panes
    • Select Unfreeze Panes. From Freeze Panes
printing
Printing
  • To Print Active Sheets:
    • Select the worksheets you want to print. To print multiple worksheets, click on the first worksheet, hold down the Ctrl key, then click on the other worksheets you want to select.
    • Select Print Active Sheets from the print range drop-down menu.
  • To Print the Entire Workbook
  • To Print a Selection, or Set the Print Area:
    • Select the cells that you want to print.
    • Select Print Selection from the print range drop-down menu.
  • To Fit a Worksheet on One Page
printing1
Printing
  • To Use Print Titles:
    • Click the Page Layout tab.
    • Select the Print Titles command.
    • The Page Setup dialog box appears. Click the icon at the end of the Rows to repeat at top field.
    • Your mouse becomes the small selection arrow . Click on the rows you want to appear on each printed page. The Rows to repeat at top dialog box will record your selection.
    • Click the icon at the end of the Rows to repeat at top field.
    • Repeat for Columns to repeat at left, if necessary.
    • Click OK. You can go to Print Preview to see how each page will look when printed.
creating complex formulas
Creating Complex Formulas
  • Order of Operations
  • Excel calculates formulas based on the following order of operations:
    • Operations enclosed in parentheses
    • Exponential calculations (to the power of)
    • Multiplication and division, whichever comes first
    • Addition and subtraction, whichever comes first
creating complex formulas1
Creating Complex Formulas
  • Relative References
    • 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.
working with basic functions
Working with Basic Functions
  • The Parts of a Function
  • Working with Arguments
    • Colons create a reference

to a range of cells.

For example, =AVERAGE(E19:E23) would calculate the average of the cell range E19 through E23.

    • Commas separate individual values, cell references, and cell ranges in the parentheses. If there is more than one argument, you must separate each argument by a comma. For example, =COUNT(C6:C14,C19:C23,C28) will count all the cells in the three arguments that are included in parentheses.
working with basic functions1
Working with Basic Functions
  • Using AutoSum to select Common Functions:
    • Select the cell where the answer will appear (E24, for example).
    • Click on the Home tab.
    • In the Editing group, click on the AutoSum drop-down arrow and select the function you desire.
    • A formula will appear in the selected cell. 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.
    • Press Enter and the result will appear.
function library
Function Library
  • The Insert Function command allows you to easily search for a command by entering a description of what you are looking for.
  • The AutoSum command allows you to automatically return results for common functions.
  • Use the Recently Used command
  • The Financial category contains functions for financial calculations like determining a payment (PMT) or interest rate for a loan (RATE).
  • Functions in the Logical category check arguments for a value or condition. For example, if an order is over $50 add $4.99
function library1
Function Library
  • The Text category contains functions that work with the text in arguments using tasks like converting text to lowercase (LOWER)
  • The Date & Time category contains functions for working with dates and time and will return results like the current date and time (NOW) or the seconds (SECOND).
  • The Lookup & Reference category contains functions that will return results for finding and referencing.
  • The Math & Trig category includes functions for numerical arguments. For example, find the value of Pi (PI) .
  • More Functions contains additional functions under categories for Statistical, Engineering, Cube, Information, and Compatibility.
function library2
Function Library
  • To Insert a Function from the Function Library:
sorting data
Sorting Data
  • To Sort in Alphabetical Order:
    • Select a cell in the column you want to sort by.
    • Select the Data tab, and locate the Sort and Filter group.
    • Click the ascending command  to Sort A to Z, or the descending command  to Sort Z to A.
  • To Sort in Numerical Order
  • To Sort by Date or Time
sorting data1
Sorting Data
  • Custom Sorting:
sorting data2
Sorting Data
  • Sorting Multiple Levels:
outlining data
Outlining Data
  • Outlinesgive you the ability to group data that you may want to show or hide from view, and create a quick summary using the Subtotal command. Because outlines rely on grouping data that is related, you must sort before you can outline.
outlining data1
Outlining Data
  • Outline Data Using Subtotal
    • Sort according to the data you want to outline. Outlines rely on grouping data that is related
    • Select the Data tab, and locate the Outlinegroup
    • Click the Subtotal command to open the Subtotal dialog box.
    • the At each change in field, select the column you want to use to outline your worksheet
    • In the Use function field, choose from the list of functions that are available for subtotaling
    • Select the column you want the subtotal to appear in.
    • Click OK.
outlining data2
Outlining Data
  • Show or Hide a Group
    • Click the minus sign, also known as the Hide Detail symbol, to collapse the group
    • Click the plus sign, also known as the Show Detail symbol, to expand the group again
  • View Groups by Level
    • Click the highest level to view and expand all of your groups.
    • Click the next levelto hide the detail of the previous level.
    • Click the lowest level to display the lowest level of detail.
outlining data3
Outlining Data
  • Ungroup Data
    • Select the rows or columns that you want to ungroup.
    • From the Data tab, click the Ungroupcommand.
    • To ungroup all the groups in your outline, open the drop-down menu under the Ungroup command, and choose Clear Outline
    • Ungroup and Clear Outline will not remove subtotaling from your worksheet. Summary or subtotal data will stay in place and continue to function until you remove it
outlining data4
Outlining Data
  • Ungroup Data and Remove Subtotaling
    • From the Data tab, click the Subtotal command to open the Subtotal dialog box.
    • Click Remove All.
    • All data will be ungrouped, and subtotals will be removed.
  • Create and Control Your Own Group
    • Select the range of cells that you want to group.
    • From the Data tab, click the Group command.
    • Excel will group the selected columns or rows.
    • Click the minus sign, also known as the Hide Detail symbol, to hide the group
    • The group will be hidden from view.
filtering data
Filtering Data
  • Filter Data
    • Begin with a worksheet that identifies each column using a header row.
    • Select the Data tab, and locate the Sort & Filtergroup
    • Click the Filtercommand
    • Click the drop-down arrow for the column you would like to filter. In this example, we will filter the Type column to view only certain types of equipment.
    • Uncheck the boxes next to the data you don't want to view. (You can uncheck the box next to Select All to quickly uncheck all.)
    • Check the boxes next to the data you do want to view.
    • Click OK. All other data will be filtered, or temporarily hidden.
filtering data1
Filtering Data
  • Clear a Filter
    • Click the drop-down arrow in the column from which you want to clear the filter.
    • Choose Clear Filter From...
    • The filter will be cleared from the column
  • To instantly clear all filters from your worksheet, click the Filter command on the Data tab.
filtering data2
Filtering Data
  • Filter Using Search
  • Use Advanced Text Filters
  • Use Advanced Date Filters
  • Use Advanced Number Filters
formatting tables
Formatting Tables
  • Format Information as a Table
    • Select the cells you want to format as a table.
    • 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.
    • A dialog box will appear, confirming the range of cells you have selected for your table. The cells will appear selected in the spreadsheet, and the range will appear in the dialog box.
    • If necessary, change the range by selecting a new range of cells directly on your spreadsheet.
    • If your table has headers, check the box next to My table has headers
    • Click OK. The data will be formatted as a table in the style that you chose.
formatting tables1
Formatting Tables
  • Modifying Tables
    • Add Rows or Columns
      • Select any cell in your table. The Design tab will appear on the Ribbon
      • From the Design tab, click the Resize Table command.
      • Directly on your spreadsheet, select the new range of cells that you want your table to cover. You must select your original table cells as well
      • Click OK. The new rows and/or columns will be added to your table
    • Change the Table Style
    • Change the Table Style Options
working with charts
Working with Charts
  • Create a Chart
    • Select the cells that you want to chart, including the column titles and the row labels. These cells will be the source data for the chart.
    • Click the Inserttab
    • In the Charts group, select the desired chart category.
    • Select the desired chart type from the drop-down menu
    • The chart will appear in the worksheet.
  • Change the Chart Type
working with charts1
Working with Charts
  • Switch Row and Column Data
    • Select the chart.
    • From the Design tab, select the Switch Row/Column command.
    • The chart will then readjust

Before

After

working with charts2
Working with Charts
  • Change the Chart Layout
  • Change the Chart Style
  • Move the Chart to a Different Worksheet
    • Select the Designtab
    • Click the Move Chart command. A dialog box appears. The current location of the chart is selected
    • Select the desired location for the chart (i.e., choose an existing worksheet, or select New Sheet and name it).
working with sparklines
Working with Sparklines
  • Types of Sparklines
  • Why Use Sparklines?
    • Sparklines are ideal for situations where you just want to make the data clearer and more eye-catching, and where you don't need all of the features of a full chart. On the other hand, charts are ideal for situations where you want to represent the data in greater detail, and they are often better for comparing different data series.

Win/Loss

Column

Line

working with sparklines1
Working with Sparklines
  • Create Sparklines
    • Select the cells that you will need for the first sparkline
    • Click the Inserttab
    • In the Sparklines group, select Line. A dialog box will appear
    • Make sure the insertion point is next to Location Range
    • Click the cell where you want the sparkline to be.
working with sparklines2
Working with Sparklines
  • Click OK. The sparkline will appear in the document
  • Click and drag the fill handledownward
  • Sparklines will be created for the remaining rows.
conditional formatting
Conditional Formatting
  • 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
  • Create a Conditional Formatting Rule

.

conditional formatting1
Conditional Formatting
  • Conditional Formatting Presets
    • Data Bars are horizontal bars added to each cell, much like a bar graph
    • Color Scales change the color of each cell based on its value.
    • Icon Sets add a specific icon to each cell based on its value
  • Use Preset Conditional Formatting
    • Select the cells you want to add the formatting to
    • In the Home tab, click the Conditional Formatting command. A drop-down menu will appear
    • Select Data Bars, Color Scales or Icon Sets (Data Bars, for example). Then, select the desired preset
    • The conditional formatting will be applied to the selected cells.
conditional formatting2
Conditional Formatting
  • Remove Conditional Formatting Rules
    • Select the cells that have conditional formatting.
    • In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
    • Select Clear Rules.
    • A menu will appear. You can choose to clear rules from the Selected Cells, Entire Sheet, This Table, or This PivotTable.
pivottables
PivotTables
  • When you have a lot of data, it can sometimes be difficult to analyze all of it. A PivotTable summarizes the data, making it easier to manage. Best of all, you can quickly and easily change the PivotTable to see the data in a different way, making this an extremely powerful tool.
  • Using PivotTables to Answer Questions
    • "What is the amount sold by each salesperson?“
pivottables1
PivotTables
  • Create a PivotTable

1

2

3

pivottables2
PivotTables
  • Add Fields to the PivotTable
what if analysis
What-If Analysis
  • Goal Seek
    • It lets you start with the desired result, and it calculates the input value that will give you that result
  • Use Goal Seek
    • Select the cell whose value you wish to change. Whenever you use Goal Seek, you'll need to select a cell that already contains a formula or function.
what if analysis1
What-If Analysis
  • From the Data tab, click the What-If Analysis command and then select Goal Seek from the drop-down menu.
  • A dialog box will appear with three fields:
  • When you're done, click OK
  • The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
what if analysis2
What-If Analysis
  • The result will appear in the specified cell.