Microsoft Excel Training (Basic)Part 1.pptx

Introduction to MS Excel to Formatting

1. By: RIANNEL B. TECSON -------------------------------- September 27, 2025 -------------------------------- Proficiency

2. Goal: Build Excel proficiency from beginner to advanced to support administrative tasks, data recording and reporting tasks at Techno Trade Resources, Inc.

3. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 1:00 – 1:30 PM Beginner Introduction to Excel & Interface Familiarization - Hands-on tour of Excel interface. Activity 1. Participants can open, navigate, and format Excel workbooks relevant to Techno Trade products. - Ribbon & Tabs - Participants open a blank workbook and navigate through tabs. - Workbooks & Worksheets - Format a sample company data sheet (Techno Trade’s product list). - Basic Cell Formatting   1:30 – 2:00 PM Beginner Basic Data Entry & Formatting - Enter sample data: METALPLAS product specs, materials, and costs. Activity 2. Participants can encode and format product/price data efficiently. - Entering data accurately - Use AutoFill for repetitive entries. - Using AutoFill & Flash Fill - Apply cell formatting for readability. - Basic Formatting: fonts, borders, colors  

4. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 2:00 – 2:45 PM Intermediate Formulas & Functions - Compute total raw material costs using SUM. Activity 3. Participants can compute totals, averages, and apply conditional formulas in company worksheets. - Basic Math: SUM, AVERAGE, COUNT - Use IF to categorize suppliers by cost. - Conditional Functions: IF - Practice referencing cells in a METALPLAS sample price sheet. - Relative vs. Absolute References   2:45 – 3:20 PM Intermediate Data Sorting, Filtering, & Table Creation - Sort supplier list by price or lead time. Activity 4. Staff can organize and analyze supplier/ product data. - Sort ascending/descending - Filter out non-compliant suppliers. - AutoFilter - Convert raw data into an Excel Table. - Format as Table   3:20 – 3:30 PM HEALTH Break

5. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 3:30 – 4:15 PM Intermediate – Advanced Data Validation & Conditional Formatting - Create a drop-down list of suppliers for METALPLAS. Activity 5. Participants can apply data validation and highlight issues automatically. - Drop-down lists - Highlight duplicate entries using Conditional Formatting. - Highlight duplicates - Color code late deliveries. - Visual cues for quality control   4:15 – 4:45 PM Advanced PivotTables & PivotCharts - Generate a PivotTable of sales by region for METALPLAS products. Activity 6. Participants can produce summaries and visual dashboards from sales or testing data. - Summarize large data - Insert a PivotChart to show product performance. - Create visual charts   PivotTables & PivotCharts - Generate a PivotTable of sales by region for METALPLAS products.

6. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 4:45 – 5:15 PM Advanced Advanced Formulas - Use VLOOKUP to pull supplier contact info from a master list. Activity 7. Participants can use lookups and text functions to streamline reporting. =- VLOOKUP / XLOOKUP' - Combine text fields into a single column for reports. =- CONCATENATE / TEXTJOIN' - Handle missing values using IFERROR. - Error handling (IFERROR)'   5:15 – 5:45 PM Advanced Automation Basics with Macros - Record a Macro to format monthly METALPLAS sales reports. Activity 8. Participants can automate repetitive Excel tasks. - Recording a Macro - Assign the macro to a Quick Access button. - Assigning a Macro to a Button   Automation Basics with Macros - Record a Macro to format monthly METALPLAS sales reports. 5:45 PM onwards Recap all levels. Q&A.

7. 📝 Friendly House Rules for Training 1. Be Ready to Learn. Let’s make the most of our learning together. 2. Bring Your Own Essentials. Ensure your laptop is fully charged (or bring your charger) and have Excel installed or accessible before the session. 3. Respect Everyone’s Turn. Listen when someone is speaking or presenting. Avoid interrupting and let everyone share their insights. 4. Hands-On Participation. Follow along with the exercises on your own screen. Practice is the best way to learn! 5. Ask Questions Freely. No question is too small or too big—feel free to ask for clarification at any time. 6. Stay on Training Topics. Focus on the Excel activities during the session. 7. Help Each Other Out. If a participant needs help and you know the answer, feel free to assist—but kindly wait for your turn. 8. Take Breaks as Scheduled. Short breaks will be given. Please return promptly to keep the training flowing smoothly. 9. Enjoy and Learn! Bring a positive attitude. Let’s make the training fun, interactive, and productive.

8. KISS ~ Keep It Simple Stupid!

9. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 1:00 – 1:30 PM Beginner Introduction to Excel & Interface Familiarization - Hands-on tour of Excel interface. Activity 1. Participants can open, navigate, and format Excel workbooks relevant to Techno Trade products. - Ribbon & Tabs - Participants open a blank workbook and navigate through tabs. - Workbooks & Worksheets - Format a sample company data sheet (Techno Trade’s product list). - Basic Cell Formatting  

10. Activity 1 . Format Company Data Sheet

11. Activity 1 . Format Company Data Sheet Apply Formatting Title : On top of your sheet, type: “ Techno-Trade Resources, Inc. – Company Data Sheet ” Merge and center the title across all columns. Make it bold and increase the font size to 14 or 16. Column Headers: Bold the headers. Apply a background color (light blue or gray). Center the text. Data Rows: Adjust column widths so all data is visible. Use consistent font (e.g., Calibri 11). Align text properly (names left, numbers right/center). Add Borders: Apply all borders to the data area. Use Cell Styles: Apply a cell style to make the title stand out (e.g., Heading 1 style). Add at least 5 rows of sample data (you may make up names and details).

12. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 1:30 – 2:00 PM Beginner Basic Data Entry & Formatting - Enter sample data: METALPLAS product specs, materials, and costs. Activity 2. Participants can encode and format product (services)/price data efficiently. - Entering data accurately - Use AutoFill for repetitive entries. - Using AutoFill & Flash Fill - Apply cell formatting for readability. - Basic Formatting: fonts, borders, colors  

13. What is AutoFill & Flash Fill in Excel? AutoFill is a feature that automatically fills cells with data based on a pattern or series. You can also use AutoFill for: Copying a formula into other cells. Repeating text or numbers. Filling dates or months automatically. Flash Fill is a smart feature that automatically fills in values when it detects a pattern in your data. It’s like Excel “guessing” what you want based on an example you type. You can use Flash Fill to: Split text into parts (first/last names, addresses, etc.). Combine data from multiple columns. Reformat data (like phone numbers or dates). It’s available on the Data tab → Flash Fill button, or you can use the shortcut Ctrl + E.

14. Activity 2 . En code and format product/price data

15. Activity 2 . En code and format product/price data Using AutoFill: In column A, type “Count” Enter 1 in cell A3 and 2 in cell A4. Use AutoFill to drag down and create serial numbers for all rows automatically.

16. Activity 2 . En code and format service/price data

17. Activity 2 . En code and format service/price data Using Flash Fill Insert a new column (C) called “Short Name.” Type in cell C3 a short version of the service (e.g., “Installation” for Installation of MetalPlas Sheets). Start typing similar patterns for the next row. Use Flash Fill (Ctrl + E) to automatically fill the remaining rows based on your pattern.

18. Training Matrix Time Level Topic / Skill Area Concrete Activities Output / Expected Competency 2:00 – 2:45 PM Intermediate Formulas & Functions - Compute total raw material costs using SUM. Activity 3. Participants can compute totals, averages, and apply conditional formulas in company worksheets. - Basic Math: SUM, AVERAGE, COUNT - Use IF to categorize suppliers by cost. - Conditional Functions: IF - Practice referencing cells in a METALPLAS sample price sheet. - Relative vs. Absolute References  

19. Parts of a Formula Functions References Constants Operators

20. Formula Example ~ Single Argument =SUM(A1:A10)  is an example of a single argument.

21. Mathematical Formulas Sum Count Counta Sumif Average Round Product Roman

22. SUMIF

23. SUMIF We want to know how many HP Laser Jet Printers we have. =sumif(a2:a14,”HP Laser Jet”,c2:c14) Which equals 11 We want to know how many HP Laser Jet Printers the POLICE have. = sumifs (c2:c14,a2:14,”HP Laser Jet”,b2:b14,”Police”) Which equals 3

24. SUMIF

25. AVERAGE

26. ROUND

27. Using Rounding for Budgeting

28. ROMAN/ARABIC

29. Logical Formulas If And Or Not Choose Iferror Istext

30. IF

31. IFERROR

32. Text Formulas Proper Trim Dollar Rept Text Type

33. Shortcuts Keyboard Shortcuts Insert Function Define Name Error Checking Watch Window

34. Keyboard Shortcuts F2 Ctrl-Home Double Click to change a tab/sheet name Copy sheet within a workbook Right Click to get Menu Press “Alt” Key and letters appear called “Key Tips”

35. F2

36. Double Click to Change a Tab Name

37. Copy a Sheet ~ Copies WITH Format

38. Right Click Menu

39. ALT Key to see “Key Tips”

40. Error Messages

41. Error Types Error Type When It Happens #DIV/0! When you divide by ZERO #N/A! When a formula or a function inside a formula cannot find the referenced data. #NAME? When the text in a formula is not recognized. #NULL! When a space was used instead of a comma in formulas that reference multiple ranges. A comma is necessary to separate range references.

42. Error Type When It Happens #NUM! When a formula has numeric data #REF! When a reference is invalid. #VALUE! When the wrong type of operand or function argument is used Error Types

43. ###########

44. #DIV/0!

45. #N/A! Excel displays this error when a value is not available to a function or formula. =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)

46. #NAME?

47. #NULL =SUM(C2:C3 E4:E6)

48. #REF! This can happen when you delete a row or column in error. You can “undo” using Ctrl+Z or the undo on the formula bar. OR fix the formula to be “continuous” =SUM(A2:C2)

49. #VALUE! This is displayed when a cell contains different types of data. One way to fix this is to use =SUM(F2:F5)

50. Absolute and Relative Referencing Absolute cell reference contains a ($) in a Row and/or Column Do not change when copied or filled Use when you want to consistently refer to a certain cell A1 Relative A$1 Column is relative; Row is Constant $A1 Row is relative; Column is absolute $A$1 BOTH are Absolute

51. Order of Operations The Order of Operations tells Excel which operation to calculate first. Parenthesis Exponents Multiplication & Division Addition & Subtraction

52. Circular Reference A circular reference occurs when a cell refers to itself.

53. Common Formula Errors Start with an “EQUAL SIGN” = If you omit the equal sign, Excel thinks the data is “TEXT” Match all open and closing parentheses This gets more complicated as you add more formulas =IF(B5<0),”Not Valid”,B5*1.05) – EXTRA Parentheses after <0 Use a colon to indicate a range =SUM(A1:A5) not =SUM(A1 A5) returns #NULL

54. Common Formula Errors Enter all required and correct type of arguments Ex. Cannot combine SUM & REPLACE (Numerical vs Text) Enter numbers without formatting Ex. If you enter 1,000 into a formula vs 1000, it treats it like a comma separator looking at it like “1,000” or 1 CUT vs COPY Relative vs Absolute

55. Formulas inconsistent with other formulas

56. Formulas that omit cells in a group

57. Error Checking ~ Reset I gnored Errors

58. Formulas Referring T o E mpty C ells

59. Error checking

60. Ways to AUDIT your Spreadsheet Inspect Workbook F2

61. Find & Select Find & Select is found on the Home Tab Find and replace Find Formulas, Comments, etc

62. Trace Precedents/Dependents

63. Watch Window Used when cells and their formulas are not visible on a worksheet. On large spreadsheets, you don’t have to repeatedly go to different parts of you spreadsheet to confirm formula calculations.

64. Add cells to the Watch Window

65. Specify What You Want to “Watch”

66. Now I can see how changes effect amount to be raised no matter where I go in the sheet

67. Evaluate Formula

68. Evaluate Formula

69. No Formulas

70. Show Formulas