1 / 29

Excel Mastery: Essential Formulas and Formatting Tips

Enhance your Excel skills with this detailed guide on writing formulas, formatting cells, and utilizing functions efficiently. Explore cell alignment, font styles, number formats, and more to create professional spreadsheets effortlessly.

tiger-glass
Télécharger la présentation

Excel Mastery: Essential Formulas and Formatting Tips

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. “Thousands of candles can be lighted from a single candle, and the life of the candle will not be shortened. Happiness never decreases by being shared.” -Buddha

  2. Day 6:Excel Chapter 1 Cont’d Cody Cutright cody.cutright@mail.wvu.edu September 3rd, 2014

  3. MYITLAB! • MYITLAB LESSON A IS DUE SEPTEMBER 2ndby 11:59pm! • *THIS IS A TUESDAY – WE DON’T HAVE CLASS THE DAY BEFORE! • DO NOT WAIT TO START ON MYITLAB!

  4. MyITLAB Issues • If you are experiencing issues registering, and you have correctly followed the registration steps: • Contact Pearson Technical Support (24/7) • (855) 875-1797 • Or use this link for other options: http://cs101.wvu.edu/resources/myitlab/technical-support/

  5. Column / row width • Can be set by pixel width/height • Select -> Right Click -> Column Width / Row Height • Can be set to auto width • Select -> Home Ribbon -> Cells -> Format

  6. Formatting Why Format? • Easier to understand • Professional appearance • …it just looks nicer, like you know what you’re doing A lot of what we’re going to cover about formatting can be found in one place, Format Cells!

  7. Cell alignment • Horizontal Cell Alignment • Align Left • Center • Align Right • Vertical Cell Alignment • Top Align • Middle Align • Bottom Align

  8. Font Some fonts, are easier to read, than others. Things to consider: • Font • Size • Color Just right click!

  9. Cell Styles Premade cell themes, but you can create your own. Home -> Styles -> Cell Styles

  10. Cell styles – cont’d

  11. More formatting!!! Colors/Shading - Fill Color Button Borders - On the sides - Through the cell - Outline cell groups - Inline cell groups

  12. Number formats Reading numbers that aren’t formatted the same is time consuming and difficult. Ex: Your grades: 95, 98.4532154, 200/197, .784 Right Click -> Format Cells -> Number Tab!

  13. Page layout -> page setup (Or, Ctrl+P) • Margins • Orientation (Landscape / Portrait) • Size • Fit to • Bottom Right Corner • Header Footer

  14. Excel Chapter 2 Cody Cutright cody.cutright@mail.wvu.edu August 27, 2014

  15. Writing Formulas We’ve covered the very basics: using the standard operators, typing cell references. We know that a formula may reference the contents of other cells, and even on other sheets!

  16. Semi-selection • Click the cell where you want to create the formula. • Start typing the formula (=) • Select the cell, or range of cells, with your mouse pointer • Type a mathematical operator • Go to step 3, until done. • Press Enter to complete it!

  17. Cell references in formulas When you copy or autofill a formula, Excel may or may not adjust any cell references depending on how they were entered! Keep track of whether or not cell references need to adjust in a formula, or need to always reference the same cell!

  18. Cell references – cont’d • Relative Cell Reference • Indicates a cell’s relative location from the cell containing the formula • Ex: A8 • Absolute Cell Reference • Indicates a cell’s specific location • Ex: $B$65 • Mixed Cell Reference • Contains an absolute and a relative reference • When copied, the absolute remains the same and the relative changes **Hint: Save time by pressing “F4” to adjust cell references!

  19. Sidebar: green triangles Excel displays a green triangle in the top-left corner of a cell if it detects a potential error in a formula! Clicking on the cell displays the Trace-Error Button, which when clicked, displays information about the potential error.

  20. Functions • Excel has powerful built in functions, designed to save hours of formula creation! • Basic statistical functions include: SUM, AVERAGE, MEDIAN, MAX, MIN • These are found under the sigma button under the Home Ribbon

  21. Functions – cont’d If you can’t remember all of the functions, it’s ok! Excel will offer suggestions much like searching on Google- try it out! In the formula bar for any cell, type =S and Excel suggests a list of possible functions!

  22. Basic function use Example: To calculate the average of two cells, in a blank cell type =AVERAGE(Cell1,Cell2) To calculate the SUM over a range of cells, try =SUM(Cell1:Cell100)

  23. Rank.eq Let’s use the formula bar suggestions to view the help for this one!

  24. If FUNCTIONS The IF function evaluates a condition and returns one value if the condition is true, and a different value if the condition is false. So it has three parts: • The condition • The resulting value for a true outcome • The resulting value for a false outcome

  25. If functions – cont’d General Form: =IF(logical_test,value_if_true,value_if_false)

  26. If Function examples

  27. VLOOKUP / HLOOKUP • VLOOKUP requires the first column to contain unique identifiers • HLOOKUP requires the first row to contain unique identifiers • (Microsoft Link for Reference) • Great YouTube Video

More Related