Microsoft Excel Full Course

The purpose of this chapter is to display common code that might give you interesting ideas. The code in this chapter can be used and adjusted to fit your own environment. Also, there is a way to create a library of code that you can commonly use. You would place all your code in a single Excel file and use it as an add-in program. The code will then be made available to you as you open Excel. Video Series Part 41- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch11.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 11 - Custom Programming Techniques The purpose of this chapter is to display common code that might give you interesting ideas. The code in this chapter can be used and adjusted to fit your own environment. Also, there is a way to create a library of code that you can commonly use. You would place all your code in a single Excel file and use it as an add-in program. The code will then be made available to you as you open Excel. Section 1 - Custom Function Library This is an easier way to make custom functions available at all times by storing them in a separate Workbook. The Workbook can then be saved as an Add-In (an XLA file) in your XLStart folder. Section 2 - Custom Functions One value of a workbook is to be able to understand the code. If you have already studied this workbook up to this particular chapter, you will notice many commands you have previously used. The following are a few functions that are available to review and use: User Defined Function, Select Row Range Example, Select Col Range Example, Outlook Email Example, Copy, Display Error Message, OnError3(), Clear a Column, Truncate Zipcode, and ZIPShorter(). This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this section, we will cover how to build functions to be used in Excel. These Built-In functions can be created using Visual Basic code and will appear in the Fx in the formula bar under the custom category. We will also show how to create a Call statement inside a Visual Basic program to perform common calculations and return to the main program. You can also pass a variable to the Call routine. There are many functions available in Excel and many of these functions are supported in Visual Basic. Also, there is a way to run a Visual Basic program when Excel opens. This may be useful to perform certain setup activities. Video Series Part 40- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch10.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 10 - Functions In this section, we will cover how to build functions to be used in Excel. These Built-In functions can be created using Visual Basic code and will appear in the Fx in the formula bar under the custom category. We will also show how to create a Call statement inside a Visual Basic program to perform common calculations and return to the main program. You can also pass a variable to the Call routine. There are many functions available in Excel and many of these functions are supported in Visual Basic. Also, there is a way to run a Visual Basic program when Excel opens. This may be useful to perform certain setup activities. Section 1 - Excel Functions In this section, we will cover how to build functions to be used in Excel. These Built-In functions can be created using Visual Basic code and will appear in the Fx in the formula bar under the custom category. Section 2 - Call - Calls Another Subroutine Here, we will show you how to create a Call statement inside a Visual Basic program. This will allow you to perform some common calculations and then return to the program. You can also pass a variable to the Call routine. Section 3 - Using Excel Formulas In VB Code There are many functions available in Excel and many of these functions are supported in Visual Basic. The following standard functions will be covered: SUM, HasFormula, Date, Len, Math.Rnd, Left, Mid, and Right Function. Section 4 - Run On Open There is a way to run a Visual Basic program when Excel opens. However, it may be useful to do certain preliminary setup activities when opened such as asking for a password, displaying a start-up popup window or creating additional worksheets. The security settings set up by your IT department may prevent you from starting the VB Code automatically. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

Loops will provide the ability to repeat operations in order to perform necessary repetitive tasks. This will allow you to search for information on a sheet, identify what is present, and evaluate data in order to make a decision. The FOR Loop will run for a specific set of times and you must determine upfront the number of cells before manipulating them. The DO Loop is better suited for cell manipulation because you can loop until you find a blank record. Video Series Part 38- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch8.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 8 - Loop Control Structures Loops will provide the ability to repeat operations in order to perform necessary repetitive tasks. This will allow you to search for information on a sheet, identify what is present, and evaluate data in order to make a decision. The FOR Loop will run for a specific set of times and you must determine upfront the number of cells before manipulating them. The DO Loop is better suited for cell manipulation because you can loop until you find a blank record. Section 1 - For Loops The FOR Loop is used for looping through an explicit number, counting until it reaches the end. In order to loop through cells or an Array you will need to extract the exact number of intervals desired. Once you have the counter number, you can loop through it either forward, backward, or skip every other number. The following concepts will be covered: For Next Loop, Loop Counter, Debug.Print, Step by 2, Count Backward, and Looping Over. Section 2 - Do Loops DO Loops are probably the best loop style when dealing with cell manipulation. The looping will be open-ended until it meets a specific condition. You can place the Until or While parameter either after the DO statement or at the ending statement LOOP. An Exit DO statement can be placed anywhere in the middle of the DO Loop to break or exit out at any time. The following concepts will be covered: Do Loop, Do While Loop, and Do Until. Section 3 - Do While You can use a DO While Loop to process the cells while a certain condition is present. However, when a condition changes it will stop. An Exit DO statement can be placed anywhere in the middle of the DO Loop to break or exit out at any time. The following concepts will be covered: Do While 1, Do While 2, and Do Exit Do. Section 4 - While Wend Loops A While Wend Loop structure is similar to a DO Loop. When the condition is True, the Macro Loops. When the condition is False, the Loop stops. This technique is not often used, however, it is important to know the format of this function in case it is in the existing code. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

It is important to manipulate worksheets (located on the bottom of the Excel screen) in order to name them, insert new ones, move, copy, and reorganize. Using Visual Basic to do this can be done in a single command. This can also be performed on Workbooks which will allow you to name a file, create new workbooks, move, copy, and reorganize them. You will learn syntax to manipulate Worksheets, add additional Worksheets, and place data across Worksheets. Video Series Part 39- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch9.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 9 - Worksheet And Workbook Manipulation It is important to manipulate worksheets (located on the bottom of the Excel screen) in order to name them, insert new ones, move, copy, and reorganize. Using Visual Basic to do this can be done in a single command. This can also be performed on Workbooks which will allow you to name a file, create new workbooks, move, copy, and reorganize them. You will learn syntax to manipulate Worksheets, add additional Worksheets, and place data across Worksheets. Section 1 - Worksheets It is important to manipulate Worksheets (located on the bottom of the Excel screen) in order to name them, insert new ones, move, copy, and reorganize. Using Visual Basic to do this can be done in a single command. There are multiple ways to identify a sheet. It can be identified by the name, physical order, or visual order. Once identified, the Activate command can select it, and once selected, additional operations can be performed. There are commands to extract the total sheets available, and Sheets can be Hidden and made Visible. One unique command is the Very Hidden parameter which will hide a sheet. It can’t be unhidden unless you use a Visual Basic program. The following sheet manipulation commands will be covered: Visible Sheet Name, Order Created, Index Number, Activate, Select, Select 2 Worksheets, Name Property, ActiveSheet Object, Add Default, Add Worksheet, Delete Worksheet, Extract Sheet Name, Rename Worksheet, Count Property, Move Worksheets, Copy Worksheets, Paste Method, Hide Worksheets, Unhide A Worksheet, Very Hidden, Protect All Sheets, Unprotect All, and Add Password. Section 2 - Workbooks This section will allow you to name, create, move, copy, and reorganize Workbooks. You can also define an active Workbook that is open if you have more than one open. Also, you can protect a Workbook and close all open files except the active one. The following concepts will be covered: Workbooks, Activate, Save As, Copy Method, List Workbook Names, Protect Sheets, and Close All Workbooks. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, you will learn how to build a Message Box, Input Box, User Form, and Application Input Box. The Message Box can be used at the end of a group of codes to provide status or a particular value at the time. It can also be used as a Yes/No prompt to ask a question. The Input Box can be used to gather information or ask a question that requires a written response. The Application Input Box can be used to provide an answer by selecting a cell on the sheet. The User Input Form can be used to gather multiple inputs on a single screen. Results can then be processed and placed in a worksheet. Video Series Part 37- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch7.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 7 - Message And Input Boxes In this chapter, you will learn how to build a Message Box, Input Box, User Form, and Application Input Box. The Message Box can be used at the end of a group of codes to provide status or a particular value at the time. It can also be used as a Yes/No prompt to ask a question. The Input Box can be used to gather information or ask a question that requires a written response. The Application Input Box can be used to provide an answer by selecting a cell on the sheet. The User Input Form can be used to gather multiple inputs on a single screen. Results can then be processed and placed in a worksheet. Section 1 - MsgBox The Message Box is a small popup dialog box that can be used to pull information from a sheet, provide status prompt for Yes/No, or other possible promptings. It could consist of a message and at least one button for the user to click. Depending on the response, a routine could jump over the specific code. Graphic status symbols can be displayed showing a red circle for error conditions or a yellow triangle showing a warning symbol. The following concepts will be covered: MsgBox Function, Message Box, Option Name, Default Option, Title, Default Message Box, Information Button, Red Circle Button, Question, Warning, Symbol Button, Exclamation Button, Add Return Lines, vbCRLF, vbNewLine, Carriage Return Character, Chr(13), Line Feed Character, vbCRLF, Line Continuation, Input A Cell, Yes/No, Variable Assignment, Stacking, MsgBox Help, Button Type, vbOKOnly, vbOKCancel, vbAbortRetryIgnore, vbYesNoCancel, vbYesNo, vbRetryCancel, Message Icons, vbCritical, vbQuestion, vbExclamation, vbInformation, Mobility, and Text Position. Section 2 - Input Box The Input Box can be used to gather information or ask a question that requires a written response. There are a variety of parameters that can be used to define the Input Box, but the simplest method is simply Input Box and then specifying the Message in parentheses. This will open an Input Box and display the question. In this section, we will cover the following concepts: Input Box, Three Parameters, X Value, Y Value, value, ActiveCell, Limitation, and Date data types. Section 3 - Application.InputBox Method The Application method works like an Input Box. However, now you will be able to interact with the spreadsheet (select a cell when the Message Box is open) and select a cell reference as the answer. The following parameters will be available: Type:=0 is used for formulas, Type:=1 is used for Numbers, Type:=2 is used for Strings, Type:=4 is used for True/False, and Type:=8 is used for Cell References. Section 4 - User Form The User Input Form can be used to gather multiple inputs on a single screen. The results can then be processed and placed on a sheet at a desired location. The Toolbox contains many tools to add form related fields such as labels, text boxes, and dropdown fields. The command button can be used to execute or initiate the processing of the form which can then store information collected to a specific cell. A code can be designed to store collected information at the end of a database of information. The following concepts will be covered: User Form, Form Storage, Toolbox, Label, TextBox, Properties, Run, Command Button, and Show Model. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

"If" statements are used to define logical true/false operations of a statement requiring several possible outcomes. If the input condition is true, it will perform a specific result and if the condition is false, it will perform a different result. The input to the IF statement uses conditional statements such as Greater Than or Less Than. It will then make the necessary true/false decisions. This automated decision process will reduce human errors and increase overall accuracy. Another technique to perform many decisions in a single command are the Select Case statements which will also be covered. In this chapter, you will learn about "If" structure, Math operators, Comparison operators, Logical operators, and Select Case statements. Video Series Part 35- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch6a.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 6 - "If" Statements "If" statements are used to define logical true/false operations of a statement requiring several possible outcomes. If the input condition is true, it will perform a specific result and if the condition is false, it will perform a different result. The input to the IF statement uses conditional statements such as Greater Than or Less Than. It will then make the necessary true/false decisions. This automated decision process will reduce human errors and increase overall accuracy. Another technique to perform many decisions in a single command are the Select Case statements which will also be covered. In this chapter, you will learn about "If" structure, Math operators, Comparison operators, Logical operators, and Select Case statements. Section 1 - If Statements This section will review IF statements to make decisions that may require two or more resulting answers. The logical parameters used include Greater Than, Less Than, Greater Than or Equal To, Less Than or Equal To, Equal To, and Not Equal To. We can also use this mathematical operation to adjust a result such as Multiplication, Division, Addition, Subtraction, Exponentiation, or String Concatenation. The AND operator will only be true if all conditions are true and the OR will be true if any of the conditions are true. IF statements can be nested within an IF statement to evaluate many possible results. The code structure will always start with the word IF and end with END IF. The GOTO command is used to break or jump out of the middle of an operation under certain conditions. The following concepts will be covered: Operators, Assignment, Arithmetic Operators, Comparison Operators, Logical Operators, "IF" Structure, Nested IF, End IF Goto, and Else In. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This video will review the most common formatting techniques used to manipulate cell data. The standard Font, Fill and Borders will be covered, but other techniques will be discussed, as well such as Offsetting Cells, as well as Concatenate Text Strings, and "With" statements. Video Series Part 33- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch4.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 4 - Formatting Techniques This chapter will review the most common formatting techniques used to manipulate cell data. The standard Font, Fill and Borders will be covered, but other techniques will be discussed, as well such as Offsetting Cells, as well as Concatenate Text Strings, and "With" statements. Section 1 - Formatting The Font, Fill (Interior) and Paragraph properties are the most commonly used to format values in a cell. Some of the more common sub-commands are Font.Color, Wraptext, Orientation, and Merge cells. The Border command will need to be applied to each side of the cell in order to format all borders. You also can format numbers with decimal points. Currency and Percentage format techniques will also be reviewed. Lastly, Date formatting techniques are introduced. The following concepts will be covered: Font, Font.Color, Fill (Interior), Paragraph formatting, Border, NumberFormat, Currency, Percentage, Date, AutoFit, Concatenation. Section 2 - With Statements The With statement will allow you to nest formatting capabilities by starting with a With statement and providing the initial formatting capabilities such as the Font command. All values under the With statement will list the sub-commands such as Color, Bold, etc. This can simplify and reduce the amount of code listed. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

Variables are the basic building blocks in any programming language and will provide a way to store information to be used later in a routine. You will learn the Variable structure, math operations, data types, and various problems that may be encountered. Variables can be declared in routine, in the module, or the variable storage can be accessed from a different module. Arrays are a way to store multiple values in a single register and this chapter will review ways to extract these values. Video Series Part 34- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch5.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 5 - Variables Variables are the basic building blocks in any programming language and will provide a way to store information to be used later in a routine. You will learn the Variable structure, math operations, data types, and various problems that may be encountered. Variables can be declared in routine, in the module, or the variable storage can be accessed from a different module. Arrays are a way to store multiple values in a single register and this chapter will review ways to extract these values. Section 1 - Variable Structure These are temporary storage locations for information to be used at a later time. The data will remain in a storage location until new data overrides current information. Each Variable will be declared using a dimension statement that follows the form of “Dim i As Integer.” The Variable name will be declared using a specific Variable type, once the defined text and numeric values can be assigned to a storage location. The following concepts will be covered: Variables, Variable Name Rules, Dimension, Set Object Variable, Datatypes, Text, Numbers, Date, Boolean, Byte, Integer, Long, Decimal, String, Currency, Datatype, and Assignments. Section 2 - Global Declarations Global Declarations are defined on top of the Module and can be used for multiple subroutines. The following Global Declarations will be covered: Public Variable, Private Variable, Private Sub, Const Variable, Static Variable, Parenthesis Around An Option, Declare Above, Global Scope, Public, and Private. Section 3 - VB Arrays Arrays are a collection of data that have the same data type (called the base type). They are grouped into a single Array Variable, with a single Array name. This section will review the different techniques used to extract information out of an Array. The following concepts will be covered: Declaring Array, Split Function, Join Function, Redim, Preserve, LBound, UBound, and IsArray. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This video is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Aplying the Offset and xlDown properties will allow the system to find a specific group of data. Video Series Part 32- This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch3-sec3.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 3 - Cell Manipulation This chapter is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Applying the Offset and xlDown properties will allow the system to find a specific group of data. Section 3 - Column And Row Techniques Often, you may want to manipulate rows or columns as a group in order to Delete, Clear, Cut/Copy/Paste, or use the Paste Special capabilities. Some of these commands can be used for specific operations such as in the Delete process. Therefore, it is important to understand the correct command sequence. The following commands will be covered: Columns/Rows Object, Column/Row Property, Select Columns/Row, EntireRow, and EntireColumn. Section 4 - Cell Offset, xlDown, CurrentRegion, And Address. This is the concluding set of commands that will start at a specific cell and move or offset to a different location based on a set of criteria. You might want to move to the end of the database or move to the next column. The Select Sheet command is unique in that it will only work with a specific set of defined commands. The following concepts will be covered: Offset Property, xlDown, CurrentRegion, Address Property, Text Property, and Select Sheet. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This video is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Applying the Offset and xlDown properties will allow the system to find a specific group of data. Video Series Part 31 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch3-sec2.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 3 - Cell Manipulation This chapter is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Applying the Offset and xlDown properties will allow the system to find a specific group of data. Section 2 - Cell Manipulation Once a Cell or Cells is identified, you can Cut, Copy, or Paste data to a new location. The Paste Special command can be used to Paste values, columns, format, comments, validation, skip blanks, or transpose data. You may want to simply Clear out selected cells or Delete the physical cell to collapse related cells. Of course, one of the important things to do is to update, change, or add text or numeric values to selected cells. Concepts to be covered include ActiveCell Object, Select/Selection Property, Value Property, FormulaR1C1, Property, Cut Property, Copy Property, Paste Property, PasteSpecial Method, Delete Method, and Clear Method. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This video is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Aplying the Offset and xlDown properties will allow the system to find a specific group of data. Video Series Part 30 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch3-sec1.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 3 - Cell Manipulation This chapter is probably the most important and contains the most used features of Visual Basic. In order to manipulate a cell, you need to identify its position using the Range command. Then, you can apply an operation to manipulate the cells. This operation is used in all code at some point. Furthermore, in order to manipulate a cell, you can use options such as Cut/Copy/Paste, Paste Special, update the value of the cell, delete it, or clear it out. You can also manipulate data by row or column using various commands. Aplying the Offset and xlDown properties will allow the system to find a specific group of data. Section 1 - Cell Identification In order to manipulate a cell, you need to identify the position of the cell to be manipulated. This section will review the most common commands used to identify a Cell, select a Cell, and the manipulate data. The Range command is used to identify a cell or cells. Once identified, the Select command will highlight the cells. After cell selection , they can be formatted, changed, and calculations can be applied. Cells are identified is by using a letter and number position (such as A1:D3). The cell command will identify the position using a column number and row number. However, a more advanced technique would be to use a range name. the following concepts will be covered: Range Object, Range Single Select 1, Range Select Range, Range Select Range, Cells Option, Range Select 5, and Range Name. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This video will focus on Visual Basic Editor in order to change and debug a code. You can review a created code, make minor changes to misspelled words, and adjust starting cells and ranges as needed. One valuable feature is the Debug Tool which allows you to step through a code line by line to ensure it is accomplishing your objectives. Also, if a code produces an error, it can be identified or isolated. In this chapter, we will gain a better understanding of the VB Editor interface, modules, components of the code, debugging, as well as running the code. Video Series Part 29 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch2.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 2 - Visual Basic Editor This chapter will focus on Visual Basic Editor in order to change and debug a code. You can review a created code, make minor changes to misspelled words, and adjust starting cells and ranges as needed. One valuable feature is the Debug Tool which allows you to step through a code line by line to ensure it is accomplishing your objectives. Also, if a code produces an error, it can be identified or isolated. In this chapter, we will gain a better understanding of the VB Editor interface, modules, components of the code, debugging, as well as running the code. Section 1 - Interface The Visual Basic Editor is used to edit a Macro and code. We will review the VB Basic Editor interface to learn how to navigate and add various modules. We will also explain where the code is stored and how to display the code screen. On the Excel interface, we will add the Developer Ribbon Tab and explain the best way to switch between Excel and Visual Basic. The following concepts will be covered: Switch To Excel, Minimize VB, Editor, Project Explorer, Personal Macro Project, Workbook Projects, Modules Folder, Create A Module, Properties Windows, Rename Module, Developer Tab, and Help screen. Section 2 - Coding Components In this section, we will explain the Visual Basic coding structure. Every subroutine begins with the word Sub and ends with the word End Sub. The executed code will be listed between the structure. We will also cover the subroutine name standards and how to work around reserved names. The Macro description is very important in order to document its purpose and how it can be used. We will also cover how to add comments and the order of the Visual Basic commands such as Object.Property Method. Section 3 - Debugging And Running Code The run time of a Macro is very short compared to the time it takes to do the work manually. When you run the code in the Debug window, other windows can be displayed to watch results of the code including Locals Window and Immediate Window. The following concepts will be covered: Debug Toolbar, Debug Step-Into, Reset, Run, Locals Window, Immediate Window, and Option Explicit. Section 4 - Optional Topics The Options Explicit can be added to the top of a Code Module screen to alert you concerning misspelled methods and undeclared variables. This will help to resolve problems during the development process. We will also discuss a way to have Open Explicit added automatically to the top of a new module. There are other advanced topics to help with the Debug process in order to catch errors and speed up the development process. The following concepts will be covered: Watch Window, Auto Add Option Explicit, Debug Step-Over, Debug Step-Out, Debug Run To Cursor, BreakPoint, Run Time Error, VB Module Font, Find Subroutine, Turn Off Popup, Protect VB Code, IntelliSense, and IntelliSense Symbols. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Video Series Part 27 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch1-sec1.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 1 - Excel Macros In this chapter, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Section 2 - Running Macros In this section, the student will learn how to run Macros (using assign Macros to Form Buttons, Shape Buttons, or graphic objects), Quick Access toolbar, Customized Ribbons, keyboard commands, and Macros dialog box. Specific concepts to be covered are: Shape Buttons, Picture Buttons, Quick Access Toolbar (QAT), Customized Ribbon, Form Buttons, and review the Unassigned Macros Keys. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Video Series Part 28 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch1-sec2.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 1 - Excel Macros In this chapter, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Section 3 - Macro Examples It is important to get experience with creating Macros. This section will cover the development of the following: Macros Open File, Zoom, Format Sheet, Print Setup, Use ActiveSheet Name, Sort Macro, and User Input Form. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Video Series Part 26 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-5-ch1-sec1.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 – Visual Basic For Excel The courseware is available on Amazon at: https://www.amazon.com/dp/1979929076 Chapter 1 - Excel Macros In this chapter, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with the proper security, determine the difference between Absolute and Relative, learn debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understand Visual Basic code. The code is written in Visual Basic and we will review the code in order to make a few changes if necessary. We will run through many different examples to show the power in your control! Overall, it is not difficult to record a Macro; in fact, any novice student can do it. This will increase your overall productivity by automating routine tasks. Section 1 - Macro Introduction A Macro is a recorded program. When you press record, it will remember every command you designate and will be stored in a Visual Basic program. Once a Macro is created, it can be executed to perform several actions. Students will learn how to write a Macro, open a Macro with proper security, and determine the difference between Absolute and Relative. The specific concepts to be covered include Macro Creation, Macro Name, Assigning a Shortcut Key, Assign This Workbook, assign Personal Macro Workbook, Developers Tab, Security Level, Absolute Macro, and Relative Macro. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

Power Pivot is a technique used to create relationships between sheets in Excel similar to a relational database. Once you convert records located in different sheets to tables, you will then be able to analyze the data in a way that cannot be done in a traditional PivotTable in Excel. Power Pivot is an Add-In tool which means it must be added using the Add-Ins feature in the Excel Options menu. The following topics will be covered: Create Table, Add To Model, Define Relationships, Measures, DAX program language, Calculated Fields, and Key Performance Indicators (KPI). Video Series Part 23 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch8.mp4 - Click +Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 8 - Power Pivot Power Pivot is a technique used to create relationships between sheets in Excel similar to a relational database. Once you convert records located in different sheets to tables, you will then be able to analyze the data in a way that cannot be done in a traditional PivotTable in Excel. Power Pivot is an Add-In tool which means it must be added using the Add-Ins feature in the Excel Options menu. The following topics will be covered: Create Table, Add To Model, Define Relationships, Measures, DAX program language, Calculated Fields, and Key Performance Indicators (KPI). Section 1: Power Pivot Installation This section will cover methods to install or activate Power Pivot capabilities. There are different procedures for different Excel releases. Before Excel 2016, you had to install a Microsoft download and use the Excel Add-In feature located in Excel Options. Section 2: Data Table This will establish a Data Table to be used as input to Power Pivot and PivotTables. The concepts to be covered include: Data Table, Refresh New Fields, and Smart Rename. Section 3: Autodetect And Manual Relationships In this section, we will explain the difference between Autodetect Relationships (an Excel 2016 feature) and Manual Relationships (an Excel 2010/2013 feature). We will use the Data Model feature to add the Table to the PowerPivot interface in order to build the relationships between tables. We will also cover Manual Relationships and Autodetect Relationships. Section 4: Power Pivot More Tables This is a different technique that allows the Tables to be Related without predefining the relationships. It will autodetect the possible relationships and connect them up. Section 5: Power Pivot Window This will cover the “Add to Data Model” technique used to build Relationships between Tables. This is the most accurate and reliable technique to build Relationships. If the other techniques (mentioned above) fail, this can be used to fix any problem encountered. The concepts covered include: Power Pivot Window, Add to Data Model, Create Relationships, Relationship Screen, Manage Relationships, Diagram View, Create PivotTable, Rename Tables, Office Repair, and Object Reference Error. Section 6: Power Pivot Ribbon Tab This section will cover the Icons located in the Excel Power Pivot Ribbon Tab. The primary focus of DAX formulas is to build Measures and use them to build KPIs. DAX is a programming language used to build calculated fields or formulas. KPI uses the DAX result to display indicators or buttons when a record has alert condition. The following concepts will be covered: New Measure, Manage Measures, KPIs, Add to Data, Model, Update All, Detect, and Settings. Section 7: Power Pivot Window (Home Ribbon Tab) This section will cover the Home Ribbon Tab in the “Power Pivot for Excel” window. The concepts to be covered are Clipboard, Refresh, Formatting Group, Formatting Tools, Sort & Filter, Find, Calculations, Diagram View, Show Hidden, and Calculation Area. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

These are advanced analysis tools used to perform unique analysis techniques. Some of these tools are difficult to figure out, but performing this analysis manually usually proves to be very time consuming. What-If Formulas include Scenario Manager, Goal Seek, and Data Tables. Add-Ins are additional tools that can provide a detailed analysis. Video Series Part 22 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch7.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 7 - What-If Analysis And Add-Ins These are advanced analysis tools used to perform unique analysis techniques. Some of these tools are difficult to figure out, but performing this analysis manually usually proves to be very time consuming. What-If Formulas include Scenario Manager, Goal Seek, and Data Tables. Add-Ins are additional tools that can provide a detailed analysis. Section 1: What-If Analysis Tools These are advanced analysis tools to solve unique problems. The Scenario manager will evaluate or compare several possible inputs to a spreadsheet that contains Formulas. The more data used for the Scenario, the more valuable the feature becomes. Excel Data Tables are an excellent way to analyze data in spreadsheets. Data Tables are used to track changes in formula results in a spreadsheet by changing one or two inputs in the worksheet’s formulas. Goal See is used as a reverse calculation strategy by finding possible input values based on a result or a Goal. Concepts covered include: Data Tables, One-variable Input, Two-variable Input, Scenario, What-If Analysis, Goal Seek, and Data Table. Section 2: Other Analysis Tools These are additional Analysis tools used for advanced purposes. Once you add in the Analysis Toolpak, there are a variety of tools available. They will resolve complicated solutions by specifying and entering multiple criteria. If you know a desired outcome, you can use Solver to find variables to produce a certain result. The following tools will be covered: Anova: Single Factor (ANalysis Of VArables), Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test: Paired Two Sample for Means, t-Test: Two-Sample Assuming Equal Variances, t-Test: Two-Sample Assuming Unequal Variances, t-Test: Two-Sample for Means, One-Click Forecasting, Analysis Toolpak, and Solver. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This is an interface that allows a user to adjust the amount of data coming into Excel. It is an Add-In feature in older versions of Excel but is now located in the Data Ribbon Tab as the primary input feature. This means it will allow users to link to other data sources such as: Excel, CSV, XML, Text, SQL Server, Access, Oracle, MySQL, SharePoint List, Active Directory, websites across the enterprise, and Facebook. Once the data source is linked to the Query interface, you can reduce the amount of data to be saved in Excel or add/remove fields. Also, there are many customized techniques used to adjust the data including adjusting columns(fields), rows (records), splitting columns, combine columns, replace information, transpose the entire table, change the data type, and sort columns. Video Series Part 21 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-chmp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 6 - PowerQuery (Get Data) This is an interface that allows a user to adjust the amount of data coming into Excel. It is an Add-In feature in older versions of Excel but is now located in the Data Ribbon Tab as the primary input feature. This means it will allow users to link to other data sources such as: Excel, CSV, XML, Text, SQL Server, Access, Oracle, MySQL, SharePoint List, Active Directory, websites across the enterprise, and Facebook. Once the data source is linked to the Query interface, you can reduce the amount of data to be saved in Excel or add/remove fields. Also, there are many customized techniques used to adjust the data including adjusting columns(fields), rows (records), splitting columns, combine columns, replace information, transpose the entire table, change the data type, and sort columns. This data source in the Query Editor is used to filter, transform, or shape information. You can return to the Query Editor at any time to make additional adjustments as necessary. When finished, the resulting data can be saved to Excel, or if you are connected directly to a database data source, you can refresh the Query which will extract new or updated records that have been changed. Once the data is in Excel, it can be used as a Table structure, not a normal Excel Range. Section 1: Get Data This will Identify the data source, load the data, and adjust the data coming into Excel by applying filters to each column of data. When new data is added to the data source, the refresh button will bring in new data and apply the predefined filters to the incoming data. The following topics will be covered: PowerQuery Add-Ins, Get Data, Data Source, Load, Load To, Transform Data, Launch Query Editor, Filter, Left Pane, Center Pane, Applied Steps, and Close & Load. Section 2: Home Ribbon Tab This is the main menu to perform the most standard operations. The concepts will include: Close & Load, Refresh Preview, Properties, Advanced Editor, Manage, Choose Columns, Remove Columns, Keep Rows, Remove Rows, Sort, Split Column, By Group, Data Type, Use First Row As Headers, Replace Value, Manage Parameters, Merge Queries, Combine Append Queries, Data Source Settings, Change Source, Permissions, New Source, Recent Sources, and PowerQuery. Section 3: Transform Ribbon Tab The Transform Ribbon Tab changes or transforms data such as adding or removing columns, changing data types, splitting columns, and other data-driven tasks. The term Transform Data has to do with changing, filtering, adjusting, and shaping a data table to a new form or layout. The following buttons are in the Transform Ribbon Tab: Group By, Transpose, Reverse Rows, Count Rows, Data Type, Detect Data Type, Rename, Fill, Move, Convert To List, Split Columns, Format, Merge Columns, Extract, Parse, Statistics, Rounding, Information, Date, Time, Duration, Structured Column, Query Editor, and Transform Ribbon Tab. Section 4: Add Column Ribbon Tab This is primarily used to manipulate columns in a data source. Some of these options are available in other ribbons, however, these are the main column tools. The following topics will be covered: Column From Examples, Custom Column, Conditional Column, Index Column, and Duplicate Column. Section 5: View Ribbon Tab The View Ribbon Tab on the Query Editor interface is used to toggle whether certain panes or windows are displayed. It is also used to display the Advanced Editor. The following concepts will be covered: Query Settings, Formula Bar, Show Whitespace, Go To Column, Always Allow, Advanced Editor, Query Dependencies, and Query Editor. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, will use multiple sheets or Workbooks to produce a result. We will cover ways to add up data across worksheets or workbooks as well as external linking to other workbooks. Also, we will cover several techniques to import and export data to Excel. Video Series Part 20 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch5.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 5 - Working With Files In this chapter, will use multiple sheets or Workbooks to produce a result. We will cover ways to add up data across worksheets or workbooks as well as external linking to other workbooks. Also, we will cover several techniques to import and export data to Excel. Section 1: Manipulating Files There are two major concepts to be covered in this section. The first is Consolidate Data which is a way to add up data across worksheets. Here, the data labels are in different positions and each sheet is sorted in a different order. This feature uses labels to identify exact data values. The other major concept is linking data between worksheets and workbooks. The specific topics to be covered include: Consolidate Data, External Reference Links, Edit Links, and Create a Workspace. Section 2: Import Into Excel 365 This section is designed to Import or bring information into Excel for the manipulation of data. Generally, all data is imported by using the file open technique which is the way it worked in Excel 2010. We will also cover several other techniques in the Data Ribbon Tab such as From Text/CSV, From Web, From Table/Range, Recent Sources, Existing Connections, Stock Data Type, and Geography Data Type. The next chapter will cover another technique to Import data called Power Query (Get Data option) which establishes a link to the data source. This will allow a Refresh to Automatically Import updated information in a data source. Section 3: Export From Excel This will cover Export features using the file save as in the interface. The following data types will be covered: Excel Workbook (*.xlsx), Excel Macro-Enabled Workbook (*.xlsm), Excel Binary Workbook (*.xlsb), Excel 97-2003 Workbook (*.xls), CSV UTF-8 (Comma delimited) (*.csv), XML Data (*.xml is the Extensible Markup Language), Single File Web Page (*.mht* is the Mime HTML, Web Page (*.htm, *.html), Excel Template (*.xltx), Excel Macro-Enabled Template (*.xltm), Excel 97-2003 Template (*.xlt), Text (Tab delimited) (*.txt), Unicode Text (*.txt), XML Spreadsheet 2003 (*.xml), Microsoft Excel 5.0/95 Workbook (*.xls), CSV (Comma delimited) (*.csv), Formatted Text (Space delimited) (*.prn), Text (Macintosh) (*.txt), Text (MD-DOS) (*.txt), CSV (Macintosh) (*.csv), CSV (MS-DOS) (*.csv), DIF (Data Interchange Format) (*.dif), SYLK (Symbolic Link) (*.slk), Excel Add-Ins (*.xlam), Excel 97-2003 Add-Ins (*.xla), PDF (*.pdf), Strict Open XML Spreadsheet (*.xlsx), and OpenDocument Spreadsheet (*.ods). This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch4.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 4 - Formula Auditing And Advanced Formulas This chapter will cover a wide range of formula error-checking capabilities, as well as Array and Iteration Formulas. Error checking techniques will troubleshoot problems, errors, and formulas that produce wrong answers. Array and Iteration Formulas contain advanced functionality beyond the normal use of Excel functions. Section 1: Formula Auditing The Formula Auditing Group provides tools for students to examine the association between cells and Formulas in a worksheet. The following concepts will be covered: Trace Precedents, Remove Arrows, Trace Dependents, Show Formulas, Error Checking, Trace Error, Circular Reference, Evaluate Formula, Watch and Evaluate Formulas, Watch Name, Calculate Now, Calculate Sheet, Calculate Options, and Smart Tag Error. Section 2: Array Formulas An Array formula is unique in that it provides different levels of functionality. Usually, to define an Array as an Array formula, press Ctrl Shift Enter after the formula is entered. When you enter data in several cells and define it as an array, all data entered will be managed as an Array of cells. This means that you can’t delete one value without deleting the other values in an array. Also, you can create formulas that will multiply two columns of data as an array which can’t be accomplished using standard Excel formulas. The specific concepts covered are: Array Formulas, Transpose Array, Multi-Cell Array Formula, Single-Cell Array, Constant Array, Two-Dimensional Constant Array, Constants in Formulas, and Array Range Name. Section 3: Iterative Formulas An Iterative Formula is unique in that it provides different levels of functionality. It determines a result by iterating or providing successive attempts to arrive at an approximate answer. For example, it may add a small value (such as .01) multiple times to obtain an answer. For example, I apply a 10% growth factor to my business, I will make $200,000. What will my growth factor need to be if I wanted to make $1,000,000? The Iterative formula will add .01 to the growth factor until the $1,000,000 goal is reached. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, you will learn how to automate routine tasks at the push of a button. It will cover how to write a Macro, open a Macro with proper security, determine the difference between Absolute and Relative, display debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and how to understand Visual Basic code. We will review the code in order to make a few changes if necessary. We will also run through many different examples to show the power and control of these features. Overall, it is not difficult to record a Macro. Any novice student will be able to do this. It will increase your overall productivity by automating routine tasks. Video Series Part 18 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch3.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 3 - Excel Macros In this chapter, you will learn how to automate routine tasks at the push of a button. You can review the code created and make minor changes to misspelled words, adjust starting cells and ranges as needed. It will cover how to write a Macro, open a Macro with proper security, determine the difference between Absolute and Relative, display debugging techniques, assign Macros to Form Buttons, Shape Buttons, Graphic Objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and how to understand Visual Basic code. We will review the code in order to make a few changes if necessary. We will also run through many different examples to show the power and control of these features. Overall, it is not difficult to record a Macro. Any novice student will be able to do this. It will increase your overall productivity by automating routine tasks. Section 1: Macro Introduction In this section, we will introduce the creation and use of Macros. We will use the View Macros button and introduce you to the Developers Ribbon Tab. Defining security levels will protect you by verifying and warning when a file is opened containing a Macro. We will also introduce various elements to use when creating a Macro such as Absolute, Relative, Personal Macro workbook, and recording in an active file. Section 2: Running Macros The run time of a Macro is very shore compared to the time it takes to do the work manually. Here, we will cover different ways to begin a Macro including Keyboard Commands, Shape Buttons, Picture Buttons, Quick Access Toolbar (QAT), and creating a Customized Ribbon. Section 3: Visual Basic Editor Macros are recorded in Visual Basic code. You can review the created code created and make minor changes in the Visual Basic Editor. When a Macro crashes due to an illegal operation, the debug feature will help determine possible solutions. It will allow you to run a Macro and watch results line by line. Once a problem area is identified, necessary adjustments can be made. The following concepts will be covered: VB Editor, Switch To Excel, Minimize VB Editor, Project Explorer, Personal Macro Project, Workbook Projects, Modules Folder, Create A Module, Properties Windows, Rename Module, Debug Toolbar, Debug Step-Into, Rest and Run. Section 4: Macro Examples It is important to obtain some experience in creating Macros. This section will cover the development of the following: Open File Macro, Zoom Macro, Macros Format Sheet, Print Setup, Use ActiveSheet Name, Sort Macro, and User Input Form. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, students will learn how to format cells based on specific conditions as well as validate data. The Conditional Format features will allow you to format cells based on the condition or value of data in the cell. Data Validation will also identify a specific range of data in the cell, but it will stop you from entering that data if it is invalid. Video Series Part 17 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch2.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 2 - Conditional Formatting And Data Validation In this chapter, students will learn how to format cells based on specific conditions as well as validate data. The Conditional Format features will allow you to format cells based on the condition or value of data in the cell. Data Validation will also identify a specific range of data in the cell, but it will stop you from entering that data if it is invalid. Section 1: Conditional Format This will format numbers as well as text based on a Logical Condition. It will either highlight the cells or change the font color if conditions are met. It will also place a bar chart in the cell behind the text indicating the largest and smallest numbers. Icon sets will place objects in a cell such as a star, moon, data bars, and many other indicators to recognize values and provide a rating. The following concepts will be covered: Highlight Cell Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, New Rule, New Formula Rule, Alternating Rows, and Manage Rules. Section 2: Data Validation This will prevent, or only allow/disallow specific values to be entered in a cell and will provide a dropdown list of valid input criteria. When you type data in a cell, this Data Validation feature will either stop you from entering data or warn you that the value is out of range. The dropdown list will reduce typing errors by providing an exact list of options to enter. There are other techniques to prevent data entry errors using Date, Text, or custom capabilities. The following concepts will be covered: Data Validation Settings, Data Validation Input Message, Data Validation Error Alert, Data Validation List, Duplicate Records, Data Validation List Range Name, Custom Validation, Circle Invalid Data, Clear Validation Circles, Text Length, Date Range, Select Data Validation, and Custom Data Validation. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, we will cover how to protect cells and files. There are two steps in the process of protecting cells that includes the cell status (protected or unprotected) and the protection of an entire sheet or workbook. We will also cover how to hide formulas in a protected cell and define a password protected area on the worksheet. Also, File Protection will prevent a person from opening a file if they do not have a password. We will also save a file in read-only as well as draft mode to prevent unauthorized users from making changes. Video Series Part 16 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-3-ch1.mp4 - Click +Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934 Chapter 1 - Protection In this chapter, we will cover how to protect cells and files. There are two steps in the process of protecting cells that includes the cell status (protected or unprotected) and the protection of an entire sheet or workbook. We will also cover how to hide formulas in a protected cell and define a password protected area on the worksheet. Also, File Protection will prevent a person from opening a file if they do not have a password. We will also save a file in read-only as well as draft mode to prevent unauthorized users from making changes. Section 1: Cell Protection Individual cells can be protected to prevent the accidental deletion of formulas and other valuable data areas. First, select the cells to be adjusted, define the status of the cell (locked or unlocked, in other words, unprotected or protected), then protect the entire sheet or workbook. These concepts will all be covered including how to password protect different areas on a worksheet to be managed by different users. The following concepts will also be covered: Protect Cells, Protect Sheet, Protect Workbook, Hide Formula, Range Editing, and Select Locked Cells. Section 2: File Protection This Section covers File related Protection. An entire file can require a password to open and if the password isn’t known, it can’t be opened. Other file related protections will mark a file as read-only and will allow you to edit the file, but it can’t be saved to the original file. The following concepts will be covered: Password To Open, Password To Modify, Read-Only Recommended, Always Create A Backup, Mark As Final, Hide Sheet, Protect Sheet, and Encrypt With Password. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

PivotTables are used to summarize a large database of data into a more manageable visible layout. This will open your eyes to the true purpose of PivotTables because you will see the layout in a new light. Why should you use a PivotTable? It will allow you to identify areas of concern, reduce human error, and improve efficiency. The best time to use a PivotTable is when you need to find relationships between data. A PivotTable can also be useful if you are looking to layout your data in a unique way, you notice changing trends, or just want to organize your data for better understanding. Video Series Part 15 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-2-ch6.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables The courseware is available on Amazon at: https://www.amazon.com/dp/1660221943 Chapter 6 - PivotTable And PivotChart PivotTables are used to summarize a large database of data into a more manageable visible layout. This will open your eyes to the true purpose of PivotTables because you will see the layout in a new light. Why should you use a PivotTable? It will allow you to identify areas of concern, reduce human error, and improve efficiency. The best time to use a PivotTable is when you need to find relationships between data. A PivotTable can also be useful if you are looking to layout your data in a unique way, you notice changing trends, or just want to organize your data for better understanding. Section 1: Understanding PivotTables In this section, we will answer the basic question of "Why do you want to use a PivotTable?" Once you understand "why," you will have a deeper appreciation for its capabilities. We will take you through the process to create a Pivot Table by creating several different types. The following concepts will be covered: Internal Data Source, Row Drop Zone, Column Drop Zone, ∑ Values Drop Zone, Report Filter Drop Zone, Add Multiple Fields, Multiple PivotTables, Excel Table, External Datasource, Dedicated PivotTable Cache, Shared PivotTable Cache, Multiple Consolidation Of Ranges, Default Pivot, and Table Layout. Section 2: Filter Feature Filtering is the core part of a PivoTable which is used to adjust the amount of data within a PivotTable. This section will cover the standard filter feature and Slicer capability. The following concepts will be covered: Filter, Filter Options, Sort A to Z, More Sort Options, Clear Filter, Clear Filters, Clear All, Label Filters, Value Filters, Search, Check, Boxes, Field List Filter, Slicer, Slicer Color Codes, Multi-Select Slicer, Delete Slicer, Slicer Settings, Slicer Formatting, Slicer Report, Analytics Dashboard, Timeline, Fields Settings, Filter Section, and Filter On Worksheets. Section 3: PivotTable Formatting Once a PivotTable is created, it can be formatted using many of the standard Excel formatting tools including Conditional Formatting capabilities. The following concepts will be covered: PivotTable Styles, Style Options, Format Cells, Select, Conditional Formatting, Number Format, Merge And Center Cells, Autofit Columns, Preserve Formatting, Blank Values In Database, Empty Cells In The PivotTable, Blank Rows And Columns, Display Features, Custom Name, Convert to Values, Copy And Paste, PivotTable, and Themes. Section 4: PivotChart PivotCharts are directly tied to PivotTables and any changes made to a PivotTable will directly affect the PivotChart. Once created, the graphical layout can be formatted and adjusted using standard Chart features. Section 5: PivotTable Adjustments And Layout Once a PivotTable is created, labels can be adjusted or grouped, subtotals/total fields can be added and moved to an entirely New Sheet, PivotTable values can be copied, and DrillDowns can be added which allow you to see the details of any field. The following concepts will be covered: Grouping and Ungrouping, Collapsing And Expanding, DrillDown, Move Heading, Move PivotTable, Manual Date Grouping, Autodetect Date, Convert to Values, Subtotals, SubTotal Controls, Grand Totals, Report Layout, Blank Rows, and Compare Two Tables, Section 6: Formulas And Value Field Settings Here, both internal and external Formulas to the Pivot Table will be covered. The Value Field Setting will also be changed in order to modify the way numbers are calculated so you can explore ways to compare values. The following concepts will be covered: Order Of Operations, Calculated Item, External Formulas, Refresh Data, Table Object, Internal Formulas, Solve Order, List Formulas, Custom Name, Summarize Values By, Show Value As, Running Total, Defer Update, and GetPivotData. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com

In this video, we will begin by creating a Chart which is a fairly straightforward process. However, structuring data with the proper layout can be a challenge. For example, if you have junk in your data source, you will also have junk in your Chart. This course will cover how to clean up a data source as well as how to create a Chart. Also, Sparklines are mini Charts placed in an Excel worksheet to help clarify and describe data. We will cover the different Chart types available and the options to adjust Chart layouts. Advanced Charts will include Trendlines and Secondary Axis. We will also review some of the major changes from Excel 2010 and the newer versions of Excel. Video Series Part 14 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth. Higher Quality Video: www.elearnlogic.com/media/excel365-2-ch5.mp4 - Click + Rumble if the video was helpful. - Click Subscribe so you won’t miss out on any new videos. - Click the Share button so you can help others expand their skills. Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video. Courseware: Excel 365 - Formulas, Charts, And PivotTables Courseware is available on Amazon at: https://www.amazon.com/dp/1660221943 Chapter 5 - Charts Creating a Chart is a fairly straight forward process, but structuring data with the proper layout can be a challenge. For example, if you have junk in your data source, you will also have junk in your Chart. This course will cover how to clean up a data source as well as how to create a Chart. Also, Sparklines are mini Charts placed in an Excel worksheet to help clarify and describe data. We will cover the different Chart types available and the options to adjust Chart layouts. Advanced Charts will include Trendlines and Secondary Axis. We will also review some of the major changes from Excel 2010 and the newer versions of Excel. Section 1: Create And Modify A Chart In this section, we will begin by discussing techniques of creating a Chart from a data source. We will cover different ways to structure a data source before creating a Chart. The concepts concerning the basics of Creating A Chart and Modifying A Chart will be covered. Section 2: Design Ribbon Tab The Design Ribbon Tab is used to change the layout and colors of a Chart. It is also the main ribbon that will change the structure of the elements in a Chart. The concepts that will be covered are Chart Layouts, Quick Layout, Add Chart Element, Chart Styles, Switch Rows/Columns, Select Data, Change Chart Type, Move Chart, Color Changes, and Recommended Charts. Section 3: Format Ribbon Tab The Format Ribbon Tab is used to change or Format elements within a Chart. All formatting tools in the Home Ribbon Tab are supported including Conditional Format capabilities. The concepts that will be covered are Chart Elements, Format Selection, Reset To Match Style, Shape Styles, Shape Effects, WordArt Styles, Arrange Group, and Size. Section 4: Chart Tools Buttons This will provide options to change the layout of a Chart. Excel 2010 performed this using the Layout Ribbon Tab but it was replaced by the Chart Tools Button in Excel 2013+. This allows you to quickly pick and preview Chart elements as well as fine-tune others including Titles, Labels, or other presentable data. The concepts covered are Chart Elements, Chart Styles, and Chart Filters. Section 5: Chart Types In this section, we will show you some Chart Types that reflect information from a data source. You will want people reviewing the Charts to understand the data stored in each data source. Therefore, you must choose a Chart that fits the data in the best possible way. The following Chart types will be covered: Column Charts, Line Chart, Pie Chart, Bar Chart, Area Chart, XY Scatter, Map Chart, Stock Chart, Surface, Radar, Funnel Charts, TreeMap Chart, Sunburst Chart, Waterfall Chart, Histogram Chart, Pareto Chart, Box & Whisker Chart, and People Graph. Section 6: Advanced Charts This will cover advanced topics such as Trendline and Secondary Axis. The concepts covered include Trendline, Secondary Axis, Save As Template, and Restore Saved Template. Section 7: Sparkline Sparklines are small Charts located in a cell usually near the data. They can be used to explain the trends of the data being charted. The following concepts will be covered: SparkLine Line, Show Markers, Sparkline Column, Sparkline Win/Loss, Add Text To Sparkline, and Sparkline Styles. Section 8: Layout Ribbon Tab (Excel 2010 Only) The Charting Ribbons changed in Excel 2013, and we will cover the old Excel 2010 features in this section. Many of the features of Excel 2010 are the same as newer versions, but they have moved to different locations in the ribbons. Refer to the documentation in this chapter for a detailed explanation of these features. The following concepts will be covered: Layout Ribbon Tab, Design Ribbon Tab, Format Ribbon Tab, Save As Template, Format Ribbon Tab, Excel 2010 Design Ribbon Tab, and Secondary Axis. This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019. If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com