دسترسی نامحدود
برای کاربرانی که ثبت نام کرده اند
برای ارتباط با ما می توانید از طریق شماره موبایل زیر از طریق تماس و پیامک با ما در ارتباط باشید
در صورت عدم پاسخ گویی از طریق پیامک با پشتیبان در ارتباط باشید
برای کاربرانی که ثبت نام کرده اند
درصورت عدم همخوانی توضیحات با کتاب
از ساعت 7 صبح تا 10 شب
ویرایش:
نویسندگان: BINN. CARTY
سری:
ناشر:
سال نشر: 2022
تعداد صفحات: 1055
[918]
زبان: English
فرمت فایل : PDF (درصورت درخواست کاربر به PDF، EPUB یا AZW3 تبدیل می شود)
حجم فایل: 49 Mb
در صورت تبدیل فایل کتاب EXCEL 2022 COMPLETE GUIDE: The Concise Step-by-Step Practical Guide to Master Everything About Excel in 7 Days or Less به فرمت های PDF، EPUB، AZW3، MOBI و یا DJVU می توانید به پشتیبان اطلاع دهید تا فایل مورد نظر را تبدیل نمایند.
توجه داشته باشید کتاب راهنمای کامل EXCEL 2022: راهنمای عملی مختصر گام به گام برای تسلط بر همه چیز درباره اکسل در 7 روز یا کمتر نسخه زبان اصلی می باشد و کتاب ترجمه شده به فارسی نمی باشد. وبسایت اینترنشنال لایبرری ارائه دهنده کتاب های زبان اصلی می باشد و هیچ گونه کتاب ترجمه شده یا نوشته شده به فارسی را ارائه نمی دهد.
همه چیز اکسل را با این راهنمای قدرتمند بیاموزید! آیا شما تازه وارد Microsoft Excel هستید و حتی نمی دانید از کجا شروع کنید؟ آیا شما یک کاربر متوسط اکسل هستید اما به دنبال یادگیری اکسل تا سطح متخصص هستید؟ آیا به دنبال یک رویکرد ساده و قدرتمند برای تجزیه و تحلیل، وارد کردن، فیلتر کردن، پرس و جو، گزارش و محاسبه داده ها در اکسل هستید؟ آیا سعی کرده اید از عملکرد استاندارد جدول محوری Excel برای انجام این عملیات استفاده کنید و از نتایج ناامید شده اید؟ اگر پاسخ شما به سوالات بالا مثبت است، من یک خبر خوب برای شما دارم. ادامه مطلب این کتاب که برای تمامی سطوح آموزشی (مبتدی، متوسط و متخصص) می باشد، یک راهنمای عمیق در مورد تمامی موارد اکسل است. این کتاب یک درس عمیق مایکروسافت اکسل را برای هر کسی که با این برنامه تازه کار است، مدت طولانی است که از آن استفاده نکرده است یا می خواهد مهارت خود را در استفاده از اکسل ارتقا دهد، ارائه می دهد. هدف این کتاب کمک به شما در تجزیه و تحلیل، دستکاری و ارائه داده ها در اکسل است. به عنوان یک تازه کار که می خواهد یک متخصص اکسل 2022 شود، این کتاب به شما در درک این ویژگی ها کمک می کند. این کتاب برجسته ترکیبی از پنج کتاب مختلف در اکسل است. در زیر به تفکیک مطالب این کتاب می پردازیم کتاب 1: اکسل برای مبتدیان این راهنمای مبتدی به مبتدیان مایکروسافت اکسل پایه ای محکم، پیشینه و تور مبتدی در مورد عملکردها و ویژگی های مختلف بسته بندی شده در اکسل و همچنین ابزارها، منابع و توانایی های مورد نیاز برای انجام ورودی های ساده داده ها و تجسم ها می دهد. هر کسی که پیشینه ای در تجزیه و تحلیل داده ها نداشته باشد، می تواند با درس های ارائه شده در این راهنما، نتایج قالب بندی و تجسم های شگفت انگیزی تولید کند. کتاب 2: فرمول های اکسل
Learn Everything Excel With This Powerful Guide!Are you new to Microsoft Excel and don’t even know where to start from? Are you an intermediate user of Excel but looking for meant to learn excel to the expert level? Are you seeking a simple and powerful approach to analyze, import, filter, query, report, and calculate data in Excel? Have you tried utilizing Excel's standard pivot table functionality to do these operations and been disappointed with the outcomes? If your answer to the above questions is YES, then I got good news for you. Read ON! This book, which is for all learning levels (Beginners, intermediate, and experts), is an in-depth guide on all Excel entails. This book provides an in-depth Microsoft Excel lesson for anybody that is new to the program, hasn't used it in a long time, or wants to upgrade his/her skill in the use of Excel. The objective of this book is to assist you in analyzing, manipulating, and presenting data in Excel. As a novice who wishes to become an Excel 2022 expert, this book will assist you in understanding these features. This outstanding book is a combo of five different books on Excel. Below is the breakdown of the contents of this book Book 1: 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 2: 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 3: 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 4: Power Pivot & Power Query This guide will provide a good foundation for beginners and newcomers to Microsoft Excel's Power Pivot and Power Query Editors. A beginner's tour of the Editors of these programs, as well as the many techniques of importing and cleaning up your data, is included in this guide. Major features, filters, techniques, and loading options, as well as the tools, queries, and functions to achieve the best results for your data analysis, are all packed inside Excel Power Pivot and Power Query. With the lessons in this book, anybody without a background in data analysis or queries may produce remarkable Power Pivot and Power Query outcomes. 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 BOOK 1: 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 2: 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 3: 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 4: EXCEL POWER PIVOT & POWER QUERY CHAPTER ONE INTRODUCING POWER PIVOT Understanding the Power Pivot Internal Data Model Activating the Power Pivot Ribbon Linking Excel Tables to Power Pivot Preparing the Excel Tables Adding your Excel tables to the data model selecting more tables. Creating relationships between your Power Pivot Tables Finding a related Column Modifying your active relationship. Managing existing relationships Using Power Pivot data in reporting Power PivotChart-based reports Loading data from other data sources Loading data from a relational database Loading data from SQL Server Loading data from flat files Loading data from Excel files Loading data from text files Loading data from the clipboard Refreshing and managing external data connections Manually refreshing your Power Pivot data Setting up automatic refreshing Editing your data connection CONCLUSION CHAPTER TWO WORKING DIRECTLY WITH THE INTERNAL DATA MODEL Managing Relationships in the Internet Data Model Removing a Table from the Internal Data Model CONCLUSION CHAPTER THREE ADDING FORMULAS TO POWER PIVOT Enhancing Power Pivot Data with Calculated Columns Creating your first calculated column Formatting your calculated columns Giving names to Calculated Columns Changing the Type of Data Hiding calculated columns from end-users UTILIZING DAX TO CREATE CALCULATED COLUMNS Identifying DAX functions safe for calculated columns DAX Formulas: What You Need to Know Building DAX-driven calculated columns Month sorting in Power Pivot-driven Pivot Tables Nesting functions Fields in Nesting Order Changing the Order of Nesting Understanding calculated measures Calculated Field Implicit Calculated Field Explicit Editing and deleting calculated measures Naming a Measure Make a Change to an Existing Measure Using Cube Functions to Free your data Introduction to cube using Using the cube function Formula Conversion Conclusion CHAPTER FOUR INTRODUCING POWER QUERY Understanding Power Query Basics Understanding query steps Power Query experiences Transformations DataFlows Power Query M Language Viewing the Advanced Query Editor Refreshing Power Query Data Managing existing queries Merging columns using combine Defining a query using the Query Wizard Getting Data from External Sources Importing data from files Getting data from Excel Workbooks Getting data from CSV and text files Importing data from database systems Importing data from relational and OLAP databases Importing data from the Azure database Importing data from ODBC connections to nonstandard databases Getting data from other data systems Managing data source settings Editing data source settings Conclusion CHAPTER FIVE TRANSFORMING DATA WITH POWER QUERY Performing common transformation tasks Removing duplicate records Filling in blank fields Concatenating columns Changing case Finding and replacing specific text Replacing Text Values Replace Number, date, time, or logical values Trimming and cleaning text Extracting characters Extracting the left, right, and middle values Syntax Extracting first and last characters Splitting columns using character markers Unpivoting columns Unpivoting other columns Creating custom columns Understanding data type conversions Understanding the present data type Adding conditional logic to custom columns Grouping and Aggregating Data Selecting groups for Aggregation Selecting an Aggregation Function Conclusion CHAPTER SIX MAKING QUERIES WORK TOGETHER Reusing Query Steps Understanding the Append Feature Creating the needed base queries Appending the data Outcome Understanding the Merge Feature Understanding the Power Query joins Merging queries Outcome Conclusion CHAPTER SEVEN ENHANCING POWER QUERY PRODUCTIVITY Implementing some Power Query Productivity Tips Organizing queries in group Selecting columns in your queries faster Renaming query steps Copying queries to save time Setting a default load behavior Preventing automatic data types changes Types of Data Settings Avoiding Power Query Performance Issues Using views instead of tables Benefits of Using Views Disabling privacy settings to improve performance Avoiding data transfer by negligence Conclusion CHAPTER EIGHT TEN TIPS FOR WORKING WITH POWER QUERY Select the appropriate connection. Filtering early. Performing hard operations last Working on a portion of your data for the time being. Making use of the appropriate data types Moving or deleting columns to reorganize data. Creating a column based on examples Utilize a modular strategy. Future-proofing queries Making functions that may be reused. 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-variable Data Tables Two-Variable Data Table Analyzing Data with Conditional Formatting Instance Highlighting cells that meet some criteria Showing pesky duplicate values Highlighting the top or bottom values in a range Analyzing cells values with color scales Analyzing cells values with data bars Analyzing cells values with icon sets Creating a custom conditional-formatting rule Editing a conditional-formatting rule Removing conditional-formatting rules Summarizing Data with Subtotals How are subtotals calculated? Grouping related data Consolidating Data from Multiple Worksheets Consolidating by position Consolidating by category. Conclusion CHAPTER TWO WORKING WITH DATA-ANALYSIS TOOLS Working with data tables Creating a basic data table Creating a two-input data table Analyzing data with Goal Seek Analyzing Data with Scenarios Create a Scenario Edit a Scenario Delete a Scenario Optimizing Data with solver Understanding solver The advantages of solver Linear Algebra Optimization Education Loading the Solver add-in Adding constraints to Solver Conclusion CHAPTER THREE INTRODUCING EXCEL TABLES What are a Table and its importance? Understanding a table’s structure Building a Table Converting a range to a table Analyzing Table Information Adding a column subtotal Sorting table records Filtering table records Filtering a table with slicers Changing the table’s appearance Clearing a filter Applying a predefined AutoFilter Applying advanced filters Conclusion CHAPTER FOUR GRABBING DATA FROM EXTERNAL SOURCES What is All this about External Data? Importing External Data into Excel Importing data from an Access Table Importing data from a Word table Introducing text file importing Importing a fixed-width text file Importing data from a web page Importing an XML file Querying External Databases Defining a data source Conclusion CHAPTER FIVE ANALYZING TABLE DATA WITH FUNCTIONS The Database Functions: Some General Remarks Summing a column’s Values Counting a Column’s Values Averaging a Column’s Values Multiplying a Column’s Values Conclusion. CHAPTER SIX CREATING AND USING PIVOT TABLES Understanding Pivot Tables Exploring Pivot Table features Importance of Pivot Table Building a pivot table from an Excel Range or Table Creating a Pivot Table from External data Building a Pivot Table from Microsoft Query Building a Pivot Table from a New data connection Refreshing Pivot Table Data 1. Refreshing Pivot Table Data Manually Refreshing Pivot Table data automatically Adding multiple fields to a pivot table area Pivoting a field to a different area Multiple Row Fields Multiple Value Fields Multiple Report Filter Fields Grouping pivot table values Grouping Records Grouping selected data Assigning a name to a group Ungrouping data Error troubleshooting (grouping) Filtering pivot table values Applying a report filter Filtering row or column items Filtering pivot table values Filtering a pivot table with a slicer. Adding a Slicer to a Pivot Table Conclusion CHAPTER SEVEN PERFORMING PIVOT TABLE CALCULATIONS Messing around with Pivot Table summary calculations Changing the Pivot table summary calculations Trying out the difference summary calculation Applying a percentage summary calculation Percentage of Grand Total Percentage of Column total Adding a running total summary calculation Creating an index summary calculation Working with Pivot Table Subtotals Turning off subtotals for a field Displaying multiple subtotals for a field Introducing Custom Calculations Checking out the custom calculation types Inserting a custom calculation field Inserting a custom calculation item Editing a custom calculation Deleting a custom calculation Conclusion CHAPTER EIGHT BUILDING PIVOT CHARTS Introducing the Pivot Chart Understanding Pivot Chart pros and cons Understanding Pivot Chart Limitations Creating a Pivot Chart Creating a Pivot Chart from a Pivot Table Working with Pivot Chart Moving a Pivot Chart to another sheet Filtering a Pivot Chart Changing the Pivot Chart type Adding data labels to your Pivot Chart Sorting the Pivot Chart Adding Pivot Chart Titles Displaying a data table with Pivot Chart. Conclusion CHAPTER NINE UNDERSTANDING EXCEL DATA MODELS Elaboration Dealing with Data Models Creating a relationship between tables Importing related external data tables Import Access Data Import Web Data Import Text Data Import Data from Other Sources Basing a Pivot Table on multiple related tables Managing a Data Model with Power Pivot Enabling the Power Pivot Add-in Adding a table to the Data Model Selecting more tables. Creating a relationship between tables with Power Pivot Creating a Pivot Table or Pivot Chart from your Data Model Activating the data model Importing the data models Defining the Relationship Construct the Pivot Table CHAPTER TEN TRACKING TRENDS AND MAKING FORECASTS Plotting a Best-Fit Trend Lines Calculating Best-Fit Values Plotting Forecasted Values Extending a Linear Trend Calculating Linear Forecasted Values Plotting an Exponential Trend Line Exponential Trend Values Calculation Plotting a Logarithmic Trend Line Plotting a Power Trend Line Plotting a Polynomial Trend Line Creating a Forecast Sheet. CHAPTER ELEVEN ANALYZING DATA USING STATISTICS Counting Things Counting Numbers Counting Nonempty cells Counting empty cells Counting cells that match criteria Counting cells that match multiple criteria Counting Permutations Counting combinations Averaging Things Calculating an average Calculating a conditional average Calculating an average based on multiple conditions Getting the average of all numbers that meet a certain condition Getting the average of all numbers that meet two or more conditions. Calculating the median, mode, variance, standard deviation Finding the Rank In Source Data, Using RANK.EQ and RANK.AVG Determining the Nth Largest and Smallest Value Creating a Frequency Distribution using Groups Finding the correlation CHAPTER TWELVE ANALYZING DATA USING DESCRIPTIVE STATISTICS Loading the Analysis ToolPak Calculating a Moving Average Determining Rank and Percentile Generating Random Numbers Creating a Frequency Distribution CHAPTER THIRTEEN ANALYZING DATA USING INFERENTIAL STATISTICS Data Sampling Using t-Testing Tools Determining the Regression Correlation Calculation Calculating the Covariance Using Anova Tools Performing an f-test CHAPTER FOURTEEN TEN THINGS YOU OUGHT TO KNOW ABOUT STATISTICS Descriptive statistics are simple to understand. Deviation from the mean Explains the concept of dispersion. Standard Deviations Describe Dispersion An Observation is an Observation A Sample is a Subset of Values Inferential Statistics Are Interesting, but They're Also Complicated Probability Distributions aren't always difficult to understand. Uniform Distribution Normal Distribution Parameters aren't as complicated as they seem. Skewness and Kurtosis Describing a Probability Confidence Intervals May Appear Difficult at First, but They Are Beneficial. CHAPTER FIFTEEN TEN WAYS TO ANALYZE FINANCIAL DATA Calculating Future Value Calculating Present Value Calculating the positive and negative cash flows Calculating non-periodic future cash flows Things to note down when using these functions Calculating Loan Payments Calculating the Principal and Interest on a Loan Payment Cumulative Loan Principal and Interest Calculation Identifying the Minimum Interest Rate Determining the Internal Rate of Return CHAPTER 16 HOW TO IMPROVE YOUR PIVOT TABLE GAME Activating and deactivating the Pivot Table Fields Task Pane Change the Pivot Table Fields Task Pane Layout Showing the Details Behind the Data in Pivot Tables Use the Pivot Table Style Making Your Own Pivot Table Style Preserve a Pivot Table Renaming a PivotTable. Disable Grand Totals Workbooks with Pivot Tables Can Be Resized. Using a Pivot Table Value in a Formula Conclusion INDEX