class: center, middle, inverse, title-slide .title[ # Excel Mastery Training for PID Staff ] .subtitle[ ## A journey into World of Excel ] .author[ ### YUSUF, Akintunde Azeez ] .date[ ### 2024-08-07 ] --- ### Topics to be discussed: >- Let the journey begin! >- Take a walk with me into the world of Excel. >- Formulas and Functions. >- Data Presentations. >- Working with Data. --- class: center middle ## Let the journey begin! --- ### What is Excel? > Excel is a spreadsheet program that allows you to store, organize, and analyze information. Whether you're keeping a budget, organizing a training log, or creating an invoice, Excel makes it easy to work with different types of data. -- ### Why Excel? -- >1. It allows users to create and edit data sets -- >2. perform calculations -- >3. mathematical functions -- >4. create tables with complex database -- >5. generate great charts and graphs for data visualization --- class: center middle ## Take a walk with me into the world of Excel. --- .pull-left[ >**Worksheets:** Excel files are called workbooks. Each workbook holds one or more worksheets. ] .pull-right[  ] -- > **Workbook:** A file containing a collection of one or more worksheets -- > **Formula:** A piece of code to perform a calculation. Formula starts with an equals sign **(=)**, and contain functions, mathematical operators, values, and references. -- >**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. -- >**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. -- >**Cell:** Each rectangle in a workbook is called a cell. A cell is the intersection of a row and a column. In other words, it's where a row and column meet. Every worksheet is made up of thousands of rectangles, which are called cells. -- >**Commands:** A Command is button, A menu or a box where you enter information. --- .pull-left[ ### Ribbons Ribbons consist of **Tabs, groups and commands** There are several **tabs** across the Top of the EXCEL window depending on customisation. ] .pull-right[  ] -- .pull-left[ ### Groups Groups are sets of related commands, displayed on tabs.. ] .pull-right[  ] --- .pull-left[ ### Name box The Name box displays the location, or name, of a selected cell. ] .pull-right[  ] -- .pull-left[ ### Formula Bar In the formula bar, you can enter or edit data, a formula, or a function that will appear in a specific cell. ] .pull-right[  ] --- ### Reference While you can create simple formulas in Excel using numbers (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula. In the formula below, cell A3 adds the values of cells A1 and A2 by making cell references: -- .pull-left[ When you press Enter, the formula calculates and displays the answer in cell A3: ] .pull-right[  ] --- ### Reference continue .pull-left[ By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as shown here ] .pull-right[  ] --- ### Using relative references: We want to create a formula that will multiply each items by the quantity. Instead of creating a new formula for each row, we can create a single formula in cell D4 and then copy it to the other rows. We'll use **relative references** so the formula calculates the total for each item correctly. -- There may be a time when you don't want a cell reference to change when copied to other cells. Unlike relative references, **absolute references** do not change when copied or filled. You can use an absolute reference to keep _a row and/or column constant_. An absolute reference is designated in a formula by the addition of **a dollar sign ($)**. It can precede the column reference, the row reference, or both. --- class: center middle ## Formulas and Functions --- ### Sort Using Excel Sort, you can easily sort your data. You can sort your data in Ascending/Descending order if the column contains numbers or dates, and also sort in A-Z/Z-A order (based on the language used – it works for various languages) .pull-center[  ] --- ### Using Find & Replace -- This is used to a particular match and also to replace the said match. -- .pull-left[ From the Home tab, click the Find & Select command, then select Replace... from the drop-down menu. The **Find and Replace** dialog box will appear. Type the text you want to find in the Find what: field. Type the text you want to replace it with in the Replace with: field, then click Find Next. If the content is found, the cell containing this content will be selected. Review the text to make sure you want to replace it. If you want to replace it, select one of the replace options. Choosing Replace will replace individual instances, while Replace All will replace every instance of the text throughout the workbook. ] .pull-right[  ] --- .pull-left[ ### Formula One of the most powerful features in Excel is the ability to calculate numerical information using formulas. ] .pull-right[  ] -- ### Mathematical operators Excel uses standard operators for formulas: a plus sign for **addition (+)**, **minus sign for subtraction (-)**, **asterisk for multiplication (*)**, **forward slash for division (/)**, and **caret (^)** for exponents. All formulas in Excel must begin with an **equals sign (=)**. This is because the cell contains, or is equal to, the formula and the value it calculates. --- ### vlookup **VLOOKUP** is probably the most popular function in Excel, and one of the most helpful functions for everyday use. -- **VLOOKUP** helps us lookup a value in table, and return a corresponding value. -- Syntax =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup] -- - lookup_value – what we are looking for – this could be a text, number, or a single cell reference -- - table_array – the range in which we will lookup for our value and its corresponding result. Please note that the range must start from the column which contains the value, and should contain the column in which we have our result. -- - col_index_num – What is the column number from which we want to return the result? The number should be relative to the first column in the selected range in table_array. -- - [range_lookup] – Which range lookup method should be used. 0 is the default, so you should always type 0 (or FALSE), which means “Exact Match” – Go to the exact match to the value I’m looking for. 1 stands for “Approximate match”, and it should not be used on most cases so we’ll skip it for now. --- ### xlookup The **XLOOKUP** function searches a range or an array, and then returns the item corresponding to the first match it finds. The **XLOOKUP** function is only available in Excel for Microsoft 365, Excel 2021, and Excel for the web. -- Syntax =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) -- - lookup_value – What is the value we are looking for? -- - lookup_array – Where should we lookup our value? -- - return_array – From where should the corresponding value be returned? This could be a column, row or even a range – Meaning we can return more than one cell per match. Note that unlike VLOOKUP, and similar to INDEX MATCH – return_array can be before the lookup. -- - [if_not_found] – optional argument – What to return in case the value is not found. We can type a value, text (for example: “Value not found!” or even use a cell reference/function. --- class: center middle ### Data Presentations --- **Charts** allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and trends. There are many different types of charts that you can create in Excel. In Excel, you can create line charts, bar charts, pie charts, scatter plots, and more. .pull-center[  ] --- class: center middle ### Working with Data --- The **Pivot Table** stands out as an invaluable tool for data summarization and analysis within Microsoft Excel. This feature proves exceptionally handy in swiftly condensing extensive datasets, enabling diverse analytical perspectives. Pivot tables prove particularly beneficial for scrutinizing data derived from databases, spreadsheets, and various other sources. To initiate the creation of a pivot table, start by selecting any cell within the data table. Navigate to the "Insert" tab and click on "PivotTable." A "PivotTable" window will appear, prompting you to choose the data range you wish to analyze; subsequently, click "OK." This action will unveil the PivotTable Fields window, providing the means to construct your pivot table. **Pivot Tables** facilitate the efficient generation of reports, charts, and dashboards without the need for intricate formulas. They empower users to perform calculations on data based on specified criteria, such as calculating revenue per outstation or violations per month. This streamlined functionality makes Pivot Tables an indispensable asset for data manipulation and presentation in Excel. In summary pivot table can >- summarize and analyze large amounts of data. >- be used to create multiple charts and reports at the same time. >- be very flexible, allowing you to change the layout and pivot the data as needed. >- be easy to use, even for users with limited experience in Excel. --- class: center middle # Meet the Instructor 😊 --- ## Instructor Profile .pull-left[ ## YUSUF Akintunde Azeez Bio: An experienced Statistician with over eighteen (18) years’ experience analyzing and interpreting complex data. Practical knowledge in using statistical tools and methodologies; adept in conducting sampling and validation; Broad understanding of statistical concepts, methods and models and expert in using computer applications. Possess commercial awareness and excellent presentation, verbal communication and organizational skills with focus on constantly evaluating and recommending processes and procedures to achieve organizational corporate vision. ] .pull-right[  ] --- class: center, middle # Thanks! I am really honored to take you through this journey **Excel Mastery Training** and acquiring the skills to perform data analysis.