Excel for the CEO

Chia sẻ: Ba Trinh | Ngày: | Loại File: PDF | Số trang:368

0
412
lượt xem
267

Excel for the CEO

Mô tả tài liệu

Excel for the CEO provides the quick information you need to master Excel for running your business. Includes a special focus on pivot tables and advanced data funtions, designed to put you a step ahead of your competition.

Chủ đề:

Bình luận(0)

Lưu

Nội dung Text: Excel for the CEO

1. Excel for the CEO P.K. Hari Hara Subramanian Holy Macro! Books
4. Table of Contents Excel-lent Tips ......................................................................... 27 Activating the Menus .................................................................................................................28 Accessing the Toolbars with the Keyboard ............................................................................29 Basic and Common Shortcut Keys ..........................................................................................29 Shortcuts with the Ctrl Key Combination .....................................................................31 Ctrl Key with Special Keys.............................................................................................34 Ctrl Key with Function Keys ..........................................................................................35 Shortcuts Using the Shift Key...................................................................................................36 Shift Key with Numeric Pad Keys .................................................................................36 Shift Key with Function Keys and Other Keys..............................................................37 Shortcuts Using the Alt Key ......................................................................................................38 Alt Key with Numeric Keys ............................................................................................39 Alt Key with Alphabet keys ............................................................................................39 Alt Key with Function Keys............................................................................................40 Special Shortcut Keys ...................................................................................................40 Shortcuts Using the Function Keys..........................................................................................44 Twenty Terrific Excel Tips ..........................................................................................................45 Links for More Tips.....................................................................................................................51 Best Keyboard Shortcuts for Selecting Data ...............................................................51 Best Keyboard Shortcuts for Moving in a Workbook...................................................51 Best Keyboard Shortcuts to Use with Functions and Formulas .................................51 Formulas, Functions, and More… .............................................. 53 What Is a Formula and How Does It Work? ...........................................................................54 The Operator Precedence Rules...................................................................................55 Relative vs. Absolute Referencing ................................................................................56 Introducing Array Formulas ......................................................................................................57 Common Error Messages in Formulas....................................................................................59 How are Functions Different from Formulas? .......................................................................59 What are the Categories and Components of Functions?...................................................60 Financial Functions ....................................................................................................................61 Depreciation-related Functions ....................................................................................62 Annuity-/Investment-related Functions........................................................................64 Functions for Interest Computation .............................................................................66 Examples........................................................................................................................66 Date and Time Functions ..........................................................................................................68 Examples of Time-related Functions ............................................................................70 Examples of Date-related Functions ............................................................................71 Math, Trig, and Statistical Functions.......................................................................................71 Math and Trig functions ................................................................................................72 Statistical Functions......................................................................................................76 Lookup and Reference Functions............................................................................................78 Database Functions ...................................................................................................................83 Text Functions.............................................................................................................................85 Examples of Text Functions ..........................................................................................89 ii Excel for the CEO
5. Table of Contents Logical Functions........................................................................................................................90 Practical Application of Logical Functions ...................................................................91 Information Functions ...............................................................................................................91 Practical Application of Information Functions............................................................94 Nesting Functions.......................................................................................................................94 Example of Multi-level Nesting .....................................................................................96 Troubleshooting and Evaluating Formulas and Functions...................................................97 Conclusion ................................................................................................................................ 101 Data Management in Excel ..................................................... 103 Database Management – the Basics................................................................................... 104 Data Management Functions Available in Excel................................................................ 105 Creating a Database .................................................................................................. 106 Sorting a Database ................................................................................................................. 109 Steps in Data Sorting ................................................................................................. 109 Filtering Data – AutoFilter and Advanced Filter ................................................................. 112 AutoFilter..................................................................................................................... 112 Advanced Filter........................................................................................................... 116 Subtotals, Grouping and Outlining........................................................................................ 120 Grouping and Outlining .............................................................................................. 123 Data Validation Feature ......................................................................................................... 125 Converting Text to Columns / Importing Text Data ........................................................... 130 Sound Interesting?.................................................................................................................. 135 Advanced Data Management in Excel ..................................... 137 Database Functions – the Concept ...................................................................................... 138 Detailed Listing of Database Functions............................................................................... 139 Using Database Functions ..................................................................................................... 142 DSUM Function........................................................................................................... 143 DCOUNT Function....................................................................................................... 144 Other Functions .......................................................................................................... 144 Working with Data Tables...................................................................................................... 145 Data Table Based on Two Variables.......................................................................... 150 Creating and Managing Lists................................................................................................. 152 Advantages of Using a List......................................................................................... 153 Step-by-Step Process of Creating a List .................................................................... 154 Consolidation of Data ............................................................................................................. 156 Consolidation Using 3D Formulas ............................................................................. 157 Importing External Data ......................................................................................................... 159 Handling XML data.................................................................................................................. 162 More Terrific Links................................................................................................................... 163 PivotTables, PivotCharts, and Reporting ............................................................................. 165 Introducing PivotTables.......................................................................................................... 166 When Should You Use a PivotTable? ................................................................................... 166 Creating a PivotTable.............................................................................................................. 167 Filtering and Modifying Fields within a PivotTable ............................................................ 174 Excel for the CEO iii
6. Table of Contents Sorting Data and Listing Specific PivotTable Items........................................................... 176 Listing the Top / Bottom Items.................................................................................. 178 Drill Down Facilities .................................................................................................... 180 Using Calculated Fields.............................................................................................. 183 PivotTable Report Formatting Options ................................................................................ 188 PivotTable Field Settings........................................................................................................ 190 Things You Should Know When Using PivotTables ............................................................ 192 Refreshing Data.......................................................................................................... 192 Changing the Range of Source Data ......................................................................... 193 Table Options.............................................................................................................. 193 Show Pages ................................................................................................................ 195 Creating a PivotChart.............................................................................................................. 196 Links for More Information.................................................................................................... 200 Auditing Tools ........................................................................ 201 Introducing Auditing Tools ..................................................................................................... 202 Formula Auditing Toolbar ...................................................................................................... 203 Error Checking Feature........................................................................................................... 203 The Rules and What They Check for ......................................................................... 206 Other Buttons in the Error Checking Dialog Box....................................................... 206 Tracing Precedents ................................................................................................................. 207 Tracing Dependents................................................................................................................ 208 Removing Tracing Arrows ...................................................................................................... 209 Trace Error Option ................................................................................................................... 210 New Comment / Edit Comment Option............................................................................... 210 Circling Invalid Data................................................................................................................ 211 Clearing Validation Circles ..................................................................................................... 214 Showing Watch Window ........................................................................................................ 215 Evaluate Formula Option ....................................................................................................... 217 Tips for Using Formula Evaluator .............................................................................. 219 Tracking Changes Made to a Workbook ............................................................................. 219 Viewing and Printing Formulas in Any Sheet...................................................................... 221 Example Using the Formulas Checkbox.................................................................... 222 Viewing and Printing Comments and Errors ....................................................................... 223 Controlling the Printing of Comments ....................................................................... 225 Controlling the Printing of Cell Errors ........................................................................ 225 Related Links ........................................................................................................................... 226 iv Excel for the CEO
7. Table of Contents Formatting and Printing Reports ............................................ 227 Print Preview Options and Zooming..................................................................................... 228 Controlling Print Selection, Page, Cells, and Copies.......................................................... 230 Selecting Pages to Print ............................................................................................. 231 Controlling Print Copies.............................................................................................. 231 Printing to a File.......................................................................................................... 232 Setting, Removing Page Breaks; Page Break Preview ..................................................... 232 Print Area – Setting, Clearing................................................................................................ 235 Page Control – Orientation, Size, Scaling, Quality ............................................................. 236 Alignment Control – Margins, Centering Report ................................................................ 238 Headers and Footers, First Page Numbers ......................................................................... 239 Controlling Sheet Properties During Printing...................................................................... 241 Printing Charts ......................................................................................................................... 243 Four More Topics ..................................................................................................................... 244 Massive Printing of Blank Pages ............................................................................... 244 Pages per Sheet Option ............................................................................................. 244 Printing to PDF............................................................................................................ 245 Copying Page Setup Options Between Different Sheets.......................................... 246 For Further Study..................................................................................................................... 247 Adding Interactivity and Publishing Reports on the Web ......... 249 Saving as a Web Page............................................................................................................ 250 Publishing an Entire Workbook on a Web Page ................................................................ 253 Publishing One Worksheet / Range / Other Items............................................................ 254 Publishing a Chart on a Web page ....................................................................................... 258 Publishing a PivotTable Report on a Web Page................................................................. 259 Further Information… ............................................................................................................ 261 An Introduction to VBA ........................................................... 263 What Is a Macro and What Is It Used for?........................................................................... 264 Recording a Macro – the Toolkit........................................................................................... 265 Writing a Macro - the VBE Window....................................................................................... 269 Running a Macro ..................................................................................................................... 270 Other Advanced Topics and Links ........................................................................................ 271 Case Studies .......................................................................... 273 Peter F. Drucker’s Advice to CEOs ........................................................................................ 274 Duties of the CEO ....................................................................................................... 274 Key Decision Making Points ...................................................................................... 275 OK, But Where Does Excel Come in? ........................................................................ 276 Reviewing Projects and Their Profitability........................................................................... 277 Checking Delivery Schedules Using Gantt Charts .............................................................. 282 Comparing Excel Worksheets/Workbooks ......................................................................... 286 Comparison Process .................................................................................................. 288 Financial Analysis Models – Creation and Automation .................................................... 290 Having Your Own Menu Bar ................................................................................................... 294 Excel for the CEO v
8. Table of Contents Control Reports Using PivotTables ....................................................................................... 298 Step-by-Step Construction of the Control PivotTable ............................................... 301 Using Conditional Statements for Reporting ...................................................................... 304 Controlling Entry of Dates – the Dating Problem!.............................................................. 309 Some Interesting Examples................................................................................................... 311 Using Word Count in Excel ......................................................................................... 311 Showing Formulas in Different Color......................................................................... 311 Deleting Empty Worksheets....................................................................................... 312 Sorting All Sheets by Name........................................................................................ 312 Forcing Caps on Entry ................................................................................................ 312 Deleting Every nth Row .............................................................................................. 313 Merging Data of Multiple Columns into One Column............................................... 313 Merging the Selection into One Cell .......................................................................... 314 Inserting a Blank Row Between Every Row of Data ................................................. 314 Coloring Alternate Rows with a Distinct Shade......................................................... 315 Using VBA to Print Your File to PDF ........................................................................... 315 Goal Seek and Scenario Builder ............................................. 317 Using Goal Seek....................................................................................................................... 317 Using Scenario Builder ........................................................................................................... 320 Other New Techniques and Developments ......................................................................... 327 Dashboard Charting Techniques ............................................................................... 327 New Techniques with PivotTables ............................................................................. 327 New Techniques with Charting .................................................................................. 327 Resources and Examples for the Finance People.................................................... 327 Other Interesting Developments ............................................................................... 328 One Journey Ends – Another Begins........................................ 329 A Word of Conclusion ............................................................................................................. 330 Appendix A – Finding and Launching Excel ............................. 331 Where to Find Excel ................................................................................................................ 331 Appendix B – Excel’s Roots .................................................... 333 How Did It All Start – Weaving the Excel Magic ................................................................. 333 Index ..................................................................................... 335 vi Excel for the CEO
9. Dedications and Acknowledgements Dedications To my parents, who have raised me to be a knowledge-seeker, and my family and friends, who have always encouraged me to continue learning and sharing knowledge. Acknowledgements “We are what we repeatedly do. Excellence, then, is not an act, but a habit.” Aristotle Though I am an accountant by profession, I am known more in my industry for my skills with the PC, and with Excel®, especially. This is so because I have spent a considerable portion of my life-time in refining and automating business processes. Having tried different spreadsheet programs, I settled on Excel as the best suited, since it has got a wide range of features and is almost fully customizable. All the knowledge that I have acquired so far about Excel and automation possibilities is creditable to many individuals, to whom I owe a lot for sharing their terrific knowledge, support, and motivation. The list is long and nearly endless, but I shall include at least a few of them here for their specific and direct contributions to this project. My thanks and appreciation goes to Mr. Sriram Vaidheeswaran (Scope Intl, India) for initiating me into a career in computers, to Mr. V. L. Parameshwaran (Senior Auditor) and Mr. K. R. Sundaram (Sr. Consultant-KG Group, India) for their continued support to me in developing automation tools, and also to Mr. L. Mallikharjuna Rao (Sr. Partner – Brahmayya & Co, Excel for the CEO a
11. Dedications and Acknowledgements Do remember that you work hard to get good and then again work hard to get better. There is a simple 3P’s recipe for success – it is Patience, Perseverance and Practice. All the Very Best to Excel with Excel®, P.K.Hari Dubai pkhariaiyer@gmail.com June 2006 www.hari.ws Excel for the CEO c
12. Dedications and Acknowledgements d Excel for the CEO
13. About the Author f Excel for the CEO
14. Starting off on the Right Foot Chapter 1 Starting off on the Right Foot Welcome aboard! This book is your guide on a journey into the mysteries of Excel. This is a never-ending, ever-exploring adventure that tunnels you deep into Excel’s secrets. Let’s start with the basics. You may already know some of this information; nevertheless, give it a quick read – you just might get to know some useful info that you haven’t come across elsewhere. If you have never ventured into Excel before, take a look at Appendix A to find out how to locate and launch Excel. If you are really supremely confident of your basic Excel foundations, please feel free to skip this chapter and plunge into Chapter 2 – Excel-lent Tips starting on page 27 – for insights into the secrets of, and tips on using, Excel. Topics in this chapter: Basic terminology Creating, saving, opening, and closing workbooks Navigating in Excel – within a worksheet and between workbooks Selecting cells, rows, and columns, and non-adjacent cells Editing data, updating and deleting data – ways available Formatting cells and data Worksheet background, name, hiding and unhiding Entering functions in Excel Entering formulas in Excel Creating charts – the basics Excel for the CEO 1
15. Chapter 1 Starting off on the Right Foot Basic Terminology Before proceeding further, let’s review some basic Excel terminology. It will make life simpler for you and speed your progress if you spare a couple of minutes to familiarize yourself with these terms. The following figure shows the locations of the workbook components that we are going to discuss in this section: Figure 1 Basic components in a typical Excel workbook Title Bar Close Excel Menu Bar Maximize/Restore Excel Standard Toolbar Formatting Toolbar Minimize Excel Help Bar Min Window Max/Rstr Window Close Win Formula Bar Active Cell Indicator Name Box Task Pane Column Headings/Names Selector Task Pane Row Headings/Numbers Sheet Tabs Horizontal Scroll Bar Vertical Scroll Bar Tab Scroll Buttons Status Bar 2 Excel for the CEO
16. Starting off on the Right Foot Chapter 1 Title Bar This bar reflects the names of both the Application and the Active File (the one that is currently open) – in this case, “Microsoft Excel – Book1”. You can change the appearance to show your own name (amazing, but true!) like this – “Sam’s Excel – Book1”. Want to know how ? Hang in there – the answer is in the next chapter of tips and tricks! Menu Bar You probably know what a Menu bar is – it contains a collection of all the menu functions available in the application. But did you know that you can customize this, too – that you can have your own personalized menu of functions listed there like this – “John’s Tools”? You will learn how in one of the case studies. Active Cell The current working cell is called the Active Cell in Excel as well as in VBA. The Active Cell is identified with a thick black border around the cell that makes it obvious. Note: The row and column headings of the active cell are highlighted in different color shades than the others to make it easier to identify the current working row and column. Excel for the CEO 3
17. Chapter 1 Starting off on the Right Foot Toolbar Collections You will see the Standard and Formatting toolbars in the snapshot in Figure 1, but these are but a small selection from a huge list of toolbars available in Excel. These two are the most widely used, and are normally placed within one row to maximize the working area. There are almost 20 different toolbars, and if you were to place each one of them in a separate row on the screen, you would be left with just one row in Excel’s working area! We will get to know more about the relevant toolbars throughout the book. Certain tools are pretty cool and easy to work with. The more you explore and work with them, the more familiar and comfortable you will become with Excel. Help Bar This is available only with the latest editions of Excel, and it provides you with an instant search box, thus simplifying Help and making the Help bar preferable to the conventional F1 key (although that still works). Minimize / Maximize and Close Buttons There are two sets of these buttons – one at the Application level (Excel level) and another one at each worksheet or window level. You can choose to minimize, maximize or restore, and also to close Excel using one of these buttons. Tip: Shortcut keys: Ctrl+F9 keys – Minimize Ctrl+F10 keys – Maximize / Restore Alt+F4 keys – Close application 4 Excel for the CEO