Productivity Boosting Aspects of Microsof Excel

Description

Making better-informed business decisions to increase productivity and profitability is arguably becoming most difficult task for every manager due to huge volume of data needed to deal with and understand. Knowing the effective and efficient approach of turning this huge raw data into relevant information to enhance business success in today's challenging and competitive-environment inform the written of this book to Instilling Data Intelligence using Microsoft Excel and examine productivities boosting aspects of Excel.The book focused more on how to improve your Microsoft Excel skills and furnished you with opportunities to be versed with functionality of jobs, functions and formulas that are needed to grow fast and stay competitive. Our illustrations mirror the likely situation you might experience at work. Microsoft Excel was chosen because it remains one of the most cost effective and popular spreadsheet today. The essence of chapter one is to familiarise you with the Excel environment by simply introduce you to various features of Microsoft Excel while chapter two focused on the classification of data to various types based on predefined characteristics and how its value should be stored, what it takes for a datasheet to stands the test of time, what constitute data to be good, and business analysis aided tools.Data formatting (cleansing) is an essential process of transforming raw data into proper format (Good Data) using tools such as Tables, Flash fill, Text-to-Column, Validation, and Group to enhance all kind of operations in excel, simplify all stages of data analysis, and succinctly gives clear picture of the message been communicate was explained in chapter three. The amazing sort function use to change the order of data as chooses and filter function helps to focus on a specific set of extracted (filtered) data was discussed in chapter four. Knowing the right graphing tool for message is very important to produce effective reports that arereadable and summarily convey the right messages were explained in chapter five. Chapter six focused on conditional formatting to indicate various metrics performance in a report and how to prevent unnecessary alteration to reports generated.Techniques involve IF related functions to trick Microsoft Excel to generate significant business reports took centre stage in chapter seven while chapter eight explained those LOOKUP Excel Formulas that will enable you to carry out hundreds of searches, calculations and analysis in minutes. While chapter nine explained ideal Excel functions that will help you to maintaining highest level processing speed of lookup formulas in workbooks that contain thousands of lookup formulas as well overcome any form of value error that may arise while working with huge dataset. Chapter ten is all about Array in Excel. Chapter eleven and twelve will inundate you with how productive and useful both Excel Mathematics and text functions could simplify dynamic reports generation. How to use excel built-in functions to determine the status of data and errors handling in excel were explained in chapter thirteen. Sparklines took the centre stage in chapter fourteen, while chapter fifteen was setup to facilitate you with business analysis tools used to manage different business Scenarios and come out with the best results that will set you in right direction toward increasing productivity and profitability.Chapter sixteen and seventeen clearly explained essential Excel tools such as PivotTable, Pivot Charts, Slicers, and Timelines used to explore and analyze huge data set in order to generate an interactive and user friendly summary reports. Statistical tools such as correlation and regression used to determine the relationship between variables while carrying out data analysis were explained in chapter eighteen while Microsoft VBA popularly known as MACRO took the centre stage in chapter nineteen. Preface......Page 9 Microsoft Excel at a Glance......Page 12 Home Menu......Page 13 Insert Menu......Page 14 Page Layout Menu......Page 15 Data Menu......Page 16 Review Menu......Page 17 Cell, Worksheet, & Workbook......Page 18 Text......Page 20 Formula......Page 21 Data Consistency......Page 22 The second approach to name a range......Page 25 Rename Sheets......Page 27 Sheet Tab Color......Page 28 Duplicate Worksheet......Page 29 Linking Worksheet......Page 30 Hide / Unhide Rows and Columns......Page 32 To Insert, Format, and Delete Cells, Rows or Columns......Page 34 Chapter 3: Data Formatting......Page 36 Create a Table......Page 37 Benefits of Excel Tables......Page 41 Format for printing and Email......Page 45 Remove Duplicates......Page 49 Text to Columns......Page 53 Flash Fill......Page 57 To Create a Drop Down List......Page 61 Subtotal......Page 64 To Insert Subtotal......Page 65 To Remove Subtotal......Page 67 How to Insert Group and Outline?......Page 68 To Remove Grouping......Page 70 Chapter 4: Sorting and Filtering......Page 71 Top to Bottom Sorting......Page 73 Left to Right Sorting......Page 75 Sort by Color......Page 77 Filter......Page 79 To Remove Filter......Page 83 Advance Filtering......Page 84 Chapter 5: Charts in Excel......Page 88 Clustered Column......Page 89 Stacked Column......Page 93 100% Stacked Column......Page 94 Line Chart......Page 96 Bar Chart......Page 98 Pie Chart......Page 101 Combo Chart......Page 103 Built-in Conditional Formatting......Page 105 Logical Formula......Page 111 Conditional Formatting with Data Validation......Page 116 Two ways Conditional Formatting......Page 117 Three-ways Conditional Formatting......Page 119 Clear Rules......Page 121 Freezing Panes......Page 122 Windows Splitting......Page 124 Protect Worksheet and Workbook......Page 126 How to Protect Cells that Contain formulas?......Page 129 Basics of IF Function......Page 133 Combining IF with AND......Page 135 Combining IF with OR......Page 136 Nested IF Function......Page 137 COUNTIF......Page 138 COUNTIFS......Page 139 SUMIF......Page 143 SUMIFS......Page 144 AVERAGEIF......Page 147 AVERAGEIFS......Page 148 SUMPRODUCT......Page 149 IFERROR......Page 151 Chapter 8: Performing Lookup in Excel......Page 152 LOOKUP - Vector Form......Page 153 LOOKUP - Array Form:......Page 155 VLOOKUP and its Application......Page 158 VLOOKUP for Exact Match......Page 160 VLOOKUP for Approximately Match......Page 162 Looking for Exact MATCH using HLOOKUP......Page 164 Looking for Approximately Match using HLOOKUP......Page 165 Chapter 9: Power Excel Data Functions......Page 167 MATCH Function......Page 168 INDEX Function......Page 171 INDEX MATCH Function......Page 173 CHOOSE Function......Page 177 Chapter 10: Arrays in Excel......Page 179 An Array and array Formula......Page 180 Creating and Using an Array Formula......Page 181 Conditional Evaluation in an Array Formula......Page 185 Chapter 11: Excel Math Functions......Page 188 The OFFSET Function......Page 189 RAND and RANDBETWEEN......Page 191 INDIRECT Function......Page 192 ROUNDDOWN......Page 194 CEILING and FLOOR......Page 196 COUNT, COUNTBLANK and COUNTA......Page 197 RANK.AVG......Page 198 BREAK DUPLICATE......Page 199 DATEVALUE......Page 201 WORKDAY.INTL......Page 203 NETWORKDAYS.INTL......Page 206 Database Functions in Excel......Page 208 DAVERAGE......Page 209 DCOUNT......Page 210 DCOUNTA......Page 211 DMAX......Page 212 DMIN......Page 213 DPRODUCT......Page 214 DSTDEV......Page 215 DVAR......Page 216 DVARP......Page 217 Chapter 12: Excel Text Functions......Page 218 TRIM......Page 219 LEFT......Page 220 RIGHT......Page 221 Use Extracting functions with SEARCH......Page 222 Ampersand......Page 225 PROPER......Page 227 REPLACE......Page 228 REPT......Page 229 Chapter 13: IS Functions......Page 230 ISODD......Page 231 ISBLANK......Page 233 ISLOGICAL......Page 234 Error Checking with ISERROR......Page 236 Error Checking with ISNA......Page 237 Chapter 14: Sparklines......Page 238 Create a Sparklines in Excel......Page 239 Change the Design of Sparklines......Page 241 Removing Sparklines from a Sheet......Page 242 Scenario Manager and its Application......Page 243 Setting up Scenario and Entering Values to carry out What-If-Analysis......Page 244 Getting a summary of all Scenarios......Page 246 Using Goal Seek to carry out What –If-Analysis......Page 248 Activating Solver Add In......Page 250 Using SOLVER to Carry out What if Analysis......Page 251 Add constraints into Solver problem......Page 253 Chapter 16: PivotTable, Slicer, and Timeline tool In Excel......Page 256 PivotTable......Page 257 Organize your source data......Page 258 Create a PivotTable......Page 259 Arrange / Rearrange Fields in a PivotTable......Page 261 Show Different Calculations in PivotTable......Page 264 Summarize Values By......Page 265 SHOW VALUES AS......Page 266 Format PivotTable Numbers......Page 268 Applying Pivot Table Styles......Page 270 Disabling and Enabling Grand Totals......Page 271 Applying Report Layout......Page 272 Making Use of the Report Filter Option......Page 273 How to Refresh a PivotTable?......Page 274 Grouping......Page 276 Moving a Pivot Table......Page 279 Removing a Pivot Table......Page 280 The Slicer Tool......Page 281 The Timeline......Page 283 Chapter 17: Pivot Charts in Excel......Page 286 Creating a Pivot Chart......Page 287 Changing Chart Types Formats and Layouts......Page 290 Filtering a Pivot Chart......Page 292 Hiding Pivot Chart Field Buttons......Page 294 Moving a Pivot Chart between Sheets......Page 295 Deleting a Pivot Chart (With Care)......Page 297 Chapter 18: CORRELATION and REGRESSION ANALYSIS......Page 298 Activating Analysis ToolPak Add In......Page 299 CORRELATION COEFFICIENT......Page 301 REGRESSION ANALYSIS......Page 304 Significance F and P-Values......Page 306 Coefficients......Page 307 Chapter 19: Macros in Excel......Page 309 What is a Macro?......Page 310 Activating Developer Tab......Page 311 Creating Storing and Running your First Macro......Page 313 Invoke Macro with Keyboard Shortcut......Page 314 Using Form Button to Invoke Macro......Page 317 Chapter 20: Power Query and Power Pivot......Page 320 How to Activate Power Pivot and Power Query......Page 321 Importing Data Using Power Query......Page 323 Importing Data to Power Pivot......Page 331 Bonus: Keyboard Shortcuts in Excel......Page 338