EXCEL.pdf

LABORATORY Introduction to Microsoft Excel Formatting Cells Conditional Formatting Relative & Absolute Referencing Formatting Cells Formatting cells involves customizing the appearance of cell content to improve readability and organization within a workbook. Applying basic formatting option...

LABORATORY Introduction to Microsoft Excel Formatting Cells Conditional Formatting Relative & Absolute Referencing Formatting Cells Formatting cells involves customizing the appearance of cell content to improve readability and organization within a workbook. Applying basic formatting options allows you to highlight specific sections and make your data easier to view and understand. Conditional formatting Provides a way to visualize data and make worksheets easier to understand. Allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value. Conditional formatting rule example: If the value is less than $2000, color the cell red 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. Each color scale uses a two- or three-color gradient. For example, in the Green-Yellow-Red color scale, the highest values are green, the average values are yellow, and the lowest values are red. Icon Sets add a specific icon to each cell based on its value. Relative & Absolute References Relative references It changes when a formula is copied to another cell By default, all cell references are relative references When copied across multiple cells, they change based on the relative position of rows and columns Relative & Absolute References Absolute references It remain constant no matter where they are copied of filled You can use an absolute reference to keep a row and/or column constant. Is designated in a formula by the addition of a dollar sign ($). $A$2 The column and the row do not change when copied A$2 The row does not change when copied $A2 The column does not change when copied  10+(6-3)/2^2*4-1 Parentheses 10+(6-3)/2^2*4-1 Exponents 10+3/2^2*4-1 Multiplication 10+3/4*4-1 Whichever Division 10+0.75*4-1 comes first Addition 10+3-1 Whichever Subtraction 13-1 comes first = 12 Scenario Context: A first-year Medical Laboratory Science student, Sheena, is participating in a university-organized blood-letting activity. Her role in the event is to collect and input donor a information into a centralized spreadsheet using een Microsoft Excel. The donors fill out a physical form with h details such as Donor ID, Name, Age, Gender, Blood Type, Contact Number, and Health Status. S Challenge: Sheena's task is to ensure that the data she inputs from Ey! the forms is accurate and free from errors. Given the large number of participants, it is easy to make mistakes like entering invalid ages, incorrect blood types, or misspelling names. Sheena remembers that in her Data Management class, she learned about Data Validation. To improve accuracy and minimize errors, she decides to apply these techniques. Macros Macros in MS Excel are automated sequences of instructions that allow users to record and execute repetitive tasks with a single command. They streamline workflows by performing complex actions efficiently, saving time and reducing the risk of errors. LABORATORY Introduction to Microsoft Excel Lesson Objectives At the end of this session, you are expected to: 1. Understand the basic Microsoft Excel interface, including ribbons, worksheets, cells, and the formula bar. 2. Enter and modify text, numbers, as well as navigate the worksheet, and edit cell content. 3. Understand formula syntax (starting with "=") and use simple math operators (+, -, *, /). 4. Apply basics of sorting & filtering, inserting a table, and inserting dropdown lists What is Microsoft Excel? It is a spreadsheet program developed by Microsoft. Excel organizes data in columns and rows and allows you to do mathematical functions. It runs on Windows, macOS, Android and iOS. The first version was released in 1985 and has gone through several changes over the years. However, the main functionality mostly remains the same. Excel is typically used for: Analysis Data analysis Data entry Visuals and graphs Data management Programming Accounting Financial modeling Budgeting And much, much more! Why Use Microsoft Excel? It is the most popular spreadsheet program in the world It is easy to learn and to get started. The skill ceiling is high, which means that you can do more advanced things as you become better It can be used with both work and in everyday life, such as to create a family budget It has a huge community support It is continuously supported by Microsoft Templates and frameworks can be reused by yourself and others, lowering creation costs Navigating the Excel Environment + + + + + + + + + + + + Navigating the Excel Environment + + + Quick Access Toolbar The Quick Access Toolbar + + you access common lets commands no matter which + tab is selected. You can customize the commands depending on your preference. + + + + + + Navigating the Excel Environment + + + The Ribbon + all of the + Ribbon contains The commands you will need to + perform common tasks in Excel. It has multiple tabs, each with several groups of commands. + + + + + + Navigating the Excel Environment + + + + + Name Box + The Name box displays the location, or+name, of a selected cell. + + + + + Navigating the Excel Environment + + + + + Formula Bar + In the formula bar, you can + enter or edit data, a formula, or a function that will appear in a specific cell. + + + + + Navigating the Excel Environment + + + + + + 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. + + + + + Navigating the Excel Environment + + + + + + + Cell Each rectangle in a workbook is called a cell. A cell is the intersection of a row and a column. Simply click to select a cell. + + + + + Navigating the Excel Environment + + + + + + + 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. + + + + + Navigating the Excel Environment + + + + + + + Worksheets Excel files are called + workbooks. Each workbook holds one or more worksheets. Click the tabs to switch between them, or right- click for more options. + + + + Navigating the Excel Environment + + + + + + + Vertical and Horizontal Scroll Bars + The scroll bars allow you to scroll up and down or side to side. To do this, click and drag the vertical or horizontal scroll bar. + + + + Navigating the Excel Environment + + + + + + + + Zoom Control Click and drag the slider to use the zoom control. The number to the right of the slider reflects the zoom percentage. + + + + Navigating the Excel Environment + + + + + + + Worksheet View + Options There are three ways to view a worksheet. Simply click a command to select the desired view. + + + + Navigating the Excel Environment + + + Tell me + + The Tell me box works like a search bar to help you + quickly find tools or commands you want to use. + + + + + + Welcome to Excel: Take a tour to the most popular spreadsheet app LABORATORY Introduction to Microsoft Excel Must-Know Excel Terms 1. Cell: The basic unit in an Excel worksheet where data is entered, located at the intersection of a row and a column. 2. Worksheet: A single page within an Excel workbook, consisting of a grid of cells organized into rows and columns. 3. Workbook: A file containing one or more worksheets in Excel. 4. Formula: A mathematical expression entered in a cell that calculates a value, starting with an equals sign (=), e.g., =SUM(A1:A10). 5. Function: A predefined formula in Excel that simplifies complex calculations, such as SUM, AVERAGE, IF, and VLOOKUP. 6. Range: A selection of two or more cells in a worksheet, e.g., A1:B10. 7. Cell Reference: The unique identifier of a cell, based on its column letter and row number, e.g., A1. Must-Know Excel Terms 8. Relative Reference: A cell reference that changes when a formula is copied to another cell, adjusting based on the relative position. 9. Absolute Reference: A cell reference that remains constant, even when copied to another location, indicated by dollar signs ($), e.g., $A$1. 10. Pivot Table: A powerful tool in Excel used to summarize, analyze, and explore large sets of data by arranging it in a table format. 11. Chart: A visual representation of data in Excel, such as a bar chart, pie chart, or line chart. 12. Data Validation: A feature that restricts the type of data that can be entered into a cell, ensuring data accuracy and consistency. 13. Conditional Formatting: A feature that changes the appearance of cells based on specified criteria, such as highlighting cells that meet a certain condition. Must-Know Excel Terms 14. Sorting: The process of arranging data in a specific order, either ascending or descending. 15. Filtering: A feature that allows you to display only the rows in a worksheet that meet certain criteria, hiding the others. 16. Freeze Panes: A feature that keeps specific rows or columns visible while scrolling through a worksheet. 17. AutoFill: A tool that automatically fills cells with data, based on a pattern established in adjacent cells. 18. Data Table: A range of cells that organizes and summarizes data, often used in conjunction with scenarios and sensitivity analysis. 19. VLOOKUP: A function that looks up a value in the first column of a range and returns a value in the same row from another column. 20. Macro: A sequence of instructions that automates repetitive tasks in Excel, created using the Visual Basic for Applications (VBA) programming language. Functions in Excel Basic Functions Function Usage Example Adds up all the numbers in a specified =SUM(A1:A10) adds the values in cells A1 SUM() range of cells. through A10. Calculates the average of a range of =AVERAGE(B1:B10) finds the average of the AVERAGE() numbers. values in cells B1 through B10. Returns the smallest number in a =MIN(C1:C10) finds the smallest value in the MIN() range of cells. range C1 through C10. Returns the largest number in a range =MAX(D1:D10) finds the largest value in the MAX() of cells. range D1 through D10. Counts the number of cells in a range =COUNT(E1:E10) counts the number of numeric COUNT() that contain numbers. values in cells E1 through E10. Counts the number of non-empty =COUNTA(F1:F10) counts all non-empty cells in COUNTA() cells in a range. F1 through F10. Rounds a number to a specified number =ROUND(G1, 2) rounds the value in G1 to two ROUND() of digits. decimal places. Functions in Excel Logical Functions Function Usage Example =IF(H1>50, "Pass", "Fail") returns "Pass" if the Returns one value if a condition is true IF() value in H1 is greater than 50; otherwise, it and another if it’s false. returns "Fail." =AND(I1>50, J150, L150) returns TRUE if M1 is not greater NOT() argument. than 50. Functions in Excel Lookup and Reference Functions Function Usage Example =VLOOKUP(N1, A1:D10, 3, FALSE) looks for the Searches for a value in the first value in N1 in the first column of the range A1and VLOOKUP() column of a range and returns a value returns the value from the third column of that in the same row from another column. range. =HLOOKUP(O1, A1:D10, 3, FALSE) looks for the Searches for a value in the top row of value in O1 in the top row of the range A1and HLOOKUP() a range and returns a value in the returns the value from the third row of that same column from a specified row. range. Returns the value of a cell at the =INDEX(A1:D10, 3, 2) returns the value in the INDEX() intersection of a row and column in a third row and second column of the range A1 given range. Returns the relative position of an item =MATCH(P1, A1:A10, 0) returns the position of MATCH() in a range that matches a specified the value in P1 within the range A1. value. Functions in Excel Text Functions Function Usage Example CONCATENATE() Combines text from multiple cells =CONCATENATE(Q1, " ", R1) combines the / CONCAT() into one cell. values of Q1 and R1 with a space in between. Returns a specified number of =LEFT(S1, 3) returns the first three characters of LEFT() characters from the start of a the text in S1. text string. Returns a specified number of =RIGHT(T1, 3) returns the last three characters RIGHT() characters from the end of a text of the text in T1. string. Returns a specified number of =MID(U1, 2, 3) returns three characters from U1, MID() characters from the middle of a starting at the second character. text string. Removes extra spaces from a =TRIM(V1) removes extra spaces from the text in TRIM() text string, leaving only single V1. spaces between words. Functions in Excel Date and Time Functions Function Usage Example TODAY() Returns the current date. =TODAY() returns today’s date. Returns the current date and NOW() =NOW() returns the current date and time. time. Calculates the difference =DATEDIF(W1, X1, "D") returns the number of DATEDIF() between two dates. days between the dates in W1 and X1. =DAY(Y1) returns the day of the month from the DAY() Extracts the day from a date. date in Y1. =MONTH(Z1) returns the month number from MONTH() Extracts the month from a date. the date in Z1. =YEAR(AA1) returns the year from the date in YEAR() Extracts the year from a date. AA1.