Tag Archives: table

excel data analysis

Data Analysis Excel Tools

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

Excel is most appreciated for it’s ease of use as a Data Analysis Tool. I mean to explore the basics, as well as the more advanced Data Analysis Excel Tools. Be sure to read through the Other Tools section below for other honorable mentions.

Data Analysis Excel Tools
excel vba
Sort & Filter
excel vba
PivotTable
excel vba
What If Analysis
excel vba
Data Analysis Toolpak
excel vba
Solver
excel vba
MS Query (SQL)

The DATA ribbon tab

As some of you already have noticed MOST (except for the PivotTable for no good reason) of the tools described in this post focus on features available BY DEFAULT within the Excel DATA ribbon tab (see image below). Some of them are AddIn that are installed by default along with MS Office, however, need to be activated manually. Skip to the relevant section of this post to read more.

excel data ribbon tab
Excel DATA ribbon tab (click to expand)
In this post I am focusing just on features that allow you to explore data or solve certain analytical problems. I am not focusing on simple data transformation features like removing duplicates, however, I do encourage that you learn more on these non-mentioned features.

Sort & Filter

Let’s kick off with the Sort & Filter group. See below for a quick look at how to use the Sort and Filter feature in Excel on an example data set:

PivotTable

PivotTables are so versatile a tool that most often no other Data Analysis Tool is needed. PivotTables are great for exploring data sets and for visualizing data with the help of automatically generated PivotCharts. See a simple PivotTable example below:

What If Analysis

The What If Analysis button in the DATA ribbon tab facilitates generally two useful features:

  • Scenario Manager – the scenario manager allows you to add several scenarios to a certain problem and then generate a summary comparing how the scenario values impact a selected cell value
  • Goal Seek
  • Data Tables – creating scenarios

Let’s explore these tools (apart from the last one) on a simple problem case example.
The problem:
what if the problem

Our example assumes there is a 500k$ Marketing Budget to spend on a Marketing Campaign which can consist of spending on Internet and/or TV ads. What is more, for the sake of the example let’s assume:

  • TV ads have a parabolic impact (see below) on Product demand i.e. at first the more you spend the more Product demand you generate, until you reach a peak where Product demand will actually fall (maybe viewers are tired of their favorite programs being bombarded by your ads).
  • Internet ads have a linear impact on Product demand i.e. the more you spend the more Product demand you generate

internet and tv spend
The Product demand cell formula is:

=2*D3+((C3-100)^2*-1+10000)

Scenario Manager

Using the example mentioned above let’s use the Scenario Manager to create 3 separate scenarios:

  1. 100% TV spend – 500k$ spent on TV ads
  2. 50% TV and Internet spend – 250k$ spent both on TV and Internet ads
  3. 100% Internet spend – 500k$ spent on Internet ads

Open the Scenario Manager

To add our scenarios go to the DATA ribbon tab, click What If Analysis and then select Scenario Manager....

Click Add to add a new Scenario

Specify the Scenario details – especially the range of Changing Cells that are to be specified by the scenario:

Scenario Manager: Adding a Scenario
Scenario Manager: Adding a Scenario

Next provide values as appropriate to your Scenario:
Scenario Manager: Setting the Scenario Values
Scenario Manager: Setting the Scenario Values

Repeat for each Scenario

You need to Name and specify the Changing Values for each Scenario separately.

Generate a Summary

Hit Summary... to generate a neat worksheet with a summary of your Scenarios. Specify the Result Cells – cells which will be modified in result to your Scenario. In my case it is just the Product demand cell:

Scenario Manager: Generate Summary
Scenario Manager: Generate Summary

Now admire the results:
Scenario Manager: Results
Scenario Manager: Results

Notice that in this example on purpose I created the formula as such so that it is not directly obvious (unless you work out the formula maximum) what is the best mix of TV and Internet ads. We will work through this example using different tools below.

Goal Seek

Goal Seek calculates the values necessary to achieve a specific goal. The “goal” is an amount returned by a formula. So in short – you should use Goal Seek if you have a function which characteristics are unknown or hard to figure out. Goal Seek is Solver’s younger (less developed) brother.

Some examples of when to use Goal Seek:

  • Solving single (changing) parameter mathematical functions (seeking a specific value e.g. 0)
  • Doing a quick check if a single parameter can help achieve a specific value for a formula (e.g. financial statements)

Using Goal Seek

Ok let’s learn how to apply Goal Seek. Let’s reuse our above example – let’s say we are aware that our TV ads function is a parabolic or a polynomial at least and we want to quickly locate some of it’s 0 values.

Our Product demand (just for TV ads) looks as so if we ignore the Internet ads:

=((C3-100)^2*-1+10000)

Where C3 is Marketing Spend on TV ads.

Open Goal Seek

Go to the DATA ribbon tab, click What If Analysis and then select Goal Seek.

Provide the Goal Cell, Value and the Changing Cell

For our example the Goal is our Product demand, and our Changing Cell will be the Marketing Spend on TV ads:

Goal Seek: Setting up
Goal Seek: Setting up

Run Goal Seek and BEWARE!

This is the result I received. Notice that the value I provided into the TV ads cell was 0 to start with.

Goal Seek: Results
Goal Seek: Results

This time Goal Seek came up with the result: 0. Ok now let’s rerun this example. But this time I will type a higher starting value for TV ads spend… say 150k$. Do you think it will provide the same result? See below:
Goal Seek: Results with different starting point
Goal Seek: Results with different starting point

The computed result is different! This time Goal Seek came up with the result: 200. No why is that? Goal Seek is an iterative algorithm that in an iterative manner seeks to reach the Goal Value. However, if a function (cell formula) has several solutions expect that Goal Seek may land at different values.

This is very important to keep in mind!

Goal Seek in VBA

Not many know that you can use Goal Seek easily in VBA too! The parameters are as such:
Range.GoalSeek(Goal, ChangingCell)
where:

  • Range – single cell with Goal function (formula)
  • Goal – the Goal Value to achieve
  • ChangingCell – single cell which will can be modified to achieve the Goal

MSDN provides a sexy example below:

Worksheets("Sheet1").Range("Polynomial").GoalSeek Goal:=15, ChangingCell:=Worksheets("Sheet1").Range("X")

Data Analysis Toolpak

Also know as Analysis Toolpak is a Microsoft AddIn delivered with Excel (usually disabled by default) that provides data analysis capabilities to Excel. Let’s start by enabling this AddIn:

Enabling the Analysis Toolpak

Find Add-Ins in Excel Options

To open the Window go to FILE, select Options and then pick AddIns.

Analysis Toolpak: Excel Options
Analysis Toolpak: Excel Options

Next from the above window hit Go to progress to Step 2.

Open the Add-Ins window and enable the Add-In

From the Window below select Analysis Toolpak and hit OK:

Analysis Toolpak: Enabling the Add-In
Analysis Toolpak: Enabling the Add-In

Using the Analysis Toolpak

To open the Analysis Toolpak window simply go to the DATA ribbon and you should find at your far right in a new group (Analysis) the Data Analysis button. Hit it to open the whole range of Data Analysis Excel tools.

Analysis Toolpak: Opening
Analysis Toolpak: Opening

Click on the button to see the whole list of different tools:
Analysis Toolpak Tools
Analysis Toolpak Tools

I won’t go through all these tools as they are pretty straightforward. Personally I tend to use only the Correlation feature which creates a nice correlation matrix out of a series of numerical columns.

Solver

Solver is a Microsoft AddIn to Excel that allows you to find optimal solutions for certain decision problems defined by a certain Objective (cell value). It’s like the older brother of the previously mentioned Goal Seek as it allows for more complicated computations and optimization making. Let’s start by enabling it in Excel.

Enabling the Solver AddIn

Find Add-Ins in Excel Options

To open the Window go to FILE, select Options and then pick AddIns.

Analysis Toolpak: Excel Options
Solver: Excel Options

Next from the above window hit Go to progress to Step 2.

Open the Add-Ins window and enable the Solver Add-In

From the Window below select Solver Add-In and hit OK:

Solver: Enabling the AddIn
Solver: Enabling the AddIn

Using the Solver AddIn

Now similarly as above let’s again consider our Problem from above.
what if the problem
As previously let’s exemplify this tool with our Marketing Budget example. Quick reminder – we have 500k$ to spend on TV and Internet ads and we have a formula that calculates our spend to the resulting Product demand.

Now we want to know what is the optimal value of our spend on TV and Internet ads to maximize Product demand. This is exactly what we can calculate with Solver:

To open Solver window simply go to the DATA ribbon and you should find at your far right in a new group (Analysis) the Solver button. Hit it to open the Solver wizard:

Solver
Solver

Solver requires that you:

  • Specify the Objective – set a single cell that measures your Objective. Next select whether you want to Maximize, Minimize or set this value by changing certain variables (specified later)
  • Specify the Changing Variables – specify the variables (cells) which should be modified by Solver to optimize your Objective function (formula)
  • Specify your Constraints – specify cells which should evaluate to certain constraints limiting the values of the Changing Variables

I used the settings above to run Solver on my Problem. See the results below:

Solver: Problem results
Solver: Problem results

The results are perfect (99 for TV ads and 401 for Internet ads)! Solver managed to find the optimal solution to my problem. Some of you probably thought the answer to be 100 and 400 :) – that was the hinted answer but not the optimal one! This is the power of Solver.

MS Query (SQL)

If you have been around my blog long enough you might have noticed my affection towards SQL in Excel manifested by my SQL Excel AddIn. I consider SQL a significant tool in the Data Analysis Excel toolbox. There are simply some things you won’t do easily with a PivotTable that can be easily written in a few SQL lines of code.

Let’s demonstrate a simple example of how to create a MS Query using the DATA ribbon tab using a simple source worksheet – Sheet1:
SQL Example data set

Open the MS Query window

Go to the DATA ribbon tab, click From Other Sources and then select From Microsoft Query.

Add MS Query (SQL)
Add MS Query (SQL)

Follow the MS Query window wizard to create your MS Query

This is the hard part. In my example I want to run a query against my working Workbook (Sheet1). See screens below:

Selecting the Data Source
Selecting the Data Source

Selecting the Excel file (can be your current Workbook)
Selecting the Excel file (can be your current Workbook)

Selecting the Worksheet and columns for your Query
Selecting the Worksheet and columns for your Query

Return data to Worksheet or Edit Query
Return data to Worksheet or Edit Query

Edit the Query (optional)
Edit the Query (optional)

Return Data to Worksheet (as Table/PivotTable/PivotChart)
Return Data to Worksheet (as Table/PivotTable/PivotChart)

Indeed that’s a lot of steps right? Hence, if you consider using MS Query (SQL) I strongly recommend my SQL Excel AddIn. It does the same in just 1 step. It does not add any new features to Excel but simply makes it so easy to create MS Queries in Excel – so you can just use it to create the queries using the AddIn. The AddIn is not required to use them.

Refresh or Edit your MS Query

This is the wonderful part – your MS Query is represented in Excel as a QueryTable. A queryable Excel Table that can be Refreshed or modified. See the options below available for QueryTables:

Refreshing/Editing the MS Query
Refreshing/Editing the MS Query

Summary: Data Analysis Excel

It is worth at least being aware of the wide range of Excel Data Analysis tools that are available by DEFAULT in Excel. Most Excel users are limited to the PivotTable and maybe Solver (2 common Excel questions asked for Analyst job interviews). However, Excel contains much more Data Analysis Excel tools than just those two.

I personally see much value in the MS Query (SQL) feature which has been so successfully hidden in Excel and made so hard to setup that it is simply neglected. Fortunately, there is a way around that with my SQL AddIn which I myself use on a regular basis. SQL however is a powerful tool (and language) and often allows you to create queries that can replace VBA macros and in most cases will run faster than VBA.

autofilter featured

Automatic Excel Autofilter

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

With today’s Excel tip of the day let’s have some fun with learning how to create an auto-applying filter to an Excel Table (or range). Let’s jump right to see how we expect our Excel Autofilter to work:

excel autofilter
Excel Autofilter: Type while the table is filtered
We want to achieve the following behavior where our Table column is filtered as we type. This is a very convenient feature especially if you need to dynamically sit through a large dataset of different values. It is perfectly possible to be done in Excel with just a little VBA code.

The whole exercise takes just a minute or so – therefore I find the Excel Autofilter a really useful and flexible tool to use.

How to create an Autofilter

Feel free to review my video or read through for a step-by-step tutorial:

Let’s assume we have a similar dataset as shown above. This does not need be limited to a single column but can be spread out across as many columns we want. To facilitate this example I have however limited the data just to 1 data column of a simple list of first names.

Convert the Range to a Table

This step is not necessary, although will make matters much easier when wanting to create the macro as we move on.

convert to table
Convert the column to an Excel Table

Insert a TextBox from the Developer tab

Now we need to go to the DEVELOPER tab and select Insert and pick Text Box from the ActiveX Controls section.

insert a textbox
Insert a TextBox control

It is suggested that you name your text box appropriately e.g. “NameTextBox”.

Add filtering macro to TextBox Change event

Now the hard part. To create our Excel Autofilter we need to make sure that a filtering event is triggered whenever we want it to happen. Fortunately the TextBox ActiveX Control has a Change Event. The Change Event is triggered whenever the text in the Text Box is modified.

Open the DEVELOPER tab and open your VBA Project using the Visual Basic button. Now make sure to open the Worksheet on which your data and the Text Box is located as shown below:

macro placement
Open the Worksheet where the filtering takes place
.
Now assuming the textbox is named “NameTextBox” and your Table is named “Names” paste the following macro into your Worksheet module:

Private Sub NameTextBox_Change()
    ActiveSheet.ListObjects("Names").Range _
        .AutoFilter Field:=1, Criteria1:="=*" & NameTextBox.Text & "*"
End Sub

Notice that the criteria is specified as follows “=*” & NameTextBox.Text & “*”. How to understand this? The “*” symbol is a wildcard which captures any number of characters (0 or more). Hence this expression will capture any string containing the text we type in our text box. If you want to filter names beginning with the string you are typing simply replace it with: “=” & NameTextBox.Text & “*” – notice I removed the first “*”.

Download the example

Feel free to download the working Excel Autofilter example here:

merge worksheets

Merging worksheets / tables in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

Often we need to merge worksheets / workbooks containing lots of data. Imagine receiving periodical daily reports and wanting to quickly consolidate them to generate a weekly or monthly report… seems like a lot of work. Merging worksheets does not necessarily need to be hard… as long as you read through today’s post.

Merging worksheets with VBA

To exemplify the issue let’s consider a Workbook consisting of 2 worksheets (below named Sheet1 and Sheet2) with identical columns.

consolidate worksheets
Consolidating worksheets: Example data

What we want to do is merge these 2 worksheets into one consolidated worksheet. We may of course want to do this in multiple ways e.g. by:

  • Simply appending the worksheets to each other
  • Merging the data sets while removing duplicates

Here is the example result we want to achieve (Sheet2 appended to Sheet1 on a third separate Worksheet):

merging worksheets
Merging worksheets: Result

In today’s Tip of the Day I will exemplify how to merge worksheets / tables (any number) with an option of eliminating duplicates.

Merging worksheets with VBA

Let’s first consider a simple piece of VBA code. The procedure below can handle any number of Worksheets – including Worksheets from external Workbooks as long as you have the Workbook open and provide it within the array of Worksheets passed to the procedure.

VBA Code

Sub Merge(ws() As Worksheet, destWs As Worksheet, headerInFirstRow As Boolean, removeDuplicates As Boolean)
    'Clear destination worksheet
    destWs.UsedRange.EntireRow.Delete
    Dim pasteRange As Range, header As Range, firstFreeRow As Range, w As Variant, copyRange As Range
    'Paste header
    If headerInFirstRow Then
        Set header = ws(0).UsedRange.Cells(1).EntireRow:
        header.Copy destWs.Cells(1).EntireRow
    End If
    Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    'Paste worksheets
    For Each w In ws
        Set copyRange = w.UsedRange.Rows("" & _
            IIf(headerInFirstRow, 2, 1) & ":" & w.UsedRange.Rows.Count)
        copyRange.Copy firstFreeRow.Cells(1, 1)
        Set copyRange = Nothing
        Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    Next w
    'Remove duplicates
    If removeDuplicates Then
        Dim colArr As Variant, col As Long: ReDim colArr(0 To destWs.UsedRange.Columns.Count - 1)
        For col = 1 To destWs.UsedRange.Columns.Count
            colArr(col - 1) = col
        Next col
        destWs.UsedRange.removeDuplicates Columns:=(colArr), header:=IIf(headerInFirstRow, xlYes, xlNo)
    End If
    'Clean
    Set firstFreeRow = Nothing: Set w = Nothing: Set header = Nothing
End Sub

  

A now a simple example of this procedure being used assuming the example data I referenced above. Let’s assume data on is on worksheets Sheet1 and Sheet2, the destination Worksheet will be Sheet3.

Sub TestMerge()
    Dim ws(0 To 1) As Worksheet
    Set ws(0) = Sheet1
    Set ws(1) = Sheet2
    Merge ws, Sheet3, True, False
End Sub

Notice that the Merge procedure accepts the following parameters:

  • ws – an array of Worksheets
  • destWs – the destination Worksheet
  • headerInFirstRow – if true assumes that the header is in the first row of each source Worksheet
  • removeDuplicates – if true remove all duplicates after merging the Worksheets

Simple enough right? Not too long. If you want to optimize it for best performance read this.

Merging worksheets with MS Query (SQL)

Now let’s consider a second approach – using MS Query (SQL). This is my personal favorite as MS Query SQL code is short, does not require VBA and the destination Worksheet can be refresh by simply right clicking on the table and hitting refresh.

To create a MS Query feel free to use my free Excel SQL AddIn or follow the steps below:

merge worksheets excel
SQL AddIn: Merging worksheets

Follow the instructions below if you don’t have my free Excel SQL AddIn:

Open the From Microsoft Query Wizard

Data ribbon and then selecting From Other Sources and From Microsoft Query -> till the end be sure to hit View Data or Edit Query in Microsoft Query to be able to provide your own SQL query.

Proceed according to Wizard to edit SQL Query

As we want to merge several Worksheets within our current Excel Workbook we need to select Excel Files as the designated data source for our MS Query:

Select From Excel Files data source
Select From Excel Files data source

Next we need to select our Current Excel Workbook from the File Dialog:
Select your current Excel Workbook
Select your current Excel Workbook

Select the first Excel Worksheet you want to merge and click the right arrow > to drag it to the Columns in your Query section.
Select any Worksheet you want to merge to proceed
Select any Worksheet you want to merge to proceed

Next instead of Returning the Data hit the View data or edit Query in Microsoft Query radio button and proceed:
Proceed to edit the MS Query
Proceed to edit the MS Query

A new Window should pop-up. Look for the SQL button to modify the existing MS Query:
Hit SQL to edit MS Query
Hit SQL to edit MS Query

Modify the SQL Query to merge Worksheets

A new SQL Window should pop-up. You can modify any number of Worksheets using this approach. Below I am assuming I want to merge two Worksheets – Sheet1 and Sheet2. The MS Query need to therefore look like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$]

Simply modify the existing MS Query in the SQL Window and hit OK.

What if you need to merge 3 Worksheets not just 1? Simply append an additional UNION ALL like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$] UNION ALL SELECT * FROM [Sheet3$]

For additional Worksheets repeat this pattern

Return Data to selected Worksheet

Return Data to Excel Workbook
Return Data to Excel Workbook
Now the pleasant part – loading the Query data. Look for the Return Data button, select a cell where the MS Query is supposed to load and hit OK.

Comment on using the MS Query approach

Simple and quick – this approach is more efficient than any VBA code we may develop as it is handled by the OLEDB driver. MS Query (SQL) is also more efficient in handling large chunks of data as compared to other approaches.

Merging worksheets with PowerQuery

If you are not familiar with PowerQuery feel free to download it from Microsoft here. PowerQuery features a lot of neat data-crunching / BI features. Let’s see how can we leverage PowerQuery to do our job.

Create tables from each source Worksheet

First what we need to do is link our source tables to PowerQuery so we can create the output query.
consolidate2
Select the whole range of each source worksheet (CTRL + A)
and select the From Table option from the PowerQuery ribbon. For this example I have named Sheet1 as Table1 and similarly with Sheet2.

Merge the Worksheets

So now that we have our Tables configured in PowerQuery we can append the 2 (or more). Hit the Append button from the Combine group in the PowerQuery ribbon.

PowerQuery - Combine
PowerQuery – Combine

Next select Table1 from the first drop-down and Table2 from the second drop-down:

PowerQuery - Append
PowerQuery – Append

and hit OK

Note: When wanting to append multiple (>2) worksheets simply repeat this step. The first Table will need to be the Appended one from the remaining tables

That’s it! Similarly as with the MS Query example you can always easily refresh your query by right-clicking and hitting Refresh!

Summary

I hope you found these methods useful. On one last note I would like to share my thoughts on how these methods compare.

Personally I prefer to use MS Query (SQL) via my SQL AddIn. This sentiment may of course be partially as I used to work a lot with databases and am used to coding SQL. I know for most Excel users out there this is not the case. In such cases and if you only have a couple of Worksheets you need to merge on a regular basis I do suggest downloading the PowerQuery AddIn. It’s easy to use and also allows you to refresh your query easily. In all other scenarios feel free to use the VBA approach and simply customize your code to work best for you!

Let me know if you found this useful! And be sure to subscribe to more Tips on my Twitter or Facebook page!

PivotTable

Excel Pivot Table Tabular layout

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Pivot Tables are a fantastic tool for slicing and dicing data. Sometimes, however, you need to spend quite a lot of time to achieve the right layout for your data. Today a short tutorial on how to achieve a pure pivot table tabular layout. Enjoy.

Tabular Form vs Compact Form

Let’s assume we have a table of data as such:
tabular1
When creating a standard Pivot Table rows get appear in a treelike layout called the Compact Form as show below:
compact layout
What we want hover is some thing more like this in something called a Tabular Form:
tabular2

Pivot Table Tabular Form

Below a short video tutorial of how to properly configure a Pivot Table to achieve a pure Tabular layout.

Video Tutorial

Step by step tutorial

Create a Pivot Table

  • Select all your data (CTRL + A)
  • Go to INSERT and select PivotTable

Select Tabular Form

  • Click on the Pivot Table and go to DESIGN
  • Select from Report Layout the option Show in Tabular Form

Remove subtotals for a pure Tabular layout

Repeat this step for each Subtotal row:

  • Click on the Subtotal DESIGN
  • Deselect Subtotal "ROW NAME"
pivot table data

Reverse engineering an Excel PivotTable (recovering Pivot Table data)

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 3.00 out of 5)
Loading...

Today a quick tip that helped me out of a tight spot during one of my projects. The issue was that I received an Excel file with multiple PivotTables, but no source tables on which these PivotTables were built (these were in separate worksheets that were not shared). I needed the source data to produce my own custom complex reports (for which I use MS Queries instead of PivotTables), and I needed it now. Fortunately, PivotTables contain embedded source tables and there is a quick and easy way to recover Pivot Table data. Read on to learn how… maybe it will come useful.

How to reverse engineer a PivotTable

Let’s assume we have the following Excel PivotTable below.

Pivot Table Data
The PivotTable

In order to recover the Pivot Table data associated with the PivotTable you need to: scroll down to the right-most, bottom-most Grand Total value in the PivotTable.
Pivot Table data: Reverse engineering a PivotTable
Double Click on the Grand Total in the bottom right-most corner

Now simply double-click on the Grand Total and the source table will appear on a separate Excel Worksheet as show below:
Pivot Table data
The resulting source Pivot Table data

Easy right? Hope this helps you in similar situations – if yes, I appreciate a comment below!