Author: Wayne Winston

Dr. Winston's Excel Tip: Sensitivity Analysis with Two-Way Data Tables

Most spreadsheets have inputs and outputs. Spreadsheet inputs are assumptions that we make. For example, the price of a product might be a spreadsheet input.Spreadsheet outputs are quantities of interest that depend on the value of the spreadsheet’s inputs. For example a company’s annual profit...


Dr. Winston's Excel Tip: Sensitivity Analysis with One-Way Data Tables

Most spreadsheets have inputs and outputs. Spreadsheet inputs are assumptions that we make. Some examples of spreadsheet inputs follow: Unit sales of a product will grow at 10% per year.Our product will generate profit margins of 30%.Our product will sell for five years.Each year 90% of...


Dr. Winston's Excel Tip: Excel's Magical Equation Solver: Goal Seek

You may have a spreadsheet that computes profit from a new product as a function of units sold. A colleague asks how many units need to be sold for the product to break even. You frantically change the value in the units sold cell until profit equals 0. If you have ever faced a similar situation in...


Dr. Winston's Excel Tip: How to Use Waterfall Charts in Excel

In 2016 Excel added many new charts, including the Waterfall chart. Waterfall Charts are great for telling the story of how a quantity of interest (for example, cash position) changes over time. Waterfall charts also allow the finance professional to chart for a single point in time how a quantity...


Dr. Winston's Excel Tip: How to Summarize Data with Treemap and Sunburst Charts in Excel

Treemap charts, also known as mosaic charts, and Sunburst charts were added to Excel to help you visualize hierarchical data. Hierarchical data is a way to organize data with multiple one-to-many relationships. The structure is based on the rule that one parent can have many children, but children...


Dr. Winston's Excel Tip: 3 Tricks to Help You Work Across Microsoft Excel Worksheets

Excel has three dimensions: rows, columns and worksheets. Most of us are familiar with how to use the powerful and indispensable Copy command to “copy” a formula across columns and down rows. In this article, we explore three tricks that can help you work across worksheets. Copying a...


Dr. Winston's Excel Tip: How to Use Hyperlinks in Microsoft Excel

When navigating the web, we have all clicked on blue hyperlinks that send us to another web page. In this article, we will show you how to use hyperlinks in Excel to create links in your workbook to web pages or locations in your current workbook. Our work is in the file...


Excel’s Stock Data Types

Office 365’s new stock data types allow you to type a company name or stock ticker symbol in Excel and write formulas that extract information about the company to a different cell. The file Stockdatatypes.xlsx contains our work. To begin we typed in the company names and ticker symbols shown...


Excel’s New Geographic Data Types

Office 365’s new geographic data types allow you to type a location in Excel and write formulas that extract information about the location to a different cell. For example, if you enter “Paris” in cell E4, entering the formula “E4.Population” in a different cell will...


Dynamic Ranges and the OFFSET FUNCTION

In our January and February 2019 newsletters we discussed the OFFSET function. Recall that the syntax of the OFFSET function is OFFSET(cell  reference, rows moved, columns moved,height,width). As we explained in our last two articles, we begin in the cell reference and based on the rows...