Basics to Advanced Microsoft Excel 2010 Computer 1

Introduction to excel

1. BASICS TO ADVANCED MICROSOFT EXCEL 2010 COMPUTER 1

2. INTRODUCTION: WHAT IS MS EXCEL? Developed since 1982 by Microsoft, introduced as “Multiplan” Rebranded in 1988 as “Excel” Most familiar, flexible, and widely used business application in the world

3. OUTLINE Terminologies & Parts of MS Excel Types of Data Encoded in Cells Cursor changes in Excel Formatting Cells Relevant Functions and Formula Mastering Charts Excel outputs: Pivot Table & Print Preview PROJECT 1: HRMD & Class Files PROJECT 2: Basic FI Reporting

4. 1. TERMINOLOGIES & PARTS OF MS EXCEL Workbook = huge electronic books with pages called sheets that have been ruled into columns and rows Has 16,384 columns labeled using letters (MS 2016) Has 1,048,576 rows labeled using numbers (MS 2016) Cell = where you put data. Cell Address : (Column)&(Row) e.g., A1, XFD1048576

5. 1. TERMINOLOGIES & PARTS OF MS EXCEL Name Box = where the cell address is found Formula Bar = where the contents/formula of a cell is found Status Bar = found at the bottom of the Excel window and provides info such as sum, average, min & max value of selected numbers

6. 2. TYPES OF DATA ENCODED IN CELLS Number data Text data (String) Logical Data Error Data

7. A. NUMBER DATA Numerical data that automatically aligns to the right of the cell, such as: Number Currency Date & Time Percentage Fraction Scientific

8. B. TEXT DATA Any text/string data that automatically aligns to the left of the cell, such as: Letters/Words/Sentences Unformatted Dates & Times Addresses

9. C. LOGICAL DATA Either TRUE or FALSE only

10. D. ERROR DATA #NAME? = value in the formula lacks a quote or there is a typo error in the formula #DIV/0 = number divided by zero #REF! = invalid cell reference #N/A = you can put this value if you want to put a value here later #VALUE! = an error in an argument/operator in a function formula

11. 3. CURSOR CHANGES IN EXCEL

12. 4. FORMATTING CELLS Auto-Fills, F2, F4 ($) Text Alignment & Wrapping Texts Formatting Numbers Sort and Filter Conditional Formatting

13. 5. RELEVANT FUNCTIONS AND FORMULA Basic Math Operations: +,-,*,/,^,(),=,<,>,<> Basic Statistics: SUM, COUNT, AVERAGE Conditional: IF, COUNTIF, SUMIF, AVERAGEIF Comparative (%) Auto-Age: TODAY(), YEAR

14. 6. MASTERING CHARTS Visual representations help us to understand data quickly . When you show an effective graph or chart, your report or presentation gains clarity and authority Line Graph = useful for showing trends & correlation Bar Graphs = useful for showing trends with few discontinuous data (number of people etc.) Pie Chart = shows a percentage distribution

15. 7. EXCEL OUTPUTS: PIVOT TABLE & PRINT PREVIEW Pivot Table = used to summarize & reorganize selected columns and rows of data in spreadsheet or database table to obtain a desired report How to make pivot table How to print sheets in Excel

16. 8. PROJECT #1: HRMD FILES & CLASSROOM MANAGEMENT First Name Middle Name Surname Birthday Age (Formula-based) Civil Status Age Category (Conditional Formatting) Spiritual Level Active/Inactive etc. Address Attendance & Summary

17. 9. BASIC FI REPORTING Name Cash on Hand Gross Income Capital Net after Capital Outright expenses Tithes Net income Net income + tithes Summary