Introduction to Microsoft Excel PDF

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

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.