Jon Paul has been using spreadsheets for over 20 years and has complimented by many clients on his Excel work. Many have said, I wish I could spend a day looking over your shoulder. Well now you can. Jon passes along ideas he has shown clients throughout the years. In addition to the capabilities within the Excel program, Jon has also used the VBA programming language to build high end financial models and financial applications.
If new to this section, you might want to go bottoms up. Latest articles are on top and some build on each other.
Got an Excel question or a topic you would like covered? Email jon@valueaddedfinance.com
Charts- Techniques
| Page | Covers |
| Charts- Full Window | Get your chart to cover the full screen, regardless of the size |
| Multiple Charts | A neat way to quickly put multiple charts on the same chart sheet. |
| Chart Series | Changing the order of your data in a chart. |
| Chart Plot Area | Formatting the plot area of the chart so the data stands out better. |
| Linking Chart Titles | How to link titles into your chart rather than typing them in. |
| Floating Chart Text | Using floating text in your charts for comments in the body or as an alternative to the title. |
| Secondary Axis & Adding Chart Data | Adding a secondary axis to your charts and how to add in additional data |
| Data Tables in Charts | Add data tables to your charts and tie in your legend |
| Chart Data Labels | Add data labels to your charts. Excel 2002 users- show multiple items in data labels on charts |
| Scatter Chart Fix | Here is a way to use scatter charts to fix a timeline problem that can occur in charts. |
| Chart Gaps | You're not stuck when you have gaps in your data- how to work around with your chart. |
| Line Chart Formatting | Make the lines in your line charts stand out |
| Changing Chart Data | Changing data on your chart can be easier than you think |
| Trailing 12 Month Chart | How to create a trailing 12 month chart that automatically adjusts as you add additional months using OFFSET and COUNT functions |
Charts- Types
| Page | Covers |
| Scatter Charts | Scatter charts can be a great way to visually correlate your data. |
| Radar Charts | These are an interesting way to display data, with the axes of each point extending from the center. |
| Doughnut Charts | Good way to give birds-eye view of two or more sets of data. |
| Bubble Charts | Use bubble charts to get a 3D perspective in a 2D chart and add a third dimension to a scatter chart. |
Data Analysis
| Page | Covers |
| Data | Get your data in shape to take advantage of Excel's data analysis tools |
| Data Filter | Introduction to basic data filtering to view certain parts of your data |
| AutoFilter | A look at advanced ways to use AutoFilter for filtering data |
| Advanced Filter | Using the advanced filter to copy over selected information or do faster searches |
| Data Index | Putting an index on long lists to maintain your data |
| Sorting Data | Sorting your data quickly and creating your own custom sort orders |
| Copy With Filters | Use filters to help copy selected information from a list |
| Does A Value Appear? | Simple but powerful way to test if a value is included in a list |
| Rank | Ranking your results automatically |
| List Formats and Formulas | Excel 2002 offers a way to automatically format list entries and automatically enter formulas in lists. |
| Filling In Row Numbers | Excel has a ROW function you can use for this, which allows your row numbers to change dynamically with any changes. |
Errors
| Page | Covers |
| Error Checking | Using an error checking formula that becomes visible only when there is an error |
| Conditional Formatting | How to use conditional formatting to highlight errors or key results |
| Circular Reference Iterations | Using iterations to get around valid circular references |
| Broken Links | Avoiding and fixing any broken links |
| Error Trapping | Error trapping functions. Keep those nasty errors from blowing your spreadsheets. |
| AutoCorrect | Handy tool for speeding up typing on long names or to automatically correct common misspellings |
Formatting
| Page | Covers |
| Paintbrush | Quickly copy formats |
| Legal Symbols | Inserting legal symbols |
| Color Tabs | Another new feature in Excel 2002. You can format the worksheet tabs in color for easier viewing and navigation. |
| Double Underline | A quick way to double underline numbers |
| Merge & Unmerge Cells | Small but helpful feature added with Excel 2002 |
| Custom Formats | Creating your own custom formats, such as showing cents. |
| Custom Formats Expanded | More on custom formats. Colors. Changing based on number. Other tips. |
| Round | A number of different ways to round your numbers |
| List Formats and Formulas | Excel 2002 offers a way to automatically format list entries and automatically enter formulas in lists. |
Formulas
| Page | Covers |
| Viewing Formulas | Fast way to view formulas in a worksheet. |
| Random | Use this function to develop data to test out new spreadsheets |
| Formula Auditing | More on auditing formulas |
| Formula Arguments | A quick way to display formula arguments when you start typing a formula |
| Labels in Formulas | Using labels in your formulas as an alternative to cell references or names |
| Paste Operations | Do fast manipulations of numbers with this feature |
| Averages | A refresher on averages and what to do when you have blanks or zeros |
| Maximum | How to get more than just the ordinary information on the largest value |
| YTD | Setup calculations for the whole year from YTD numbers even when numbers could go negative or fall back to zero. |
General Use
| Page | Covers |
| Saving | Saving your work! Using AutoRecover, AutoSave and a quick way to manually save files. |
| Save As | A simple step that makes sure you save a backup copy with each save. |
| Shortcut Keys | Shortcut keys to save you time |
| View | How to change your view to see just what you want |
| General Workbook Settings | Key general settings- number of worksheets, font and where documents are stored. |
| Custom Views | Here is a way to have different views on the same worksheet or print different sections from the same worksheet |
| Global Searches | New in Excel 2002. Quick way to do a global search and find all instances, rather than one at a time. |
| Text Length | Find out the maximum length of a column in your list and cut down on the size of your columns. |
| Protecting Sheets | An introduction to protection- protecting your sheets so formulas are not overwritten |
| Pasting Special Ways | Paste only what you want, rather than copy the entire cell or range |
| Go To Special | Special, hidden feature for navigation. Use it to highlight inputted numbers as an example. |
| Tips and Tricks | A PowerPoint presentation done back in 1997 for a telecom client. Lots of pointers on Excel for Excel 97 that still apply. |
| Navigate Via Formulas | Change settings to navigate to cells referenced in a formula by clicking within the formula. |
| Comment Views | How to change comments if you don't want the red triangles to show. |
| Full Screen View | How to get a full view of the screen quickly and then change back. |
If Statements
| Page | Covers |
| IF | How to use IF statements to streamline your calculations or build in some quality control. Advanced uses, such as nested IF statements. |
| And, Or | Two functions you can use to add power to your If statements |
| ISBLANK | A handy function to use with IF statements |
Lookups
| Page | Covers |
| Lookup | Doing a lookup to help build your database or help your analysis |
| Lookup- Missing Items | What to do when your lookup creates an error message |
| ISNA | Using the ISNA function to override errors so lookups run smooth. |
| Match | Using the MATCH function to find row numbers; use it in combination with INDEX as a different way to do lookups |
| Index | Using the INDEX function to return a value; follow up on Maximum lesson day before. |
Macros- VBA Programming Language
| Page | Covers |
| Macro Intro | Here is an intro to Excel programming. Not as bad as it might sound. We will teach some basics you can easily use. |
| Macro Recorder | Quick intro on macros- use them to build a clickable index of sheets in your workbook, such as for a financial package. Using the Macro Recorder |
| Macro Recorder Code Cleanup | Looking at and cleaning up the code created by the recorder. Creating your second program. |
| Creating Buttons To Run Code | Finishing your first code by creating buttons so your user can easily run the programs. |
Named Ranges
| Page | Covers |
| Named Ranges | Use named ranges to make formulas more intuitive, protect your links and speed up creating formulas and spreadsheets |
| Named Ranges Expanded | Using named ranges on multiple sheets or workbooks for real power |
| Names Navigation | A great benefit of using names in large workbooks and worksheets- faster navigation |
| Names Maintenance | How to maintain the list of names in your workbook |
| Sheet Related Names | Create sheet related names in your formulas. This can speed up setting up formulas and new worksheets. |
| Automatic Named Ranges | Create named ranges automatically using Excel |
Pivot Tables
| Page | Covers |
| Pivot Tables | Introduction to pivot tables, a powerful way to summarize and analyze data |
| Pivot Table Pivoting | Quickly changing the view in your pivot tables using two different methods |
| Pivot Table Formatting | How pivot tables allow you to quickly format an entire data range. |
| Pivot Table Calculations | You can do more than just sums in pivot tables |
| Pivot Table Calculation Options | Even more calculations that you can do in pivot tables |
| Pivot Table Details | A great benefit of pivot tables- getting right to selected detail. |
| Pivot Table Pages | How to automatically create individual sheets for every option in a field |
| Pivot Table Refresh | Keep your pivot table up to date by refreshing your data and checking your range. |
| Pivot Table Calculated Fields | Add more powerful analysis to your data by adding calculated fields. |
| Pivot Table Groups | Group together pivot table results |
| Pivot Table Sorting | How to fix the sorting in your pivot tables |
| Pivot Table Fields | Another benefit of pivot tables- how to be selective with what data you show from fields. |
| Pivot Tables Page Adjustments | Getting selective in your page fields in pivot tables. We show you a trick. |
Printing
| Page | Covers |
| Printing Pointers | Tips to make it easier to print out the good work you have done |
| Printing Page Number Setup | Quickly set the page number when printing part of a larger document |
| Custom Views | Here is a way to have different views on the same worksheet or print different sections from the same worksheet |
| Printing Non Adjacent Ranges | A fast way to print non adjacent ranges. |
Simulations
| Page | Covers |
| Data Tables- 1 Variable | Introduction to data tables- creating a 1 variable table |
| Data Tables- 2 Variable | Part two on data tables- creating a 2 variable table |
| Scenario Manager | Create scenarios to show different results when dealing with more than 1-2 variables. |
| Goal Seeking | Use Excel to find the result you are looking for. |
| Solver | An add-in product to use for further analysis. |
| Solver Developer | Examples you can download and advanced versions of Solver from 3rd party that developed this add-in |
Sums
| Page | Covers |
| Weighted Average | A fast way to calculate weighted averages, without having to do individual calculations |
| SumIf | A handy way to generate totals from lists or selectively add numbers |
| AutoSum | AutoSum- more than just summing numbers. Other uses- min, max, count, average |
| Sum | Different ways to get the most out of the Sum function and do it quickly |
| Rounding Sums | How to fix rounding errors in your sums without having to fix the individual items |
| Status Bar Values | Use the status bar to quickly see results- not just sums. |
| Calculating Time | Use SUM and formatting rather than complicated formulas to total time in Excel. |
Text
| Page | Covers |
| Concatenate | Basic functions for working with text |
| Text- Core Functions | More core functions with text- length, find, left and right |
| Text- More Functions | Next we show you how to trim away unwanted text |
| AutoCorrect | Handy tool for speeding up typing on long names or to automatically correct common misspellings |
| Text Splitting | Split up text in a long line by building on your prior formulas |
| Text Length | Find out the maximum length of a column in your list and cut down on the size of your columns. |
Got an unanswered Excel question?
Email questions@excel-erate.biz
Contact Us:
Jon Paul
President, Value Added Finance Inc.
jon@valueaddedfinance.com
jpaul@mba1980.hbs.edu
847 372-1963