TSL_25_07_2024_exceltraining-190602234542.pptx

nice

1. CRITICAL CORE OF EXCEL -

2. Introduction to MICROSOFT EXCEL™

3. ROWS, COLUMNS AND REFFERENCES Address (COLUMN + ROW) – A1 COLUMNS A -> XFD (~16000) ROWS 1 -> 1048576

4. Selecting Multiple Cells/Rows/Columns

5. Cell real value Cell displayed value Cell real value vs Cell displayed value Font color option

6. THE STATUS BAR . RANGE SELECTED STATUS BAR

7. CUSTOMIZING THE STATUS BAR A check mark next to an item means the item is selected.

8. Row/Column size Manually dragging rows or columns to change size Adjusting the size through the “Row/Column Width…” menu

9. Se margin

10. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Performing calculations -

11. Excel functions All excel functions start with “=“ The most important of basic functions are: = IF – returns a TRUE or FALSE value for a given test = AND – checks a series of conditions and returns TRUE if all are proven to be true or FALSE if at least one of them is false = OR – checks a series of conditions and returns TRUE if at least one of them is TRUE or FALSE if all of them are false = COUNTIF – returns the number of cells which comply with a given condition = SUM – adds up all the numbers in a range of cells

12. The formula reads =5*3-4 which results in 11 The same result is obtained by giving the cells A2, B2 and C2 the values 5, 3 and 4 – thus the formula becomes =A2*B2-C2

13. For example, if we were to try to find the total SUM for the values in the A1:A25 range We could be using addition like the pervious example, but that would result in a very long formula: =771.17+17.28+912.23+340.86+412.91+247.55+121.77+670.88+251.08+85.29+284.53+523.13+376.76+571.73+955.27+345.39+529.95+67.05+395.30+685.27+555.47+431.99+754.41+417.68+767.79 Using cell references would also make for a long formula: = A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15+A16+A17+A18+A19+A20+A21+A22+A23+A24+A25 The most convenient solution is provided by the excel formula SUM which returns the sum of all numeric values in a given array : =SUM(A1:A25)

14. Formulas can be easily applied on multiple cells by dragging the right-bottom corner of the cell which contains a formula. Applying formulas on multiple cells will also lead to changes in the reference of that formula. Formula reference is B3:C3 . By “dragging” the formula downwards, the reference cells are also moving down.

15. Blocking References 1 2 3

16. Types or references: Free “=C2” – our reference cell will move if we apply the formula to other cells or columns Blocked Column “=$C2” – our reference cell will move if we apply the formula to other rows but the column will remain always remain the same Blocked Row “=C$2” – our reference cell will move if we apply the formula to other columns but the row will remain always remain the same Absolute Block “=$C$2” – our reference cell will not change regardless of we apply it Blocked Column reference Blocked Row Reference

17. A matter to be concerned with is the impact of copying, pasting or deleting references to an Excel Formula. This is where most mistakes occur in workbooks are can be very hard to identify for the “untrained eye”. Altering references: Copying data – if we copy a cell which acts as a reference to a formula, nothing will happen to that cell. Our old reference was not altered in any way; Cutting data – if we cut a cell which acts as a reference to a formula, the cell reference will also move in the formula. For example, if we cut B3 which acts as a reference in the simple formula =B3, the formula will automatically follow the reference (so if we paste in A3, the formula becomes =A3 ); Deleting data – A regular delete will only clear the content of a cell, but if we delete a row/column which contained a reference to an excel formula, that formula will now display a #REF! in the places where the reference was found; Altering formulas: Copying formulas – when copying a formula, any free reference (with no row/column blocks) will move exponentially to the formula’s new placement – for example: =A1+B2 a formula situated in C3 – if we copy the formula in C6, it’s references will move so that the formula becomes =A4+B5; Cutting a formula – will not alter any of the references! Deleting a formula – will not harm any references since the formula will no longer exist;

18. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Formatting -

19. Basic cell formatting options These basic options can be accessed through the “FONT” or “ALIGNMENT” categories under the “Home” Main Tab. Borders – allows for the addition of different types of borders for the selected cells Text Alignment – several options for changing the position of text inside a cell Wrap Text – increases the size of the row in order to fit all the information in a cell; Merge & Center – merges multiple cells into one (The new cell will keep the address of the first cell) Wrap Text OFF Wrap Text ON

20. Merging Cells

21. Excel provides multiple options for designing our tables such as one-sided borders, different style borders and line colors. In the screenshot below the “ All Borders ” option was used for cells B3:F13 Cell Borders

22. Cell Formatting

23. Default Formatting Options

24. In the following example we will cover the value “1” in all the default formatting's: General – 1 Number – 1.00 – by default will add 2 decimal places Currency – $1.00 – by default will add the currency as dollar Accounting – same as in currency but will provide a special alignment Short Date – 1/1/1900 – the reason for this strange date is the perception excel uses to store dates; All dates are attributed a number which is the equivalent of the number of days that have passed since the 1 st of January, 1900 . This means that the 20 th of September will be stored as 42998 (days since 01/01/1900) Long Date – Saturday, January 1, 1900 Time – 12:00:00 AM – the reason for this similarly strange formatting is the perception Excel uses to store time. All hours are being processed as a fraction of 1 divided by something. Excel understands 1 as the hour 12:00:00 AM , thus if we were to put in 0.75 it would result in 06:00:00 PM and 0.5 will be 12:00:00 PM . Percentage – 100.00% Fraction – 1 Scientific – 1.00E+00 Text – 1

25. Another example with 0.5 on the C column and 09/24/2017 on column D

26. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Working with Data

27. Conditional Formatting

28. Highlight Cells Rules : Greater Than… / Less Than… - based off a given number, highlights all cells that meet the condition; Between… / Equal To… - highlights all cells that meet the conditions (between A and B or equal to C); Text that Contains… - highlights all cells that contain a given letter or word; A Date Occurring… - overs a large variety of options in order to highlight specific cells formatted as “DATE” (such as Next Month, Last week, and so on); Duplicate Values… - highlights all cells in the given range that are identical; Top/Bottom Rules : Highlights cells based of given conditions ( Top 10 Items… / Top 10%... / Above Average… and so on); Data Bars: Offers a wide variety of colors to highlight a percentage of a cell based off a given condition (for example, if James has 79 Sales and we set the condition to 100, then 79% of the cell will be colored in order to highlight the progress made towards achieving the target); Color Scales: Multiple options that highlight numerical values based off their average (for example, if our given values range between 1 and 100 and we select the first option “ GREEN-YELLOW-RED ”, the bottom-most values will be red, values around 50 will be Yellow and the top values will be green); Icon Sets: Different types of icons used to showcase differences between numerical values based off their average;

29. Next off we are going to create a rule to showcase the differences in “Sales volume change ”.

30. Each conditional formatting rule has in-depth options which can be modified by using the “Manage Rules…” option under “Conditional Formatting”. Before opening this menu, make sure to select at least one of the cells to which the rule applies.

31. All options available to this type of formatting are displayed in the first cell. Next off we have a handful of secondary options followed by a third and final option. The number of options may vary for each type of formatting (I.E. “Date Occurring” only has one secondary option) Rule types can be easily changed to the ones that would best highlight our data. The first 4 options allow for the formatting of data based on simple conditions, while the 5 th one only formats data based on an Excel Formula. The “Format” button opens a new window which us to create a custom format which will then be applied to all cells that meet the formatting condition.

32. Formatting based on a formula Consider the following example:

33. =WEEKNUM(B3,2) =ISEVEN(C3) Final formula: =ISEVEN(WEEKNUMB(B3,2))

34. We can then go to “Conditional Formatting”  “New Rule”  “Use a formula to determine which cell to format”  we will have to create two formulas, one for odd and one for even weeks. We then design a cell formatting which we like and proceed to apply the formula on all cells

35. Paste formatting

36. PASTING Options Paste – will paste all the information from the copied cell (formatting/formulas or cell content) Paste Values – will only paste the content of the cell – in case the cell contains a formula, only the result of the formula will be pasted; Paste Formulas – will only paste the formula from the copied cell; Paste Transpose – will paste everything just like a regular paste but every reference of the cell will be transposed; Paste Link – will paste a reference to the original copied cell;

37. PASTING Options -Examples-

38. Filtering and sorting Excel provides two very simple solutions for organizing large amounts of data: Filters and Custom Sorting. Filters – a built-in function of Excel that allows for an easy filtering or sorting of large chunks of data based off: List Values – For example, sorting all data on a column in Alphabetical order; Format – Filtering all data on a column based on cell formatting options such as cell colors; Criteria – Filtering all information on a column based on a specific criteria (different for data containing Text or Numbers); While sorting information will only re-arrange it based on a given criteria, filtering data will “hide” the rows that do not comply with the given criteria. In order to apply a filter on specific data, all we have to do is to select the table and go to “ Sort & Filters ” and select “ Filter ”. This can also be achieved by selecting the “ DATA ” mail tab and clicking on the “ Filter ” button.

39. Once the filter is applied, we can sort information using the options mentioned earlier. This can be done by clicking on the “Sort” button 1. By default, “ Sort Smallest to Largest ” and “ Sort Largest to Smallest ” will be the first two options in any filter applier. This function works for both numerical and string (text) data. For text data it will arrange the information in an Alphabetical or Un-alphabetical order (and the name will change to “A->Z” or “Z->A”) ; 2. “Sort by color ” will present several options based on the different colors found throughout the column; 3. “Clear Filter From COLUMN NAME ” will remove any filter from the table. However, it will not return the data to it’s previous state if we are to sort the information; 4. “Filter by color” will hide all other data that does not have the same color as the option chosen; 5. “Number/Text Filters” has several default options such as Greater Than, Top 10, Contains (specific text) and so on; 6. “Search” option allows for us to search for a specific number/text inside the filtered information.

40. A few examples of how sorting and filtering can help us manipulate data: 1. Sort Smallest to Largest (Alphabetical order if the data is text) 2. Sort by color 3. Filter by color 4.Clear Filter From COLUMN NAME 5.Number filter (on column C) – Greater Than 5 SORTING EXAMPLES

41. Using the “ Search ” function to sort data based on a specific criteria. If we were to type the letters “ Ja ” in the “ Search ” bar for column B (Name) the details below will already showcase all the results that contain those two letters. After applying the filter Applying a similar filter on the “Sales” column – all values that contain the digit 4

42. Lastly, there is a simple difference we must understand between sorting data and filtering. Filtering occurs through hiding the data that does not meet the input criteria, while sorting will keep all data and sort it in a specific way. Although filtering will hide the rows not matching the information desired, trying to unhide that data will not work. Hidden data through filters will look similar to that of normally hidden rows, but the color of the row numbers will be blue. Filtering vs Hiding rows The easiest way of identifying data which was hidden is a double line which occurs between lines which were hidden. HIDDEN ROWS: 1012->1013 1015->1021 FILTERED ROWS 249->258 267->320 322->337

43. Custom Sorting – this function allows for a fast sorting of large amounts of data by applying several sorting options on different columns of a table. In the example below we will apply 3 sorts on a table in order to have them ordered properly. The “ Custom Sort ” option can be selected from the same menu as the “ Filter ” option, after selecting our table.

44. Custom Sorting allows for an unlimited amount of sorting levels. This can be done by using the “Add Level” button. “ Sort By” – will display a list of the available columns in the table; “ Sort On” – will allow for 4 sorting options: “ Values ” / “ Cell Color ” / “ Font Color ” / “ Cell Icon ” “Order” – will display sorting options based on the “Sort On”; For example: If we select “Values” in the “Sort On” category, we can choose “A-Z” in the “Order” category. If we select “Cell Color” in the “Sort On” category, we can choose colors from the ones available in that specific table column; In our example we will have the information ordered by 3 criteria: First Level – Queue sorted A-Z Second Level – Number of days sorted Smallest to Largest Third Level – Norm sorted Largest to Smallest

45. You can see the Queue is ordered in an alphabetical order. Each Queue section has the number of days sorted from low to high If two agents from the same Queue have the same number of days, they will be separated by the third level – Norm – from high to low Finished sorted table

46. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Charts -Charts are one of the most common ways to present data visually. This module walks you through creating and modifying charts in Excel-

47. Charts Excel has several different  types   of   charts , allowing you to choose the one that best fits your data. In order to use charts effectively, you'll need to understand how different charts are used. For the following examples, we are going to use a standard dataset representing the number of sales for 5 different departments for 5 different years.

48. Let’s start by inserting a chart. We are first going to use a simple “vertical bars” chart and try all of the common options to see how they can all enhance or decrease the amount of clarity provided to the data. To insert a chart, we first need to select the Raw Data which will be showcased in the chart and then go to the “Insert” Main Tab and select either the wanted chart or the “Recommended Charts” option.

49. The following window will come up if we click on “Recommended Charts” allowing us to choose from a preset which Excel will find most useful. We can also click on “All Charts” to get the full range or data charts available. Column Charts – uses vertical bars to compare data Line Charts – ideal for showcasing trends Pie Chart – proportion comparison Bar Charts – same as column charts but horizontal Area Chart – similar to line charts but the areas under the lines are filled Surface Charts – a 3D landscape ideal for large data sets

50. In the first example we are going to chose a Column Chart . The following image will appear in our document, showcasing the differences between department sales for each individual year. Next off, Excel provides a wide range of customization tools in order to modify the data so that we can see exactly what we want from it. Customization can be done through the “Design” and “Format” Main Tabs. There is enough customization to modify the data in any way shape or form we need. The most basic options include: Data Labels, chart titles, legends, gridlines and trends (all which can be done from the “Add Chart Element” option Colors and dimensions which can be primarily done from the “Format” Main Tab

51. We can also edit the data for our chart, swap the axis and even change the chart type with ease using the options under the “Design” Main Tab Charts also provide a few handy side options to further filter the data as well as quickly adding chart elements and changing chart colors. These will pop-up in the upper-right corner of any chart after you click on it

52. A few examples of changing chart types as well as adding elements:

53. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Working with Multiple Worksheets & Workbook -This module is all about working with multiple worksheets and workbooks. Learn how you can combine data, manage datasets and perform calculations across multiple sources-

54. RANDOM FACT #101 You cannot name a Sheet “History” The reason for this is a special and not so well known Excel feature which keeps a tracking record of changes made in a document. In order to check a file History, you need to press the following keyboard combination (in succession, not simultaneously): ALT+T+T+H This will open a window which allows for a few filters regarding the changes we want to see. Upon confirming, a sheet will be created named “History” which will contain a list of the changes made in the current file:

55. As stated previously, Excel offers the option of creating multiple worksheets which can all contain data or formulas, as well as opening separate Excel instances (or workbooks) and they can all be referenced together. Let’s start with referencing between worksheets – or simply “sheets”. Just like we have learned, the physical address of a cell of given by the Column & Row name/number. However, when working between sheets, in order to differentiate a cell – say, A1 – we must also add the Sheet Name in front of the cell address. Thus, A1 from Sheet1 will be come Sheet1!A1 and A2 from Sheet 2 will be Sheet2!A1. Luckily, we don’t have to write the actual address of the sheet if we want to reference something from another part of the workbook. By switching workbooks while working on a formula, Excel will keep the formula active in the formula bar and continue to take references of whatever we click on. There are formulas that will require typing the Sheet Name manually, but we’ll get to that later.

56. A cross-workbooks reference will look like this: =[Book1]Sheet1!$H$7 Book1 being the name of the document being referenced while Sheet1 being the actual sheet where the cell is located. Referring between workbooks is just as easy, but instead of having only the Sheet Name, the address will also acquire the document name – as long as it is still open! If by any change we close the document, Excel will even keep a physical address of the document to keep the reference alive. If by any chance the document gets erased, an error will be returned instead of the reference name – “REF!” – and if the formula in which the reference was used can continue without it, it will simply ignore the “REF!” alert.

57. A very common problem which you may encounter with Excel which can be extremely stressful and annoying is the double-Excel-instance. Basically, when referencing another workbook in a formula, Excel will simply not recognize other workbook. When switching to the other workbook, you will be able to navigate and edit the date while the formula you were creating in the original workbook will be in a “pending” status. This is caused by Excel opening two instances in your computer, two individual “Excels” which are completely individual from each other. Unfortunately, the only to resolve this matter is to shutdown the workbook that did not open under the same Excel process and re-open it. Status can be “Source is open” or “Error: Source not found” The Location section will display the actual PC address of the referenced workbook – if the workbook was not saved, it will be blank. We can easily check all connections to other excel workbooks by using the “Edit Links” option under the “Data” Main Tab – “Queries & Connections” section. Upon using the option, the following will be displayed:

58. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Text and Date Functions -By the end of this module, you will be an expert in Date and Text functions. This module discusses ways you can extract information and manipulate data to fulfil specific business requirements-

59. RANDOM FACT#31 FLIGHT SIMULATOR Very few people know, but Microsoft Excel 1997 had an amazing Easter Egg – The program included a flight simulator in which you could explore the environment with the mouse. you can move it back and forth to vary the pitch of your ‘craft’ and sideways to roll it

60. Text and Date Functions Every now and then, the working data will not be in a proper formatting for interpreting, thus leaving us with no option to manipulate the information. Luckily, instead of trying to change the source data, we can use various TEXT and DATE functions to modify the data in our advantage. Lets start with the basic formulas for this module: =TEXT( value,format_text ) – this formula will take a cell and modify it’s value in a specific format; =LEFT(text,[ num_chars ]) – will return x characters ( num_chars ) from a cell starting from the left side.; =RIGHT(text,[ num_chars ]) – same as LEFT but from the right side; =MID( text,start_num,num_chars ) – will return x characters ( num_chars ) from a cell starting from the y number ( start_num ) =DATE( year,month,day ) – will compose a date based off the imput values (numerical only) And here’s how they work, on a few simple examples: =TEXT(43022,"dd-mmm-yyyy") – will return 14-Oct-2017; =LEFT(“Hello”,2) – will return “He”; =RIGHT(“Hello”,2) – will return “lo”; =MID("Hello",2,3) – will return “ell”; =DATE(2017,10,14) – will return 10/14/2017

61. Let’s take a practical example. The following data is a report which is exported as Text , which means that it cannot be properly filtered or interpreted as Dates . We are going to try to compilate this data in two different columns, each representing the start and end date of these periods: If we look through the information, we can identify a few simple trends which will hep will separating the information before we create our dates: The first two characters are always the DAY of the first date; The 4 th and 5 th characters are always the DAY of the second date; The 7 th and 8 th characters are the MONTH of each date; The last 4 characters are the year of each date; We are going to separate these details on individual columns before we use the DATE function to create our new dates;

62. And this is how it all looks like in Excel:

63. Now let’s include the concatenate and text functions into example. Concatenate is a simple excel function which can be achieved either by typing =CONCATENATE or by using the “&” symbol between different “text” characters. For example, if we want the output of a cell to display the data from two different cells (say, A1 and B1) but combined, we could use: =CONCATATENATE(A1,B1); or =A1&B1; in both cases, the output will be the same; Let’s try to further complicate our example. We are going to use the H column to type the following standard message for each of our dates: Started with “FIRST DATE” and ended with “SECOND DATE” Upon typing =“Started with “&F2&” and ended with “&G2 in H2 we should get the wanted result. However, the following value will be displayed in the cell “ Started with 43180 and ended with 43184” The reason for this is the formatting of the cells – Both F2 and G2 contain DATE values, which mean that, when called in the CONCATENATE function, they will be displayed as their real values instead of our chosen formatting. And here’s where the TEXT function comes in.

64. To save our formula, we will use the TEXT function as if we were formatting a cell in the DD-MMM formatting: The TEXT formula works with all basic formatting's available in the “Format” section of excel

65. Before we wrap up, here are a few examples of the more popular format_text options for the TEXT function: DATES – “D” is short for DAY, “M” short for MONTH and “Y” short for year. These can be combined in a various number of ways, to showcase the exact value we desired: DD/MM/YYYY – 04/12/2013 DDD-MM – WED-12 DD-MMMM-YY – 04-December-12 HOURS – “H” is for HOUR, “M” for MINUTE and “S” for SECOND – additionally we can use “PM” or “AM”: H:MM AM/PM – 1:29 PM HH:MM:SS – 01:29:35 PERCENTAGE - 0.00% will translate in a number with 2 decimals and the “%” sign FRACTION - # ?/? will translate in a fraction such as 4 1/3 CURRENCY - $#,##0.00 will translate in a sum of dollars with 2 decimals - $1,234.57

66. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Data Validation & Name Ranges - The basics of creating a data list as well as interacting with defined names-

67. You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to  create a drop-down list. Data validation provides various options to restrict data inside a cell, starting from data formatting up to specific values. It can be accessed through the “DATA” Main Tab under the “Data Tools” Data validation has 3 main menus: Settings – chose the type of data as well as the restrictions; Input Message – design the message that will appear when the cell containing data validation is selected; Error Alert – design the error message that will appear if the data inserted differs from the one we want; Data Validation

68. Consider the following example: We want to fill the table below with details for each agent. However, in order to make sure everyone uses the same format, we will restrict each column to a specific type of data. We will also add specific input/error messages to let the user know more details regarding the input data LOCATION  we will restrict this to “Brasov”, “Sibiu” or “ Bucuresti ” DEPARTMENT  we will restrict this data based off the Location: Brasov  UK or US Sibiu  FR Bucuresti  SP or DE or RO

69. Before going forward with our example we need to discuss Defined Names. Data validation will allow us to restrict data in the “Department” field based off the location but we will need to create specific list with each department. In order to avoid typing in the range of the list (G3:G5 for BV, G7 for SIB and G9:G11 for BUC) we can create a “ Name ” for each range so that we can use them with ease. To do this, we need to access “ FORMULAS ” Main Tab and select “ Define Name ” under the “ Defined Names ” section DEFINED NAMES

70. Name  the Name by which the range will be known; Scope  if multiple excel workbooks are selected, we can choose which ones will have access to this range; Comment  an explanation about the contents of a defined name; Refers to  the range which will be converted to a Defined Name After creating 3 defined names for each of our departments, we can easily view them in the upper-left side of the screen under the drop-down menu for the cell we have currently selected. Another way to view or manage them is by clicking on “Apply Names” or “Use in Formula” under the “Defined Names” sectionC

71. We can then proceed to apply our data validation on both fields and customize the messages.

72. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Conditional Logic -Excel has several logical functions and this module explores some of them. We will be learning the concept of conditional logic in formulas, followed by how to conduct logic tests and use conditional operations, to our benefit-

73. Conditional logic functions are the cornerstone of Excel Functions. Mastering nested basic functions is the first step towards mastering complicated formulas, and the most basic of nested functions is “IF”. We have discussed the IF function earlier, so let’s get into nested functions. A nested function is simply a function repeated multiple times in order to achieve a needed effect. Let’s take a practical example. We are going to build a function that will determine what the proper bonusing is for an agent, considering that we have 3 different bonuses for each type of working hours (Full time – 1; Extended Part-Time – 0.75; Part time – 0.5;) For the formula to work, we are going to need to test the amount of sales made by each agent versus the number of sales in the bonusing system. The first IF will have to test the working hours – Type column – while the other 3 will be related to the amount of sales, resulting in a bonus based on those targets.

74. =IF(B2=1,IF(C2>=40,100,IF(C2>25,75,IF(C2>15,50,0))),IF(B2=0.75,IF(C2>=40,80,IF(C2>25,65,IF(C2>15,35,0))),IF(B2=0.5,IF(C2>=40,60,IF(C2>25,40,IF(C2>15,20,0))),0))) With each IF function, we will test if the Working hours (B2) are: Equal to : 1 0.75 0.5 IF one of the values turns to be true, then we will be test if: The sales Value is larger or equal to 40; If false, the sales value larger than 25 (and automatically lower than 40 since the previous statement was false); If false, the sales value larger than 15 (and automatically lower than 25 since the previous statement was false); If false, the value will be 0; The same result can be achieved in an easier way using the “IFS” function – IFS is a nested IF function which allows for multiple tests and values if TRUE only – which means that we have to accompany it with an IFERROR function, in the eventuality that the test will not be true. =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …) The final formula will look like this:

75. And this is how the IFS function will look: =IFERROR(IFS(B2=1,IFS(C2>=40,100,C2>25,75,C2>15,50),B2=0.75,IFS(C2>=40,80,C2>25,65,C2>15,35),B2=0.5,IFS(C2>=40,60,C2>25,40,C2>15,20)),0) An additional part of using conditional formulas are adjacent testing functions such as: AND(logical1,logical2,…) – will test each logical test and return “TRUE” only if ALL tests are TRUE; OR(logical1,logical2,…) - will test each logical test and return “TRUE” only if AT LEAST ONE test is TRUE; NOT(logical) – will return TRUE if the test is FALSE and FALSE if the test is TRUE; IFNA( value,value_if_NA ) – will return a value if a logical test returns #N/A; All these logical tests can amount to very complex formulas, so we need to be able to properly follow the logical line when things get complicated – luckily Excel offers a handful of tools that can help us evaluate our formulas.

76. The “Formula Auditing” section under the “Formulas” Main tab has a few functions designed to help with evaluating formulas: Trace Precedents – will draw arrows indicating all cells which affect the value of the cell currently selected; Trace Dependents – will draw arrows indicating all cells which are affected by the value of the cell currently selected; Remove Arrows – will remove all arrows from the previous two options; Show Formulas – will temporarily eliminated the “Cell Displayed Value”, showing only the actual values of all cells; Error Checking – will launch a new menu with more options on evaluating or checking an error (only if there is at least one formula in our Excel Workbook which contains or results in an error); Evaluate Function – will provide a step-by-step evaluation of the formula from the cell we have currently selected; Let’s break these options one by one, since they are vital identifying problems with complex formulas.

77. Trace Precedents Trace Dependents Each of the above cells impact the value of E9, since it contains a SUM formula. Thus, the arrows will point from each cell towards the E9 cell we have selected. Each of the cells form above (B5, D3, G3, etc.) contain the product between the value of E9 and the numerical value of each cell’s ROW. Thus, the arrows will point from E9 to all cells affected by it’s value.

78. Show Formulas – the cells blow (A1:C10) all contain a formula to calculate the ROW value + the COLUMN value of each cell. Upon pressing the “Show Formulas” button, these cells will contain the actual formulas and not the results of the formulas.

79. To showcase Evaluate Function let us return to our original example:

80. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Pivot Tables, Charts and Slicers -This module deep dives into the popular (and very useful) pivot tables. Learn how you can create and modify them to solve a variety of business problems. Then gain skills to create interactive dashboards with pivot charts and slicers-

81. PIVOT TABLES Being able to quickly analyze data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data. PivotTables are a great way to summarize, analyze, explore, and present your data, and you can create them with just a few clicks. PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create PivotCharts based on PivotTables that will automatically update when your PivotTables do. Let us consider the following example: We have a list of raw data containing 100 agents as well as details about their location, sales, skills and the month of when the sales were registered. A pivot table will help us to quickly analyze a few stats this data.

82. First, we need to select the data which will provide details for the pivot table. We will then go to the “ Insert ” Main Tab and Select “ Pivot Table ”. The following pop-up window will appear: The first section will ask you for details as to where the RAW data is located. By default it’s set to take the range you had selected, prior to pressing the “Pivot Table” button The second part will ask for details as to where we want our pivot table to be created. By default it’s set to create a new sheet.

83. Pressing “ OK ” will bring up the following tab in a new sheet. This section will help us customize the Pivot Table fields so it reflects the information desired. This section will display the name of each column in our RAW data. This information can be dragged & dropped in the area below. This is where the magic happens. These 4 sections will help the pivot table understand what we want to do: Filters  will act like a filter outside of the table, allowing us to switch between specific types of data; Columns  Information in this area will act like columns in our pivot; Rows  Information in this area will act like rows in our pivot; Values  the actual data which will be generated in the table;

84. So, what do we want from our RAW data? Let’s try these two analysis: Total number of sales per location for skill 1 and 2 ; Average number of sales per month by location for skill 3 ; 1. First, we want the skills to act as filters so we can select 1 and 2 . Thus, we will move the column “Skill” to the “Filters” area. Next, we want the location to act as Rows so we can see all 5 locations in separated rows – thus we will move “Location” to “Rows”. Lastly, we need the sales to be summed up per location, so we will move “Sales” to the “Values” area . The result will give us a simple, elegant table showcasing what we wanted with minimum effort!

85. By default, moving information to the “Values” field will generate a SUM of that data. However, this, can be modified to do all sorts of functions. Let’s see example 2:

86. Another very beneficial tools of Pivot Tables are Slicers (these can also be used for regular tables). By using the “Insert Slicer” option under the “Analyze’ Main Tab, we can select a column of the RAW data we want filtered and this will generate a “control panel” which allows for fast & easy filtering

87. PIVOT CHARTS Just as PivotTables behave similar to regular tables but providing extensive options for customizing the data, Pivot Charts will allow for multiple level filtering of information and real-time chart modifications. Let’s add a PivotChart to the previous example. To do this, all we have to do is go to the “Analyze” Main Tab and select “PivotChart”. What this will do is to open a template box from where we can choose the type of chart we want.

88. Manipulating data in a pivot table is extremely easy and to make it even better, Excel offers a handful of options to customize Pivot Tables as well as insert Pivot Charts. Let’s see how a chart will help us manipulate and understand data better on the previous example. How hard is it to see which Location had the highest average of sales in a month for skill 3? Or who had more sales in the month of April between London and Bucharest?

89. Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0; Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Automating Lookups -How do find information from different parts of a workbook? This module introduces you to one of the most important excel functions: VLOOKUP-

90. VLOOKUP VLOOKUP is an essential excel formula that can be found in many of our daily files. The formula is used to look a value inside a table and extract a corresponding value from the same row. Consider the following example: On one side (Columns B and C ) we have an exported table containing the number of sales for each agent. On the other side we need to extract the information for each one of our agents which are split in 4 teams

91. We’re going to start writing our VLOOKUP formula in F3 for the first agent. VLOOKUP uses a very simple functioning principle – you provide a value and a table which has that value on it’s 1 st column – once the formula finds the value, it will return a result which is on the same row as the one you provided, but on a different column VLOOKUP has 4 major elements Lookup_Value – the actual value we are trying to find in a table; Table_Array – the table where our values are located(both the LookupValue and the value we want in return); Col_Index_Num – the number of columns (to the right) where the wanted data is (count starts with 1) [ Range_Lookup ] – 0 for an Exact Match or 1 if you want the lookup to be an approximate value;

92. B3 - Lookup_Value B:C (or B2:C22 ) - Table_Array 2 - Col_Index_Num since the value we want back is on the 2 nd column of the table - [ Range_Lookup ] because we want an exact match =VLOOKUP(E3,B:C,2,0)

93. We can then apply the formula to the other fields of TEAM 1. Next we will apply it to the other team tables but beware, copying the formula from one table to another will move all unblocked references! The first example showcases the situation of copying the formula without blocking the Table_Array reference. When copying the formula to the TEAM 2 table, both the Lookup_Value and the Table Array will be moved 3 columns to the right. While we want that to happen to the Lookup_Value (since we don’t want to look for the same agents we did in the TEAM 1 Table) this will result in an error since the Table_Array will be completely out of place.