MASTERING EXCEL & POWER BI: Learn Everything About Microsoft Excel & Power BI for Data Modelling, Analysis, Visualization & Transformation

Learn Everything Excel & Power BI With This Powerful Guide! This book is an in-depth guide on all that Excel and Power BI entails. This book provides in-depth knowledge of Microsoft Excel and Power BI lessons for anybody that is new to these programs, hasn't used them in a long time, or wants to upgrade his/her skill in their uses. The objective of this book is to assist you in analyzing, manipulating, presenting, and transforming data in Excel and Power BI. This outstanding book is made up of five different books . Below is the breakdown of the contents of this book: Book 1 : Microsoft Power BI This book covers the fundamentals and most commonly used features of Power BI. It is an in-depth guide on all you need to know about data modeling and how to reliably develop data models using Power BI. It includes how to connect data from a variety of sources, comprehends data, build and manage data connections, and design data models. As a reader, you will learn the step-by-step process of how to create relationships and develop a data model using data modeling and navigation approaches before creating new measures and conducting custom computations using modeling features. Book 2: Excel for Beginners This beginner's guide will give newbies to Microsoft Excel a solid foundation, background, and a beginner’s tour on the various functionalities and features packed in Excel, as well as the tools, resources, and abilities needed to perform simple data entries and visualizations. Anyone without a background in data analysis is capable of producing amazing formatting results and visualizations with the lessons provided in this guide. Book 3: Excel Formulas & Functions This comprehensive guide's goal is to take you by the hand and show you how to utilize all of Excel's sophisticated formulas and functions. Learn how to utilize the tools that have been handed to you and when to use them. The many examples in the book make it an ideal instructional tool for all Excel users who wish to learn how to interpret, write, and utilize formulas. "Microsoft Excel Formulae and Functions" is an excellent reference for many of the program's complex formulas and functions for intermediate and advanced users. Book 4: Pivot Table & Pivot Chart Gain control of your data and your company by using Excel pivot tables and pivot charts to create powerful, dynamic reports in minutes rather than hours. Even if you've never used a pivot table before, this book will show you how to make use of all of its amazing flexibility and analytical power. Book 5: Data Analysis with Excel Excel's versatility allows it to be used for business, work data, as a calculator, a data converter, and even as a display sheet for evaluating data. In practice, it has evolved into a critical decision-making aid in the workplace. Excel is all around you, and you rely on it more than you realize or like to admit. With this book in your hands, you will discover all of the tactics for using Excel data analysis to its full potential, making your work much simpler or increasing your chances of landing a job! ... and a lot more! CONTENTS OVERVIEW OF MICROSOFT EXCEL AND POWER BI Differences Between Power BI and Excel BOOK 1 MICROSOFT POWER BI INTRODUCTION CHAPTER ONE CONFIGURING POWER BI TOOLS TECHNICAL REQUIREMENTS INSTALLING AND CONFIGURING POWER BI DESKTOP INSTALLING FROM MICROSOFT STORE DOWNLOAD AND INSTALL FROM THE DOWNLOAD CENTER USING COMMAND-LINE OPTIONS DURING INSTALLATION WHAT IS THE ON-PREMISES DATA GATEWAY? CONSIDERATION IN USING ON-PREMISES DATA GATEWAY CREATING AN ON-PREMISES DATA GATEWAY FEATURES OF THE ON-PREMISES DATA GATEWAY WHAT IS ANALYZE IN EXCEL? INSTALLING ANALYZE IN EXCEL FROM THE POWER BI SERVICE SUMMARY CHAPTER TWO ACCESSING AND RETRIEVING DATA TECHNICAL REQUIREMENT WHAT IS M? WHY LEARN M? VIEWING AND ANALYZING M FUNCTIONS MANAGING QUERIES AND DATA SOURCES MANAGING DATA SOURCES (POWER QUERY) DATA SOURCE SETTINGS (POWER QUERY ON EXCEL) MANAGING DATA SOURCES (EXCEL) HOW TO CONNECT TO A DATA SOURCE DATA SOURCE SETTINGS ADDING A DATA SOURCE REMOVING A DATA SOURCE IMPORTING DATA USING DIRECTQUERY SELECTING AND RENAMING COLUMNS TRANSFORMING AND CLEANSING DATA CREATING CUSTOM COLUMNS COMBINING AND MERGING QUERIES SELECTING COLUMN DATA TYPES VISUALIZING THE M LIBRARY DIAGNOSING QUERIES SUMMARY CHAPTER THREE BUILDING A POWER BI DATA MODEL TECHNICAL REQUIREMENTS DESIGNING A DATA MODEL IMPLEMENTING A DATA MODEL CREATING RELATIONSHIPS CONFIGURING MODEL METADATA HIDING COLUMNS AND TABLES ENRICHING A MODEL WITH DAX CALCULATED COLUMNS CREATING HIERARCHIES AND GROUPS Creating Groups DESIGNING COLUMN LEVEL SECURITY LEVERAGING AGGREGATION TABLES Creating Aggregation tables Managing Aggregations Table Configuring Aggregation Table CONCLUSION CHAPTER FOUR AUTHORING POWER BI REPORTS TECHNICAL REQUIREMENTS BUILDING RICH AND INTUITIVE REPORTS Keeping Track of Your Reports FILTERING AT DIFFERENT SCOPES INTEGRATING CARD VISUALIZATIONS Creating Cards Creating a card using the Report Editor Creating a card using the QA Question Box USING SLICERS Creating A Slicer in Power Bi Desktop CONTROLLING VISUAL INTERACTIONS First, select a visualization UTILIZING GRAPHICAL VISUALIZATIONS SELECTING VISUAL STYLES WATERFALL CHART FOR VARIANCE ANALYSIS CREATING TABLE AND MATRIX VISUALS ENHANCING REPORTS PAGE REQUIREMENTS FOR DRILL-THROUGH REPORTS ENABLE CORTANA INTEGRATION AND QA Create Featured QA Questions PARAMETERIZED QA REPORT FORMATTING REPORTS FOR PUBLICATION DESIGNING MOBILE LAYOUTS Power BI Dashboard on Mobile Devices Power BI Reports in Mobile Devices Designing Power BI Reports for Mobile Devices CREATING PAGINATED REPORTS Step One: Starting Report Builder Step Two: Going through the Chart Wizard Step Three: Designing your Reports Step Four: Saving your report to the Report Server CONCLUSION CHAPTER FIVE WORKING IN THE SERVICE Technical Requirements ADDING ALERTS AND SUBSCRIPTIONS Helpful hints for troubleshooting Receiving notifications Controlling alerts From the tile's surface From the Power BI configuration menu SUBSCRIBING POWER BI REPORTS DEPLOYING CONTENT FROM EXCEL AND SSRS PIN SSRS REPORT ITEMS STREAMING DATASETS AUTHORING DATAFLOWS CONCLUSION CHAPTER SIX GETTING SERIOUS WITH DATA INTELLIGENCE TECHNICAL REQUIREMENTS BUILDING A DATE DIMENSION TABLE PREPARING THE DATE DIMENSION VIA THE QUERY EDITOR DEVELOPING ADVANCED DATE INTELLIGENCE MEASURES SIMPLIFYING DATE INTELLIGENCE WITH DAX AND CALCULATED TABLES LEVERAGING CALCULATION GROUPS CONCLUSION CHAPTER SEVEN PARAMETERIZING POWER BI SOLUTIONS TECHNICAL REQUIREMENTS FILTERING REPORTS DYNAMICALLY LEVERAGING QUERY PARAMETERS PARAMETERIZING A DATA SOURCE TO CONNECT TO DIFFERENT SERVERS AND DATABASES. WORKING WITH TEMPLATES CONVERTING STATIC QUERIES TO DYNAMIC FUNCTIONS CAPTURING USER SELECTIONS WITH PARAMETER TABLES FORECASTING WITH WHAT-IF-ANALYSIS CONCLUSION CHAPTER EIGHT IMPLEMENTING DYNAMIC USER-BASED VISIBILITY IN POWER BI TECHNICAL REQUIREMENTS CAPTURING CURRENT USER CONTEXT IMPLICIT FILTER CONTEXT EXPLICIT FILTER CONTEXT ROW CONTEXT QUERY CONTEXT DEFINING RLS ROLES AND FILTER EXPRESSIONS How to Define RLS Roles and Filter Expressions DESIGNING DYNAMIC SECURITY MODELS BUILDING DYNAMIC SECURITY FOR DIRECTQUERY Getting Ready DISPLAYING THE CURRENT FILTER CONTEXT AVOIDING MANUAL USER CLICKS Getting Ready CONCLUSION CHAPTER NINE APPLYING ADVANCED ANALYTICS AND CUSTOM VISUALS TECHNICAL REQUIREMENTS INCORPORATING ADVANCED ANALYTICS Clustered Column Chart Line Chart ENRICHING CONTENT WITH CUSTOM VISUALS AND QUICK INSIGHTS Getting ready Let’s get started Scoped quick insights CREATING GEOSPATIAL MAPPING WITH ARCGIS MAPS Getting ready Let’s get started Customer clustering Map BUILDING ANIMATION AND STORYTELLING Getting ready Scatter chart with play axis ArcGIS map timeline EMBEDDING STATISTICAL ANALYSES Getting ready Let’s get started GROUPING AND BINNING DETECTING AND ANALYZING CLUSTERS Getting ready Let’s get started Analyze the clusters FORECASTING AND ANOMALY DETECTION Getting ready Lets’ get started. Monthly forecast (date hierarchy) CONCLUSION CHAPTER TEN ADMINISTERING AND MONITORING POWER BI TECHNICAL REQUIREMENTS Getting started IMPORTING AND VISUALIZING DYNAMIC MANAGEMENT VIEW (DMV) DATA PROVIDING DOCUMENTATION Getting ready Getting started ANALYZING SSAS TABULAR DATABASES AND GATEWAYS Getting ready Getting started ANALYZING EXTENDED EVENTS VISUALIZING LOG FILE DATA Getting ready Getting started Power BI Audit Log Integration CONCLUSION CHAPTER ELEVEN ENHANCING AND OPTIMIZING EXISTING POWER BI SOLUTIONS TECHNICAL REQUIREMENTS ENHANCING DATA MODEL SCALABILITY AND USABILITY Getting ready Getting started IMPROVING DAX MEASURE PERFORMANCE Getting ready Getting started PUSHING QUERY PROCESSING BACK TO SOURCE SYSTEMS Getting ready Getting started STRENGTHENING DATA IMPORT AND INTEGRATION PROCESSES How you can do it ISOLATING AND DOCUMENTING DAX EXPRESSIONS IMPROVING DATA LOAD SPEEDS WITH INCREMENTAL REFRESH CONCLUSION CHAPTER TWELVE TECHNICAL REQUIREMENTS PREPARING FOR CONTENT CREATION AND COLLABORATION Follow the procedures for deploying Power BI MANAGING CONTENT BETWEEN ENVIRONMENTS SHARING CONTENT WITH COLLEAGUES CONFIGURING WORKSPACES CONFIGURING ON-PREMISES GATEWAY DATA CONNECTIONS PUBLISHING APPS PUBLISHING REPORTS TO THE PUBLIC INTERNET ENABLING THE MOBILE EXPERIENCE CONCLUSION CHAPTER THIRTEEN TECHNICAL REQUIREMENTS INTEGRATING SSRS AND EXCEL Follow the steps below to use SSRS: MIGRATING FROM POWER PIVOT FOR EXCEL DATA TO POWER BI Steps to Go About It: ACCESSING AND ANALYZING POWER BI DATASETS IN EXCEL BUILDING POWER BI REPORTS INTO POWERPOINT PRESENTATIONS CONNECTING TO AZURE ANALYSIS SERVICES HOW TO USE POWER BI WITH MICROSOFT FLOW AND POWERAPPS How to use MS Flow to stream a Power BI dataset CONCLUSION BOOK 2: EXCEL FOR BEGINNERS CHAPTER ONE INTRODUCING EXCEL UNDERSTANDING WHAT EXCEL IS USED FOR LOOKING AT WHAT’S NEW IN EXCEL 2022 UNDERSTANDING WORKBOOKS AND WORKSHEETS MOVING AROUND A WORKSHEETS Navigating with your keyboard Navigating with your Mouse USING THE RIBBON Customizing the Ribbon Ribbon Tabs Inserting a new tab Creating a new group Adding a command Changing the names of ribbon tabs, groups, and commands Changing the position of tabs, groups, and instructions Contextual Tabs Types of Command on the Ribbon Accessing the Ribbon by using your Keyboard USING SHORTCUT MENUS Shortcuts for formatting data Data entry shortcuts Data View and Navigation Data selection CUSTOMIZING YOUR QUICK ACCESS TOOLBAR WORKING WITH DIALOG BOXES USING TASK PANES CREATING YOUR FIRST EXCEL WORKBOOK Getting Started on Your Worksheet Filling in the Month Names Entering The Sales Data Summing the Values Making Your Worksheet Look a bit Fancier Creating a Chart Formatting the Numbers Printing your Worksheet Printing from a Selection Using a Print Area Saving Workbook CHAPTER TWO ENTERING AND EDITING WORKSHEET DATA Exploring Data Types Numeric values Text Entries Entering Text and Values into your Worksheets Entering Numbers and Text Entering Dates and Times into your Worksheets Entering date and time values MODIFYING CELL CONTENTS Deleting the contents of a cell Replacing the contents of a cell Learning some handy data-entry techniques Automatically moving the selection after entering data Selecting a range of input cells before entering data Using CTRL + Enter to place information into multiple cells Changing modes Entering decimal points automatically Using Autofill to enter a series of values Using Autocomplete to automate data entry Forcing text to appear on a new line within a cell Using Autocorrect for shortcut data entry Entering numbers with fractions Using a form for data entry Applying Number Formatting Using shortcut keys to format numbers Formatting numbers by using the Format Cells dialog box Add your own custom number formats. CHAPTER THREE PERFORMING BASIC WORKSHEET OPERATIONS Learning the Fundamentals of Excel Worksheet Working with Excel windows Moving and Resizing Windows To move a window Switching Windows Closing Windows Activating a worksheet Adding a new worksheet to your workbook Deleting a worksheet Changing the name of a worksheet Changing the tab color Rearranging your worksheet Hiding and unhiding a worksheet CONTROLLING THE WORKSHEET VIEW Zooming in or out for a better view Viewing a worksheet in multiple windows Comparing sheets side by side Splitting the worksheet window into panes Keeping the titles in view by freezing panes Monitoring cells with a watch Window WORKING WITH ROWS AND COLUMNS Inserting Column Deleting rows and columns Changing column widths Changing row heights Hiding rows and columns CHAPTER FOUR WORKING WITH EXCEL RANGES AND TABLES UNDERSTANDING CELLS AND RANGES Select Single Cell Range Selecting complete columns Selecting complete rows Selecting noncontiguous ranges Selecting multi-sheet ranges Selecting special types of cells Selecting cells by searching COPYING OR MOVING RANGES Copying by using Ribbon commands Copying by using shortcut keys Copying or moving by using drag-and-drop Copying to adjacent cells Using the Office Clipboard to paste Pasting in special ways Using the Paste Special Dialog box Skipping blanks when pasting USING NAMES TO WORK WITH RANGES Creating range names in your workbooks Using the Create Names from Selection dialog box ADDING COMMENTS TO CELLS Formatting comments Changing a comment’s shape Resizing comments Hiding and showing comments Editing comments Deleting comments WORKING WITH TABLES Understanding a table’s structure Creating a table Sorting a table Filtering a table Filtering a table with slicers Changing the table’s appearance CHAPTER FIVE FORMATTING WORKSHEETS GETTING TO KNOW THE FORMATTING TOOLS Using the formatting tools on the Home tab Using the Format Cells dialog box Formatting your worksheets Using fonts to format your worksheet Wrapping or Shrinking text to fit the cell Purpose of Wrap Text Shrinking text Merging worksheet cells to create additional text space Displaying text at an angle Using colors and shading Adding border lines Using conditional formatting Using graphical conditional formats Using data bars Using color scales Using icon sets Creating formula-based rules Understanding relative and absolute references Absolute references Conditional formatting formula examples Identifying weekend days Highlighting a row based on a value Displaying alternate-row shading Creating checkerboard shading Working with Conditional formats USING NAMED STYLES FOR EASIER FORMATTING Applying styles Modifying an existing style Creating new styles Merging styles from other workbooks Controlling styles with templates UNDERSTANDING DOCUMENT THEMES Applying a theme Customizing a theme CHAPTER SIX UNDERSTANDING EXCEL FILES AND TEMPLATES Creating a new workbook Opening an existing workbook Choosing your file display preferences Enabling AutoRecover in Excel Recovering versions of the current workbook Recovering unsaved work Configuring Auto recover Password-Protecting a Workbook Organizing your files OTHER WORKBOOK INFO OPTIONS Protect Worksheet options Check for issues options Manage workbook option Browser view options Compatibility mode section Closing Workbooks Safeguarding your work Working with templates Exploring Excel templates Viewing templates Creating a workbook from a template Creating a worksheet template Editing your template Resetting the default workbook Using custom workbook templates Creating custom templates Saving your custom templates CHAPTER SEVEN PRINTING YOUR WORK Doing Basic Printing Changing your Page View Normal view Page layout view Page break preview ADJUSTING COMMON PAGE SETUP SETTINGS Choosing your printer Specifying what you want to print Changing page orientation Specifying paper size Printing multiple copies of your reports Adjusting the page margins Understanding page breaks Inserting a page break Removing manual page breaks Printing row and column titles Scaling printed output Printing cell gridlines Using a background image Adding a header or a footer to your reports Inserting a header Inserting a footer Understanding header and footer element codes Exploring other header and footer options EXPLORING OTHER PRINT-RELATED TOPICS Copying page setup settings across sheets Preventing certain cells from being printed Preventing objects from being printed Creating custom views of your worksheet Creating PDF files. Excel print's Limitations CHAPTER EIGHT CUSTOMIZING THE EXCEL USER INTERFACE About the Quick Access Toolbar Customizing the Quick Access Toolbar Adding new commands to the Quick Access Toolbar Other Quick Access Toolbar actions Customizing the Ribbon Why you may want to customize the ribbon What can be customized What cannot be customized How to customize the ribbon Creating a new tab Creating a new group Adding commands to a new group Resetting the ribbon. CHAPTER NINE GETTING STARTED WITH EXCEL CHARTS What’s Chart How Excel Handle charts Embedded charts Chart sheets Parts of a chart Chart limitations BASIC STEPS FOR CREATING A CHART Creating the chart Switching the row and column orientation Changing the chart type Applying chart layout Adding and deleting chart elements Formatting chart elements MODIFYING AND CUSTOMIZING CHARTS Moving and resizing a chart Converting an embedded chart Copying a chart Deleting a chart Copying a chart formatting Renaming a chart Renaming a data series in a chart Printing charts UNDERSTANDING CHART TYPES Choosing a chart type Column charts Bar charts Line charts Pie charts XY (scatter) charts When in doubt, use a scatter chart when: Area charts Radar charts Surface charts Bubble charts Stock charts NEW CHART TYPES FOR EXCEL Histogram charts Pareto charts Waterfall charts Box whisker charts Treemap charts Sunburst charts Funnel charts Map charts CHAPTER TEN USING ADVANCED CHARTING TECHNIQUES SELECTING CHART ELEMENTS Selecting with the mouse Selecting with the Keyboard Selecting with the chart element control EXPLORING THE USER INTERFACE CHOICES FOR MODIFYING CHART ELEMENTS Using the format task pane Using the chart customization buttons Using the ribbon Using the Mini toolbar Modifying the chart area Modifying the Plot area Working with Titles in a chart Working with Legends Working with Gridlines Modifying the Axes WORKING WITH DATA SERIES Deleting or hiding a data series Adding a new data series to a chart Changing data used by a series Using the Edit series dialog box Editing the Series formula Displaying data labels in a chart Handling missing data Adding error bars Creating combination charts Displaying a data table Creating Chart Templates. CHAPTER ELEVEN CREATING SPARKLINE GRAPHICS What is a Sparkline Sparkline Types Importance of Using Sparklines Creating Sparklines Customizing Sparklines Handling hidden or missing data Changing Sparkline types Changing Sparkline colors and line width Merging and Sizing Sparklines cells Highlighting certain data points Grouping and Ungrouping Sparklines Deleting Sparklines Adjusting Sparkline axis scaling Specifying a date axis Auto-Updating Sparklines Displaying a Sparkline for a Dynamic Range SUMMARY CHAPTER TWELVE VISUALIZING WITH CUSTOM NUMBER FORMATS AND SHAPES Visualizing with Number Formatting Doing basic number formatting Using the formal cell dialog box to format numbers Using shortcuts keys to format numbers GETTING FANCY WITH CUSTOM NUMBER FORMATTING Formatting numbers in thousands and millions Hiding and suppressing zeros Applying custom format colors Formatting dates and time Using a symbol to enhance reporting Now let’s use the symbols in our formatting USING SHAPES AND ICONS AS VISUAL ELEMENTS Inserting a shape Inserting SVG icon graphics Enhancing Excel reports with shapes Layering shapes to save space Constructing your infographic widgets with shapes Creating dynamic labels Creating linked pictures Some do ask, the importance of creating a linked picture and when is it needed? USING SMARTART AND WORDART SmartArt basics WordArt basics WORKING WITH OTHER GRAPHICS TYPES About graphic files Inserting screenshots USING THE EQUATION EDITOR CONCLUSION BOOK 3: EXCEL FORMULAS FUNCTIONS CHAPTER ONE INTRODUCING FORMULAS AND FUNCTIONS UNDERSTANDING FORMULA BASICS Using Operators in Formulas Using functions in your formulas Examples of formulas that use functions Functions arguments More about functions Priority for the master operator Show Formulas Fix Formulas Use absolute references wherever possible Apply conditional formatting to your documents Validate your data ENTERING FORMULAS INTO YOUR WORKSHEETS Entering formulas manually Entering formulas by pointing Pasting range names into formulas Inserting functions into formulas Function entry tips Editing Formulas USING CELL REFERENCES IN FORMULAS Changing the types of your references Referencing cells outside the worksheet Referencing cells in other worksheets Referencing cells in other workbooks USING FORMULAS IN THE TABLE Summarizing data in a table Using formulas within a table Referencing data in a table CORRECTING COMMON FORMULA ERRORS Handling Circular references So, how do you discover a circular reference? Specifying when formulas are calculated USING ADVANCED NAMING TECHNIQUES Using names for constants Using range intersections Applying names to existing references WORKING WITH FORMULAS Not hard-coding values Using the formula bar as a calculator Making an exact copy of a formula Converting formulas to values CHAPTER TWO USING FORMULAS FOR COMMON MATHEMATICAL OPERATIONS Calculating Percentages Calculating percent of goal Calculating percent variance Calculating percent variance with negative values Calculating a percent distribution Calculating a running total Applying a percent variance with negative values Dealing with divide-by-zero errors ROUNDING NUMBERS Rounding numbers using formulas Rounding to the nearest penny Rounding to significant digits However, there are a few guidelines that you should follow: COUNTING VALUES IN A RANGE Explanation: Explanation: USING EXCEL’S CONVERSION FUNCTIONS. This function has the following arguments: CHAPTER THREE USING FORMULAS TO MANIPULATE TEXT WORKING WITH TEXT Using Text Function Joining text strings Settings text to sentence case Removing spaces from a text string Extracting parts of a text string Finding a particular character in a text string Substituting text strings Counting specific characters in a cell How does this work? Adding a line break within a formula Cleaning strange characters from text fields Adding leading zeros in Excel Using the DOLLAR function CHAPTER FOUR USING FORMULAS WITH DATES AND TIMES UNDERSTANDING HOW EXCEL HANDLES DATES AND TIMES How Excel stores dates: How Excel stores time: Understanding dates serial numbers Example of a serial number in Excel Date and Time Entering dates Using Two-Digit Years to Enter Dates Understanding time serial numbers ENTERING TIMES FORMATTING DATES AND TIMES PROBLEMS WITH DATES Excel’s leap year bug Pre-1900 dates Inconsistent date entries USING EXCEL’S DATE AND TIME FUNCTIONS Getting the current date and time Calculating age Calculating the number of days between two dates DATEDIF(start_date, end_date, "d"). Calculating the number of workdays between two dates USING NETWORKDAYS.INTL Generating a list of business days excluding holidays Count Workdays excluding Holidays and Particular Weekends Extracting parts of a date Calculating the number of years and months between dates Converting dates to Julian dates formats Returning the last date of a given month To find the last date of a given month, follow the steps below: USING THE EOMONTH FUNCTION Calculating the calendar quarter for a date What this formula entails Here's what it's all about: Calculating the fiscal quarter for a date What is the mechanism behind it? Returning a fiscal month from a date Calculating the date of the Nth weekday of the month Calculating the date of the last weekday of the month Calculating elapsed time Calculating the amount of time that has passed in years Months of elapsed time Weeks that have passed The amount of time that has passed in days. In working days, the amount of time that has passed. Time elapsed in hours Time elapsed in minutes In seconds, the time has elapsed. Rounding time values Converting hours, minutes, or seconds to a decimal. Adding hours, minutes, or seconds to a time. CHAPTER FIVE USING FORMULAS FOR CONDITIONAL ANALYSIS UNDERSTANDING CONDITIONAL ANALYSIS Checking if a simple condition is met Checking for multiple conditions Validating conditional data Checking if Condition1 AND Condition2 are met Checking if Condition1 OR Condition2 are met PERFORMING CONDITIONAL CALCULATIONS Using SUMIFS Summing all values that meet a certain condition Summing greater than zero Summing all values that meet two or more conditions Based on OR logic: Based on AND logic: Summing if values fall between a given date range Getting a count of values that meet a certain condition Getting a count of values that meet two or more conditions Finding nonstandard characters Getting the average of all numbers that meet a certain condition EXAMPLE: Getting the average of all numbers that meet two or more conditions. CHAPTER SIX USING FORMULAS FOR MATCHING AND LOOKUPS Introducing Lookup Formulas Leveraging Excel’s Lookup Functions Looking up an exact value based on a left lookup column Looking up an exact value based on any lookup column Looking up values horizontally There are things you need to consider when applying this vector Lookup. FINDING THE CLOSEST MATCH FROM A LIST OF BANDED VALUES Finding the closest match with INDEX and MATCH functions Looking up values from multiple tables Looking up a value based on a two-way matrix Finding a value based on multiple criteria Finding the last value in a column Finding the last number using LOOKUP. CHAPTER SEVEN USING FORMULAS FOR FINANCIAL ANALYSIS Performing common business calculations Calculating markup Steps in doing so: Calculating EBIT and EBITDA Calculating cost of goods sold Calculating return on assets Calculating return on equity Keywords Calculating break-even The formula for break-even Calculating the average customer lifetime value The formula is as follows: How do you figure out a company's LTV? Calculating employee turnover Leveraging Excel’s Financial functions CONVERTING INTEREST RATES Computing effective rate with FV Creating an amortization schedule CALCULATING DEPRECIATION Calculating accelerated depreciation Calculating the net present value Calculating the positive and negative cash flows CALCULATING AN INTERNAL RATE OF RETURN Calculating non-periodic future cash flows Things to note down when using these functions Performing financial forecasting. CHAPTER EIGHT USING FORMULAS FOR STATISTICAL ANALYSIS Working with weighted averages Procedures in carrying out weighted average. Smoothing Data with moving averages Where to find this tool: Applying exponential smoothing to volatile data Using functions to create descriptive statistics How do you calculate this? Getting the largest or smallest value Calculating mean, median, and mode Identifying statistical outliers with an interquartile range Creating a frequency distribution Steps in doing this: An alternative to the Frequency function CHAPTER NINE USING FORMULAS WITH TABLES AND CONDITIONAL FORMATTING Highlighting cells that meet certain criteria Follow the steps below: Explanation Highlighting values that exist in List1 but not List2 Highlighting values that exist in List1 and List2 Highlighting based on Dates The first process is by using built-in conditional formatting Highlighting days between two dates Highlighting days between a due date. Conclusion. CHAPTER TEN UNDERSTANDING AND USING ARRAY FORMULAS Understanding Array Formulas To enter a multi-cell array formula, follow these steps: There are a few characteristics that differentiate multi-cell array formulae: A single cell array formula 1. A formula for a single-cell array: 2. An Excel formula for a multi-cell array: 3. Returning a multi-cell array utilizing an Excel array function Creating an array constant UNDERSTANDING THE DIMENSIONS OF AN ARRAY One dimensional horizontal array One dimensional vertical array Naming array constants WORKING WITH ARRAY FORMULAS Entering an array formula Selecting an array formula Editing an array formula Expanding or contracting a multi-cell array USING MULTICELL ARRAY FORMULAS Creating an array constant from values in a range Performing operations on an array Counting characters in a range Summing the three smallest values in a range Here's a faster and more practical array formula: Counting text in a range Eliminating intermediate formulas CHAPTER ELEVEN MAKING YOUR FORMULAS ERROR-FREE Finding and correcting formula errors Mismatched parenthesis Cells are filled with hash marks Blank cells are not blank Extra Space Characters Formulas returning an error #DIV/0! Errors #N/A errors #NAME? errors #NULL! errors #REF! errors #Value! Errors Operator Precedence problems =1+A1*A2 =1+(A1*A2) =-32% =0-32% Formulas are not calculated “Phantom link” errors Using Excel Auditing tools Viewing formulas TRACING CELL RELATIONSHIPS Identifying precedents Identifying dependents Fixing circular reference errors Using the background error-checking feature Using Formula Evaluator Searching and Replacing Spell-checking your worksheets Using Autocorrect BOOK 4: EXCEL PIVOT TABLES DASHBOARDS CHAPTER ONE INTRODUCTION TO PIVOT TABLES What are Pivot Tables? What are the main parts of a Pivot Table? Importance of Pivot Table Conclusion CHAPTER TWO BUILDING A BASIC PIVOT TABLE CHART First, choose the cells for the pivot table: Summarizing Numbers Below are the summarize options that you can use alongside the function. How to Drill-Down Pivot Table Data When utilizing slicers, use extreme caution! Watch out for the source data Drill-down PowerPivot Data Model Adding Additional Rows (categories) to your Pivot Table Charts: How to create a basic Pivot Table Chart Conclusion CHAPTER THREE DISPLAYING PERCENTAGES Percentage of Grand total Percentage of Column Total Conclusion CHAPTER FOUR RANKING RESULTS AND DISPLAYING AVERAGES Displaying Averages How does this calculation work? Below are the procedures in creating pivot tables and measures (showing average): Measures: Implicit vs. Explicit Ranking Data In Source Data, Using RANK.EQ and RANK.AVG In a Pivot Table, create a separate Rank Column. Conclusion CHAPTER FIVE SLICERS (INTERACTIVE ANALYSIS) AND ADVANCED FILTERING Timeline Slicer Adding a Timeline to a pivot table Slicer Adding a Slicer to a Pivot Table Advanced Filtering Create a calculated field Using one Pivot Table to generate many pivot-tables Hiding and unhiding subtotals Refresh your Data Conclusion CHAPTER SIX INTRODUCTION TO DASHBOARDS Adding Multiple Pivot Tables to a Worksheet Formatting the Dashboard. Adding Charts to the Dashboard Parameters for Charts Notes: Conclusion CHAPTER SEVEN ADDING SLICERS AND PERFORMANCE SYMBOLS TO YOUR DASHBOARD Slicers Performance Symbols (up/down arrows and other indicators) Conclusion CHAPTER EIGHT REFRESHING PIVOT TABLE AND DASHBOARD DATA 1. Existing Data, the values vary considerably, while the numbers of rows of data remain constant: 2. The size of the data in Excel changes 3. Refresh automatically when the Pivot Table is opened. Conclusion CHAPTER NINE PROTECTING YOUR DASHBOARD Hiding your Pivot Table Source Data Protecting the Dashboard or any other Worksheet Conclusion CHAPTER TEN GROUPING PIVOT TABLE DATA Grouping Records Group the selected data. Name a group. Ungroup data that have already been grouped. Errors Troubleshooting (Grouping) Multiple Consolidation Ranges and Grouping Count Function Count the field Conclusion CHAPTER ELEVEN CALCULATED FIELDS IN PIVOT TABLES COGS = Total Sales multiplied by a percentage Adding a basic calculated field Changing the display of formula error messages Removing or changing calculated fields Conclusion CHAPTER TWELVE CREATING PIVOT TABLES FROM IMPORTED FILES – USING THE DATA MODEL Activate the data model Import the data models Define the Relationship Construct the Pivot Table Conclusion CHAPTER THIRTEEN TROUBLESHOOTING Pivot Table displaying duplicate values Formula – LEN Formula – TRIM A few remarks about the TRIM Function: Conclusion CHAPTER FOURTEEN TROUBLESHOOTING How to resolve common Pivot Table Errors. Correcting the Source Reference not Valid Error in a Pivot Table Old items in the Drop-Down Menus Overlap Errors How to fix it Solution One Solution Two Solution Three How Do I Figure Out Which Pivot Tables Overlap? CONCLUSION BOOK 5 EXCEL DATA ANALYSIS CHAPTER ONE LEARNING BASIC DATA-ANALYSIS What is Data Analysis Anyway? Sorting Filtering Conditional Formatting Charting Tables Pivot Tables What-If Analysis Solver Analysis Toolkits Descriptive Statistics Cooking raw data Cleaning of data Exploration of Data Using Pivot Tables Dealing with data Defining Requirements for Data Data Gathering Processing of Data Cleaning of data Analyzing the data Communication Building data models Performing what-if analysis One-varia