19/09/2023
𝗠𝗔𝗞𝗘 𝗔 𝗖𝗔𝗥𝗘𝗘𝗥 𝗜𝗡 𝗗𝗔𝗧𝗔 𝗔𝗡𝗔𝗟𝗬𝗧𝗜𝗖𝗦 𝗔𝗡𝗗 𝗕𝗨𝗦𝗜𝗡𝗘𝗦𝗦 𝗜𝗡𝗧𝗘𝗟𝗟𝗜𝗚𝗘𝗡𝗖𝗘 𝗨𝗦𝗜𝗡𝗚 𝗔𝗗𝗩𝗔𝗡𝗖𝗘𝗗 𝗘𝗫𝗖𝗘𝗟, 𝗣𝗢𝗪𝗘𝗥 𝗕𝗜, 𝗧𝗔𝗕𝗟𝗘𝗔𝗨, 𝗩𝗕𝗔 𝗔𝗡𝗗 𝗦𝗤𝗟
𝐅𝐞𝐞:
𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐑𝐬: 𝟔𝟗𝟗
𝐄𝐱𝐜𝐞𝐥+𝐏𝐨𝐰𝐞𝐫 𝐁𝐈: 𝐑𝐬 𝟗𝟗𝟗
𝐓𝐚𝐛𝐥𝐞𝐚𝐮 𝐑𝐬: 𝟔𝟗𝟗
𝐂𝐨𝐦𝐩𝐥𝐞𝐭𝐞 𝐌𝐢𝐜𝐫𝐨𝐬𝐨𝐟𝐭 𝐄𝐱𝐜𝐞𝐥: 𝐑𝐬 𝟒𝟗𝟗
-----------------------------------------------------------------------
𝗙𝗹𝗲𝘅𝗶𝗯𝗹𝗲 𝗧𝗶𝗺𝗶𝗻𝗴𝘀: 𝘄𝗲𝗲𝗸𝗱𝗮𝘆𝘀 𝗮𝗻𝗱 𝘄𝗲𝗲𝗸𝗲𝗻𝗱𝘀
𝗭𝗼𝗼𝗺 𝐋𝐈𝐕𝐄 𝗢𝗻𝗹𝗶𝗻𝗲 𝗖𝗹𝗮𝘀𝘀𝗲𝘀
𝗥𝗲𝗰𝗼𝗿𝗱𝗶𝗻𝗴 𝗮𝘃𝗮𝗶𝗹𝗮𝗯𝗹𝗲 𝗣𝗼𝘀𝘁 𝗖𝗹𝗮𝘀𝘀
𝗗𝗿𝗼𝗽 𝗮 𝗺𝗲𝘀𝘀𝗮𝗴𝗲 𝗼𝗻 𝟬𝟲𝟯𝟵𝟵𝟱 𝟱𝟯𝟰𝟵𝟳, 𝘄𝗲 𝘄𝗶𝗹𝗹 𝗴𝗲𝘁 𝗯𝗮𝗰𝗸 𝘁𝗼 𝘆𝗼𝘂
𝗦𝗲𝗲 𝗯𝗲𝗹𝗼𝘄 𝘾𝙊𝙐𝙍𝙎𝙀 𝘾𝙊𝙉𝙏𝙀𝙉𝙏𝙎 𝗳𝗼𝗿 𝗘𝘅𝗰𝗲𝗹, 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜, 𝗧𝗮𝗯𝗹𝗲𝗮𝘂, 𝗩𝗕𝗔
==========================================
𝗠𝗜𝗖𝗥𝗢𝗦𝗢𝗙𝗧 𝗘𝗫𝗖𝗘𝗟 (𝗔𝗗𝗩𝗔𝗡𝗖𝗘𝗗):
𝗥𝗘𝗤𝗨𝗜𝗥𝗘𝗠𝗘𝗡𝗧: 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗢𝗳𝗳𝗶𝗰𝗲 𝟮𝟬𝟭𝟯 𝗮𝗻𝗱 𝗮𝗯𝗼𝘃𝗲
==========================================
𝗘𝘅𝗰𝗲𝗹 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀 & 𝗯𝗮𝘀𝗶𝗰 𝗳𝗼𝗿𝗺𝘂𝗹𝗮𝘀
Writing conditional expressions (using IF)
Using logical functions (AND, OR, NOT)
Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
VlookUP with Exact Match, Approximate Match
Dynamic Ranges
Nested VlookUP with Exact Match
Using VLookUP to consolidate Data from Multiple Sheets
𝗗𝗮𝘁𝗮 𝗩𝗮𝗹𝗶𝗱𝗮𝘁𝗶𝗼𝗻𝘀
Specifying a valid range of values for a cell
Specifying a list of valid values for a cell
Specifying custom validations based on formula for a cell
𝗦𝗼𝗿𝘁𝗶𝗻𝗴 𝗮𝗻𝗱 𝗙𝗶𝗹𝘁𝗲𝗿𝗶𝗻𝗴 𝗗𝗮𝘁𝗮
Sorting tables
Using multiple-level sorting
Using custom sorting
Filtering data for selected view (AutoFilter)
Using advanced filter options
𝗣𝗶𝘃𝗼𝘁 𝗧𝗮𝗯𝗹𝗲 𝗮𝗻𝗱 𝗥𝗲𝗽𝗼𝗿𝘁𝘀
Creating subtotals
Multiple-level subtotals
Creating Pivot tables
Formatting and customizing Pivot tables
Using advanced options of Pivot tables
Pivot charts
Pivot table slicers
Consolidating data from multiple sheets and files using Pivot tables
Using external data sources
Using data consolidation feature to consolidate data
Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
Viewing Subtotal under Pivot
Creating Slicers ( Version 2010 & Above)
𝗠𝗼𝗿𝗲 𝗙𝗼𝗿𝗺𝘂𝗹𝗮𝘀 𝗮𝗻𝗱 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
Date and time functions
Text functions
Array Formulas
Power Functions (CountIf, CountIFS, SumIF, SumIfS, Sumproduct, Offset)
String Functions (Left, right, mid)
𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝘄𝗼𝗿𝗸𝗯𝗼𝗼𝗸 𝗖𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻𝘀
Using Microsoft Query to get data from different sources
Linking Different Workbooks
𝗙𝗼𝗿𝗺𝗮𝘁𝘁𝗶𝗻𝗴
Using auto formatting option for worksheets
Using conditional formatting option for rows, columns and cells
𝗖𝗵𝗮𝗿𝘁𝘀
Formatting Charts
Multiple series charts
Column Charts
Waterfall Charts
Line Charts
Pie Charts
Dynamic Charts
Slicer with charts
Using Bar and Line Chart together
Using Secondary Axis in Graphs
Pivot Charts
𝗗𝗮𝘁𝗮 𝗠𝗼𝗱𝗲𝗹𝗹𝗶𝗻𝗴 𝗶𝗻 𝗘𝘅𝗰𝗲𝗹
Power pivot
Relationships between tables
Basic Measures
𝗩𝗕𝗔 𝗠𝗮𝗰𝗿𝗼𝘀
Recording Macro
Relative & Absolute Macros
Editing Macro's
=========================================
𝗣𝗢𝗪𝗘𝗥 𝗕𝗜:
𝗥𝗘𝗤𝗨𝗜𝗥𝗘𝗠𝗘𝗡𝗧: 𝗙𝗿𝗲𝗲 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗗𝗲𝘀𝗸𝘁𝗼𝗽, 𝗻𝗼 𝗻𝗲𝗲𝗱 𝘁𝗼 𝗯𝘂𝘆 𝗹𝗶𝗰𝗲𝗻𝘀𝗲
=========================================
𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝗗𝗮𝘁𝗮 𝗠𝗼𝗱𝗲𝗹𝗹𝗶𝗻𝗴
Understanding table relationships
Understand Cardinality and Cross Filtering
Creating Calculated Columns and Tables
𝗣𝗢𝗪𝗘𝗥 𝗕𝗜 𝗩𝗜𝗦𝗨𝗔𝗟𝗜𝗭𝗔𝗧𝗜𝗢𝗡
Choosing the right visualizations
Creating, formatting, and styling your reports
Using Hierarchies and Drilldown
Utilizing Slicers and Filters
Adding Custom Visualizations
Column Charts Line charts Waterfall charts, stacked chart, treemap chart
Scattered chart, Donut chart, Area Chart
Table and Matrix visual
Slicer with charts
𝗗𝗔𝗫 𝗕𝗔𝗦𝗜𝗖𝗦
Measure Basics
If or and conditions in power bi
Creating Calculated Measures
Row context and filter context
Context Transition
Calculate Function
FILTER Function
All and all selected function
ALL EXCEPT function
𝗧𝗜𝗠𝗘 𝗜𝗡𝗧𝗘𝗟𝗟𝗜𝗚𝗘𝗡𝗘𝗖𝗘 (𝗗𝗮𝘁𝗲𝘀)+ 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
CALENDAR AND CALENDARAUTO
DATESMTD
PREVIOUS MONTH MTD
PREVIOUS YEAR MTD
PREVIOUS MONTH SALES
DATESYTD
PREVIOUS YEAR YTD
PREVIOUS YEAR SALES
DATESQTD
PREVIOUS QTD
LAST YEAR QTD
𝗥𝗔𝗧𝗜𝗢 𝗔𝗡𝗗 %𝗢𝗙 𝗧𝗢𝗧𝗔𝗟𝗦
% of Category
% of Subcategory Total
𝗥𝗨𝗡𝗡𝗜𝗡𝗚 (𝗖𝗢𝗠𝗠𝗨𝗟𝗔𝗧𝗜𝗩𝗘) 𝗧𝗢𝗧𝗔𝗟𝗦
Monthly, quarterly, yearly weekly Running Totals
Variance from last week month, years
𝗥𝗲𝗽𝗼𝗿𝘁 𝗙𝗜𝗟𝗧𝗘𝗥𝗦
filter on visual
filter on page
creating dashboards in power bi
using Slicers in charts
======================================
𝗧𝗔𝗕𝗟𝗘𝗔𝗨:
𝗥𝗘𝗤𝗨𝗜𝗥𝗘𝗠𝗘𝗡𝗧: 𝗧𝗮𝗯𝗹𝗲𝗮𝘂 𝗣𝘂𝗯𝗹𝗶𝗰 𝗙𝗿𝗲𝗲 𝗗𝗼𝘄𝗻𝗹𝗼𝗮𝗱, 𝗻𝗼 𝗻𝗲𝗲𝗱 𝘁𝗼 𝗯𝘂𝘆 𝗹𝗶𝗰𝗲𝗻𝘀𝗲
========================================
𝗗𝗮𝘁𝗮 𝗦𝗼𝘂𝗿𝗰𝗲𝘀
Connecting different Data Sources
Working with Extracts
Creating Extracts
Joins in Tableau
Data Blending
𝗧𝗮𝗯𝗹𝗲𝗮𝘂 𝗕𝗮𝘀𝗶𝗰 𝗖𝗼𝗻𝗰𝗲𝗽𝘁𝘀
Tableau Pills
Discrete vs Continuous measures
Dimension Vs Measures
Measure Names and Measure Values
Aggregation and Granularity
Blend vs Join
Top/Bottom Cases
𝗧𝗮𝗯𝗹𝗲𝗮𝘂 𝗖𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻𝘀
Tableau – Operators
IF Function in Tableau
Aggregate Functions in Tableau
Create Calculated Field in Tableau
Logical Functions in Tableau
Case Function in Tableau
Highlight Tables in Tableau
Quick Table Calculation in Tableau
LOD Calculation
𝗧𝗔𝗕𝗟𝗘 𝗰𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻𝘀
Tableau window functions
Tableau joins and relationships
𝗧𝗮𝗯𝗹𝗲𝗮𝘂 𝗙𝗜𝗟𝗧𝗘𝗥𝗦
Enhance User Interaction
Working with Action Filters
Parameters
𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝘁 𝗖𝗵𝗮𝗿𝘁𝘀 𝗶𝗻 𝗧𝗮𝗯𝗹𝗲𝗮𝘂
Line graph in Tableau
Dual Lines Chart in Tableau
Bar Chart in Tableau
Side-by-Side Bars in Tableau
Stacked Bars in Tableau
Histogram in Tableau
Scatter plot in Tableau
Bubble Chart in Tableau
Pie chart in tableau
Total in a crosstab by column in Tableau
Box Plot in Tableau
Bullet Graph in Tableau
Area Chart in Tableau
Heat Map in Tableau
Basic Map in Tableau
Geographical plot in Tableau
Circle views in Tableau
Dual Combination Chart in Tableau
Treemap in Tableau
Funnel Chart in Tableau
Waterfall Chart in Tableau
Gantt Chart in Tableau
𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗗𝗮𝘀𝗵𝗯𝗼𝗮𝗿𝗱𝘀
navigation buttons to dashboards.
Create a Dashboard in Tableau
Tableau – Filters in Dashboard
Filter across data sources in dashboards.
Action Filters in tableau
𝗧𝗮𝗯𝗹𝗲𝗮𝘂 𝗤𝘂𝗶𝗰𝗸 𝗧𝗔𝗕𝗟𝗘 𝗰𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻𝘀
Calculated Fields
Create Table Calculations
Calculating Running Total
Calculate the Moving Average
Calculate the Percentage Of Total
Quick Table Calculations
MTD YTD Table Calculations
Previous Year, Month, Quarter, Week Calculations
Lookup function
𝗥𝗲𝗳𝗲𝗿𝗲𝗻𝗰𝗲 𝗹𝗶𝗻𝗲 𝗶𝗻 𝗧𝗮𝗯𝗹𝗲𝗮𝘂
Reference Band in Tableau
Creating and Removing Hierarchies
𝗣𝗮𝗿𝗮𝗺𝗲𝘁𝗲𝗿𝘀
𝗦𝗲𝘁𝘀
𝗖𝗼𝗺𝗯𝗶𝗻𝗲𝗱 𝗦𝗲𝘁𝘀
𝗚𝗿𝗼𝘂𝗽𝗶𝗻𝗴
======================================
𝗩𝗕𝗔
𝗥𝗘𝗤𝗨𝗜𝗥𝗘𝗠𝗘𝗡𝗧: 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗢𝗳𝗳𝗶𝗰𝗲 𝟮𝟬𝟭𝟯 𝗮𝗻𝗱 𝗮𝗯𝗼𝘃𝗲
======================================
Overview of VBA
Objects in VBA
Modules, classes, userforms
Ranges, cells, Rows, Columns
Deleting, inserting rows, columns
Copying and Pasting Data in VBA
Working with condition, Strings, looping
Opening, creating, Saving Deleting workbooks, worksheets
Combining sheets and workbooks
Using different active x controls on Worksheets
vba String functions (left, right, mid, trim,instr, strcmp)
VBA logical operators (AND, OR, NOT, IF NOT)
VBA select case
VBA LOOPS (do while, for, while, do until)
Using File Open Dialog to Read files
Consolidating files using dir() function
Workbook and Worksheet events
Creating Pivot tables in VBA
Creating Charts in VBA
Auto Filters using VBA
Dealing with Files and Folders
Using Formulas in Sheet using VBA
VBA procedures and Functions
Using VBA to send mails through Outlook
Reading contents from Web
Using timer functions to run programs at specified time
Using Input Box to get data from users
Using Special cells property in VBA
Handling Errors
Importing & Exporting Data from different sources
Error handling
Debugging errors
Getting username & system name using VB
