Business Analytics with Excel | Data Science Tutorial | Simplilearn

[Music] hello and welcome to the Business Analytics with Excel course offered by simply learned this video will give you an overview about the importance of analytics in business today why it is in demand among a lot of organizations and how you can effectively leverage Microsoft Excel for data analytics it is quite clear that businesses must understand the changing market and trends in social media to stay ahead of the competition and drive effective business decisions what can help businesses maintain their competitive edge the answer lies in business analytics the term business analytics refers to the skills technologies applications and practices that facilitate continuous iterative exploration and investigation of past business performance customer data and a competitive landscape data the goal is to gain insights and drive business planning therefore business analytics empowers businesses to automate and optimize business processes thereby enabling them to sustain the competitive advantage Microsoft Excel has extensive analytics capabilities to perform statistical analysis with the added advantage of visibility a flat learning curve and good reporting functionalities Excel has become the tool of choice to start your data analytics journey did you know Excel offers intuitive analytics features such as find connect and shape to model and analyze a company's data today Excel comes with in-memory technology that makes it easily compatible with millions of bytes of data enabling companies to perform the necessary analysis without being dependent on specialized solutions additionally features such as power business intelligence including power pivot power query power view power Maps and so on easily integrate with Excel the sandbox like Excel environment makes data exploration and deriving results quite easy it includes features to perform powerful data analytics processes with just one click instead of using a complex set of formulas pivot tables and manual steps with increasingly diverse tool sets being added to excel it's very clear that Excel is the business intelligence tool of choice and the future of business intelligence workflows and is being increasingly preferred by analysts reporting professionals and bi people Excel provides a grid-like interface of rows and columns enabling users to organize and format data better the data can be simple text numbers dates or other supported formats excels libraries span a large range of functions from basic math to complex statistical formula it cannot be said enough that Excel is pivotal to organizations as it is the leading user-friendly analytical tool Amazon in American electronic commerce and cloud computing company has been in business for more than twenty years it has a user base of millions of customers ever wondered how Amazon predicts exactly what customers want to buy it's simple or let's just say it's simple business analytics that involves understanding the patterns of what happened in the past to predict what might happen in the future currently this is a requirement of every business simply learns business analytics with Excel course introduces you to excel the powerful data analysis tool that will enable you to collect data and perform an in-depth analysis of the data to drive effective impactful business decisions designed it to build upon the concepts and procedures in data analytics the primary focus of the course is to learn how to use Excel to perform a multivariate statistics and to understand how to explore experiment and forecast results with Excel histograms pivot tables statistics and mathematical formulas and charts after attending this course you should be able to understand business problems and data sets perform calculations using statistical techniques conduct a variety of statistical analyses including regressions covariance correlation and testing hypotheses present and interpret the results of statistical now design dynamic reports and dashboards for business consumption this highly engaging course features concept videos with exercises which will empower you with hands-on experience the course covers introduction to business analytics with an overview to different types of analytics statistical and mathematical functions after each lesson practice and revision assessments are provided to reinforce the concepts learned the course ends with detailed case studies outlining real-life scenarios simply learn will offer complete support anytime during the course or while you're working on the projects if you subscribe to the live virtual classroom training you can attend an ongoing batch and revisit important concepts to seek help for the projects visit the simply learn website wwm play learn calm and click help & support at the bottom of the page to submit queries you can also speak directly to the support staff with the simply talk or live chat option so are you ready to begin happy learning hello and welcome to the lesson formatting conditional formatting and important functions in this lesson we will learn about custom formatting how to use conditional formatting and formulas and implement logical statistical and mathematical functions let's say we need to differentiate the sales value from the given data set as above target below target and met target as a data set can be huge in complex differentiating each value manually would consume a lot of effort and time here comes the Excel features such as formatting and conditional formatting which enables us to perform this task easily formatting as the name suggests helps us to format the data using different techniques making the data easy to read in the required format and analyze them the right formatting technique applied to the worksheets can help the users present the data efficiently conditional formatting a type of formatting helps us visually explore and analyze data detect critical issues and identify patterns and trends conditional formatting helps to add patterns and trends to the raw information using different colors icons and formulae etc at times we also need to perform mathematical operations and calculations within our data set Excel provides a large number of logical statistical and mathematical functions such as vlookup H lookup and if not ranked quartile and many more that can help the users to perform calculations ranging from basic to complex operations these inbuilt functions help us to manage data and perform descriptive statistical analysis Excel also offers a wide range of important formulas that help to perform many common tasks these formula may be as simple as basic addition or it could be a complex combination of in Bill excel functions before going into details let us start this lesson by defining the objectives of the lesson the worksheets appear more polished and easy to read if proper formatting techniques are applied to it we can format the cells in Microsoft Excel manually by selecting fonts font color font size background colors and borders we can also use numerous predefined table styles or quick styles to quickly format a table automatically this is the basic formatting in Excel if these built-in functions do not meet our needs or do not display the data in the format that we require we can create our own custom format in this topic will focus on custom formatting in Excel let's say we want to display one million five hundred and thirty two thousand as 1.53 m or the first of January 2016 date with a weekday as the first Friday January 2016 here we can use custom formatting feature of Excel let's see how we can accomplish this in Excel in this lesson we shall consider the following fields from our master table dates order ID product salesman region number of customers net sales and profit and loss here in the given worksheet we have a column named net sales suppose we want to display the values of net sales in terms of thousands for example seven thousand one hundred and sixty-four should be displayed a seven point one six K select the entire column for net sales under the Home tab click the drop-down present in the numbers panel select the option more number formats a format cells dialog box appears from the category list select the type as custom in the type text box enter number column number number 0 decimal 0 0 comma open double quote capital K close double quote here number is the digit holder with thousand comma separation each comma after decimal zero zero indicating the two zeros after decimal point indicate number of value after decimal places the K component displays a literal K character to denote thousands here we can see the preview in sample box the value shown is seven point one six K finally click OK button the values in column net sales are now displayed in terms of thousands now let us consider another example of date formatting in the column dates we have a date value written as first Jan 2012 we want to display it as first Sunday Jan 2012 first select the column containing dates tab click the drop-down present in numbers panel select the option more number formats a format cell dialog box appears from the category list select the type as custom in the type text box enter DD - DD DD - mmm - why why here d stands for date M stands for month and y stands for year the first 2 D D is the date the next 4 D D D D is for weekday say Thursday then mmm is for the month in short form and YY is for year here we can see in the preview in the sample box the values shown is one Sunday Jan 12 finally click on OK button you thus the custom formatting helps us in changing the appearance of the cell value according to our needs a worksheet may contain thousands of rows of data by simply examining the raw information it would be difficult to see patterns and trends conditional formatting helps us visualize data that make worksheets easier to understand it quickly highlights important information in a spreadsheet by using colors icons and data bars it changes the appearance of one or more cells when the cell value meets certain criteria to do this we need to create a conditional formatting rule for example a conditional formatting rule can be if value is greater than 5,000 color the cell yellow by applying this rule we were able to quickly see which cells contain values that are greater than 5,000 let us understand this with the help of examples consider the given sales table which depicts the amount of sales done by each salesman here we want to highlight the duplicate order ID values first select the order ID column click Home tab under styles panel select the option conditional formatting from the drop down menu hover the mouse over highlight cells rules and select duplicate values a duplicate values dialog box appears select a formatting style from the drop-down menu in our example we will choose green fill with dark green text click OK here we can see that the conditional formatting is applied to the column order ID Excel highlights all the duplicate cells with green fill with dark green text we can also explore other options available in conditional formatting like greater than less than between equal to text that contains a date occurring and top / bottom rules all these rules work in the similar format Excel provides predefined styles to quickly apply conditional formatting to our data they are data bars data bars are horizontal bars added to each column much like a bar graph color scales color scales change the color of each cell based on its value each color scale uses a two or three color gradient icon sets icon sets add specific icon to each cell based on its value let us consider another example to explain how icon set rules are used consider the given sales table here on the basis of the sales amount we want to apply the icon set rule if sales amount is greater than 8000 then a green arrow should appear in the upward direction if sales amount is between 5,000 and 8,000 then show a yellow arrow in the horizontal direction and if sales amount is less than 5,000 then show a red arrow in the downward direction to perform conditional formatting select the desired cells click on Home tab under styles panel select the option conditional formatting from the drop down menu hover the mouse over icon set rules and select the first rule again click on conditional formatting select the option manage rules from the drop-down a conditional formatting rules manager dialog box appears - icon set rule and select three icon set rule click on edit rule and an edit rule formatting dialog box appears by default for three icon set rule Excel uses the 67th percentile and the 33% I'll rule this means that it shows a green arrow for values greater than 67% yellow arrows for values between 67 and 33% and red arrow for values less than 33% now let us customize this rule for our criteria in the type drop-down select the option as number you in the value reference box enter value is 8,000 for green color arrow and 5,000 for yellow color arrow Excel automatically picks the criteria for the red color arrow click OK in the conditional formatting rules manager window click OK we can see that the cells are highlighted as per our criteria cells which have a value greater than 8000 have a green arrow in the upward direction values between 5k and 8k are highlighted with a yellow arrow and values less than 5k are highlighted with a red arrow we can also apply conditional formatting rule with the help of formulas consider the given table which shows the profit done by each salesman here we want to highlight the rows in the table where ever profit is greater than 4000 select the entire table along with headings click on conditional formatting select the option new rule from the drop-down a new formatting rule dialog box appears under select rule type select the option use a formula to determine which cells to format under format values reference box and to the formula as equals if Open bracket dollar sign h2 greater than 4000 comma true comma bolts close bracket this will apply formatting to a cell if the profit is greater than 4000 click on format button from the format cells dialog box select the fill tab from the color dialog box select the color as green finally click OK the conditional formatting is successfully applied to the cells here we can see that the cells with the profit greater than 4000 are highlighted with the green color logical functions evaluate a cell or cells for our criteria and returns a boolean value true or false in this topic we will cover various logical functions like if and/or not true false consider the given sales table here on the basis of the sales amount we want to determine the commission percentage of each salesman following is the criteria if net sales greater than or equal to 10,000 set commission as 5% if net sales greater than or equal to 5,000 and less than or equal to 10,000 set Commission as 2.5% if net sales less than 5,000 set Commission as 1.5% this is a common condition where we're required to create a between formula that picks all values between the two given values in such conditions we use if with the and function let's write the formula in cell I five like any other formula we will start with the equal sign then type our function e if and then type in open parentheses the first parameter we need to pass is a logical test here the logical test is if sales cell G 5 greater than or equal to 10,000 a sign value as 5% then we will pass the second logical test here we will use an function if open bracket and open bracket sale that cell G 5 greater than or equal to 5,000 comic at cell G 5 less than or equal to 10,000 a comma a signed value as 2.5% in the else parameter we will type 1.5 percent this means that if the first two cases become false then the formula will assign the value 1.5 finally close the two braces for the two ifs thus Excel returns the result as 2.5% for cell G 5 now drag the formula with the help of the fill handle we can see the results for the other cells as well the or function returns true if any of the conditions are true and returns false if all of the conditions are false a given table we are required to assign performance remarks for each salesman as per the scaling table in scaling table we have the criteria that if net sales greater than 10,000 or profit greater than 5,000 set remark as good else set remark as needs improvement let's write the formula using if and or' to check sales and maximum sales in cell j5 so our formula becomes equals if open bracket or open bracket g5 greater than 10,000 comma H 5 greater than or equal to 5,000 comma double quote good double quote comma double quote need improvement double quote close bracket let's drag this formula across the cells with the help of the fill handle this formula returned true if any of the condition is true thus in the given example the cell j9 returns good even if profit is less than five thousand our next function is true / false true / false returns true and false respectively if we write them in any cell the not function reverses the result of the function if result is true it returns false else true let's write formula as equal not open bracket k5 closed bracket in cell l5 this returns the result as false let us now study about different lookup and reference functions used in Excel such as vlookup each lookup match index and offset function the vlookup function lets us search for specific information in the current worksheet say we have a list of students with marks we can search for the marks of a specific student if our table is in horizontal format and we want to determine a specific value then we use the H lookup function consider the extract from our master sales table the table depicts sales information based on order ID products sold salesman and net sales now we want to determine the net sales corresponding to order ID 1 1 2 1 let's understand how we can accomplish this using the vlookup function note that vlookup function is only used for vertical tables let's use cell I to to add the formula like any other formula we will start with the equal sign then type our function named vlookup and then type and open parenthesis now we will add the arguments the first argument is lookup value lookup value corresponds to the value or item we are searching for so in our example it will be order ID 1 1 2 1 cell H 2 the second argument is table array table array is the cell range that contains the data in this example our data is in cell b2 to e 40 you you the third argument is called index num column index number the column index number is the column number in which we are looking for information here in our table array net sales is the fourth column so our third argument will be for the fourth argument is range lookup this value corresponds if we are looking for approximate matches or not and can have value either true or false true denotes the approximate match and false denotes the exact match here we will pass false finally close the parentheses so our function has the form equal vlookup open bracket h2 comma B 2 : e 4 t comma 4 comma false close bracket we can see that the function returns the value as 2 - 7 7 note that the vlookup function works from left to right that means the lookup value should always be present in the leftmost column of our table array if our table is in horizontal format and we want to determine a specific value then we use the H lookup function consider the given table let's write the formula in cell I for we will start with the equal sign then type our function name each lookup and then type open parentheses the first argument is the lookup value so in our example it will be order ID equals 1 1 - 1 cell 8 for the second argument is table array in this example our data is in cell G 8 2 is 11 the third argument is row index number here our table array net sales is the fourth row so our third argument will be 4 the fourth argument is range lookup here we will pass false finally close the parentheses so our function has the form equals H lookup open bracket H 4 comma G 8 : a s 11 comma 4 comma false close bracket the function returns the value - 2 7 7 note that H lookup function works from left to right that means lookup value should always be in the topmost row of your table array the match function searches for a specified value in an item in a single dimensional array and then returns the relative position of that item in the array let us understand its functioning with the help of an example consider the given sales table shown here we want to determine the position of the corresponding date in the original table so let's write the formula in cell e5 type equal sign then type the function name match and then type an open parenthesis the first argument is the lookup value so in our example it will be date in cell b5 the second argument is lookup array the lookup array is a range of cells that contains the value we are searching for in this example we will select the cells b-17 to be 1 3 4 2 you you the third argument is match type match type can be either -1 0 or 1 should be one if we want to find the largest value less than or equal to the lookup value the value should be zero if we want to find the first value exactly equal to lookup value it should be minus one if we want to find the smallest value greater than or equal to lookup value here we'll pass a match type as zero as we require an exact match finally close the parentheses so our function should look like match Open bracket B five comma dollar sign B dollar sign seventeen : dollar sign B dollar sign 1 3 4 2 comma zero close bracket this returns the value as 3 now we can drag the formula across cells with the help of the fill handle similarly we can use the match formula in cell III to determine the position of the shown field name cell c3 in range b-17 to I 7 teen for example net sales is at 7th position in the range b-17 to i 17 let's write the function in cell III write formula as equal sign match and open parentheses in the first argument select cell c3 for second argument select cells b-17 to I 7 teen past third argument is 0 finally close the parentheses so our function looks like equal match open bracket C 3 comma dollar sign B dollar sign 17 : dollar sign I dollar sign 17 comma 0 close bracket so for profit loss field the value returned is 8 net sale the value returned is seven the index function returns value from a specified position in a specific column in a list suppose we want to determine the value of the field mentioned in cell c3 from the sales table on a specified date the table should dynamically fetch the value if the value of the cell c3 has changed and if we enter profit loss formula should fetch the profit loss value for the specified date if we write net sales in cell c3 then formula should fetch net sales value for specified date so let's use the index function to determine the value in this worksheet select cells c5 write formula as equal sign index and open parentheses the first argument is the array for our example enter b-17 colon i1 342 you the second argument is the roan um this corresponds to the row number that has the value we want to return here we will enter a five which denotes the required date the third column is call num this corresponds to the column number that has the value we want to return here we will enter III which denotes the position of the field name in cell c3 in table finally close the parentheses thus our formula returns the value as 6500 and 28 based on the position at cells c5 this is the net sales value on the date the second of January now drag the formula with the fill handle to the other cells as well the offset function returns a reference to a range that is the offset number of the rows and columns from another range or cell now let's understand how to use it select cell d5 right formula is equal sign offset and open parenthesis the first argument is the reference this denotes the starting range from which the offset will be applied for our example we will select the reference cell as b-17 the second argument is the rose this denotes the number of rows to apply as the offset to the reference this can be a positive or a negative number so for our example we will enter a 5 minus 1 here we are subtracting 1 to match number of row which have the value and the offset from the reference cell the third argument is the calls this corresponds to the column number that has the value we want to return here we will enter III which denotes the position of the field name and subtract by 1 to match with the offset referencing finally close the parentheses the function returns the value based on the position of date from cell b5 and field from cell c3 now drag the formula across the cells with the help of the fill handle if we enter profit loss formula should fetch the profit loss value for the specified date a variety of statistical functions are available in Excel to perform calculations ranging from basic function mean median and mode to more composite distribution and test binomial or chai square in this topic we will cover various statistical functions like sum if countifs percentile quartile standard deviation and median some ifs and countifs are the most frequently used functions in Excel these functions allow us to perform count and some based on one or more criteria the sum ifs function calculates the sum of cells based on certain criteria let's understand how to use the function with the help of an example consider the given sales table say we want to determine the net sales done by salesman Justin here we will use the sum ifs function to calculate net sales so let's write the formula in cell n4 type equal sign then type the function name some ifs and then type in open parenthesis the first argument is the sum range this is the input range that contains the required data so in our example we will select cells h4 to H 1328 the second argument is the criteria range this denotes the range that contains the required criteria here will pass the range that holds salesman's name cells efore to e 1328 the third argument is criteria this corresponds to our required criteria we only require net sales of the salesman Justin so we will select cell m4 finally close the parentheses note that we can pass multiple criteria in this function the function returns the net sales done by Justin countifs function works in a similar manner like some ifs it counts the number of cells in a given range when certain criteria is met say in the given sales table we want to determine the total number of sales for net sales greater than 8,000 to determine the count we will use the countifs function so let's write the formula in cell n 5 type the equal sign then type the function name countifs and then type in open parentheses the first argument is the criteria range one this denotes the range that contains the required criteria here we will pass the cells containing net sales h4 to H 1328 the second argument is criteria one this corresponds to our required criteria we only require entries with net sales greater than eight thousand so we will enter greater than eight thousand finally close the parentheses note that the function allows you to enter multiple criteria so n5 reflects the total number of sales as four hundred and forty five for net sales greater than eight thousand in this topic we will learn how to use the functions percentile quartile standard deviation and median in Excel consider the given sales table let's see how to use the percentile function on net sales to find its 40th percentile select the cell and 6 write the formula as equal sign percentile open bracket select the column net sales H for 2h 1328 as our first argument pass the second argument as 0.4 in order to find the fortieth percentile this returns the result 5220 this means that 40% of the net sales is less than or equal to 5220 the next function is quartile quartile function depicts the results for the first second third and fourth quartile say we want to find the total number of sales which are less than or equal to 25% let's use the quartile function to calculate this selects ln7 to write the formula type equal sign function name is quartile and open braces select net sales column H for 2h 1328 as the first argument type 1 as the second argument the second argument must be a number between 0 & 4 the function returns the value as 3822 which means that 25% of the sale is less than or equal to three thousand eight hundred and twenty two note that here we can use the percentile function to perform the same task the function percentile H for 2h 1328 comma 0.25 gives us the same result it's up to us which function we would like to use for the quartiles Excel has a function to calculate sample standard deviations stdev standard deviation determines how dispersed the data is from the mean say we want to calculate the standard deviation for a number of customers so write the formula in cell n8 as equals the function name stdev open braces select number of customers columns as its argument the function returns a result one point for the median function returns the value from the range for example we want to find the mid value for net sales to calculate median in sell and nine write the formula as equal sign the function named median then open braces select net sales column as its argument this calculates the mid value of net sales as six thousand one hundred ninety two rank function is used to compare a number from the rest of the numbers in list in any order whether ascending or descending this function returns the rank of the number relative to the other values in the list let's use rank function in cell j4 to calculate rank on the basis of net sales select cell j4 and write the formula as equal sign then type the function name rank and then type in open parentheses the first argument is the number this is the value whose rank we're required to calculate here we will pass the argument cell h4 the second argument is ref this corresponds to the array or reference list here we will select the column net sales ie cells h4 to H 1328 you the third number is order this specifies how to rank the number zero means descending order and one denotes ascending order this argument is optional if we don't pass any argument Excel automatically takes the argument zero here we will pass zero finally close the parentheses here we can see that Excel has determined the rank for net sales seven thousand one hundred and sixty-four as 553 finally we can drag the formula and can see the rank for the other cells based on net sales [Music] Hey want to become an expert in Big Data then subscribe to the simply learn Channel and click here to watch more such videos to nerd up and get certified in Big Data click here

Channel: Simplilearn

Views: 261,628

Keywords: simplilearn, training, tutorial, certification, course, curriculum, Free resources, business analytics with excel, business analytics with excel spreadsheet functions, excel tutorial, excel formulas and functions, excel tricks, excel vlookup, excel shortcut keys, business analyst, who is a business analyst, learn excel basics, excel for begineers, conditional formatting, ba with excel, business analyst skills, excel lookup, vba, excel simplilearn, business analytics simplilearn

Id: W3vrMSah3rc

Length: 42min 30sec (2550 seconds)

Published: Tue Aug 29 2017