Category Archives for "Analysis"

Top 29 Best Pivot Table Tips

Analysis , Excel , Formatting , Pivot Table and PowerPivot , Top Best Tips

It is easy to say that Pivot Tables and Power Pivot are the two most powerful features in Excel. I don’t mean time saving as with Excel VBA macros, but rather they are powerful because the decisions made from Pivot Table (and Power Pivot) results drive key business decisions. I’ve built tons of Pivot Tables […]

Continue reading

PowerPivot and Power View for Excel 2013

Analysis , Charts , Dashboards , Excel , Pivot Table and PowerPivot

If you are using PowerPivot for Excel 2010, then click here. The following collections of links and resources will help you go from short video overviews of PowerPivot and Power View capability to being fairly proficient. Video Overview of Excel 2013 PowerPivot and Power View Here’s a collection of short videos from Microsoft on Excel […]

Continue reading

Excel 2010 PowerPivot

Analysis , Charts , Dashboards , Excel , Pivot Table and PowerPivot

To learn more about PowerPivot and PowerView for Excel 2013, click here. If you have Excel 2010 you have access to Power Pivot, but not Power View. Just the capabilities of Power Pivot make it an extremely powerful tool that any power Excel user has to be familiar with. The ribbon has slight differences from […]

Continue reading

Excel’s Four Quadrant – Matrix Model Chart : Don’t Make a Decision without It!

Analysis , Balanced Scorecard , Charts , Dashboards , Decision Making , Excel , Personal Performance , Strategy , SWOT Analysis and Action Plan , Time Management

The Four Quadrant or Matrix Model is one the most valuable and widely used tools for decision making. This article has short descriptions of a few famous Four Quadrant – Matrix Model charts. The related article shows you how to create the two most used types using Excel charts. You can’t be a successful consultant […]

Continue reading

Smoothing Excel Chart Data with a Moving Average

Analysis , Charts , Dashboards , Excel , Formulas

Sales, marketing, and customer data can be so erratic that it’s hard to see patterns in your Excel chart or dashboard. With a couple of easy techniques and formulas you can smooth your data to make those hidden patterns visible. The easiest smoothing technique is the moving average. For example, instead of showing the current […]

Continue reading

Smoothing Excel Chart Data with Dynamic Smoothing

Analysis , Charts , Dashboards , Excel , Formulas

Smoothing Excel chart data with a fixed moving average works when you have experience with the data and how variable it is. But, if the data changes or its new you or management may want to experiment with a different number of periods in the smoothing average. In this example, an OFFSET formula is used […]

Continue reading

Dynamically Control Excel Chart Trend Lines with Check Boxes and Drop-Down Lists

Analysis , Charts , Dashboards , Excel , Formulas

You can get very creative using check boxes and drop down lists to give users the ability to view different sets of data and trend analysis. In this example the user can use a check box to select whether to show or hide the trend line. They can also use the drop down list to […]

Continue reading

Editing TREND and Array Functions

Analysis , Charts , Dashboards , Excel , Formulas

TREND and some other functions that work on an array (a collection) of cells are array formulas. They use an array of cells as data sources and usually output the results into an array of cells. Array formulas are entered by selecting the entire range to receive the resulting array, typing the formula, and then […]

Continue reading

Using the TREND Function to Forecast Future Values in Excel Charts and Dashboards

Analysis , Charts , Dashboards , Excel , Formulas

The TREND function will also forecast future points on the trend line. The TREND function format is, =TREND(known-y’s,[known_x’s],[new_x’s],[constant]) In the figure below the previous tutorial was used to fill D24:O24 with the array formula, {=TREND(D23:O23,D22:O22)} Use the TREND function to forecast out as many periods as you think are valid. In general, for most business […]

Continue reading

Using Excel’s TREND function to Create Trend Lines on Excel Charts or Dashboards

Analysis , Charts , Dashboards , Excel

To see the trend data and to create dynamic trend lines you can control with menus you need to use Excel’s TREND function. Using the Trendline function in the Analysis group is an easy way to add a trend line to a chart, but it doesn’t show you the data for that line and it […]

Continue reading