BI Data Analytics

Below are few tips to set up Excel for Data Analysis: How to create table and name it? In Excel Tab, click on any cell with data in it and press Ctrl +T. Excel will automatically select all columns and rows that have data. Click OK to create Table. In Tab, click on any cell that have data. In Top Menu Bar, select Design Tab. In top left hand side of screen, find table name option to change name from Table1 to Name you want to change to. How to activate Excel Solver add-in? Select File - Options - "Add-Ins" Option and click GO button Check the box labeled "Solver Add-in" and click OK Navigate to Data Tab and see new button for Solver Add-in How to enable Developer Tab to view macros? Select File - Options - Click "Customize Ribbon" On the right-hand side of the Excel Options dialog box, check the Developer checkbox and click OK Check that  DEVELOPER  menu has been added to the ribbon. Shortcuts: # ALT+F11 - To invoke VBA Edi...

In Excel, there is funciton to remove duplicates; however, didn't find direct method to identifying duplicates based on specific columns. Below is step created to find out duplicate ID columns for further analysis: Column A contains all Customer IDs. We need to identify how many rows are generated for a customer id. Step 1: Sort based on Column A Step 2: Add 3 Columns with below calculations =COUNT(MATCH(A10,A11,0)) as B10 =COUNT(MATCH(A10,A9,0))  as C10 =IF(B10=1,1,IF(C10=1,1,0)) or B10+C10  as D10 Now You can use D10 to filter on 1 to list down all duplicate records for further analysis for reason of what values are different or any other purpose.