Basic Pivot Table Example

Microsoft describes PivotTables as “A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both reports enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable and PivotChart reports.” (see here).

And indeed PivotTable is a query tool that quickly generates reports on top of a base table of data. Once you get the basics, it is easy, interactive and very useful tool to know.

As always, let’s see that ability in a simple example:

Let’s assume we have a detailed report of product sales transactions. The report has a lot of information about each transaction, including the product code, the geo, the channel, and the amount.

sales data

And we want to run reports and analyze this date. We do that by creating Pivot Table like this:

Select the entire data area and go to the “Data” menu and select “PivotTable…”. A menu like below will open:

PivotTable dialog

Adjust the range if needed and for this simple example just leave all other parameters without a change.

The result is a new sheet that looks like this:

PivotTable Empty

Now we can dynamically select items to display and to summarize.

The simplest example: Let’s create a report of the total sales by GEO.

1. In the “PivotTable Builder” dialog, Drag & Drop the “Geo” field to the “Row Labels”.

2. Drag & Drop the “Price” field to the “Values”. By default the Count function will be applied.

3. Click on the little “i” near the Price field and a new dialog will be shown like below. Select there the function “Sum”.

PivotTable Function selection

4.Format the PivotTable column to show nice $ formatted data.

You should get a report like this:

Basic PivotTable report

Now let’s extend this example and create a report showing Total and Average price by Geo/Channel.

We will continue from the previous report.

1. Open again the “PivotTable Builder” dialog by either clicking on the previous report or by navigating to the PivotTable tab option and clicking on the Builder icon.

2. Drag & Drop the “Channel” field to the “Row Labels”. Make sure it is the second row on that list.

3. Drag & Drop the “Price” field to the “Values”. Should now have two lines under “Values”.

4. Adjust the second “Price” line to use Average function. At this stage the Builder dialog should look like this:

PivotTable Builder 2

5. Format the result report as needed.

You should have a report like this:

Basic PivotTable report 2

We are now masters of the simple PivotTable example.

Advertisements

Excel COUNTIF Condition Examples

A revised version of this content is now available at blog.officewizkid.com


Excel COUNTIF is one of these functions that are simple enough to understand the concept but make you scratch your head when you try to do something less trivial with the condition part.

Sure, simple cases are simple enough:

How to count all the cells in a range A1:A100 that have a specific value called “MyValue”:

=COUNTIF(A1:A100,"MyValue")

But how do you scale to more complex scenarios. E.g. How do you calculate COUNTIF with a condition that references another cell?

Here is a very nice blog with multiple examples reviewing the hidden potential of the COUNTIF function.

How to use Excel COUNTIFS and COUNTIF with multiple criteria

How To Use Excel Tutorials

A lot of what I have been writing about in this blog is related to Excel. A growing number of tips, tricks, happy moments and frustrating moments. At this stage there is enough content to try to collect that content in one place with some sort of organization. So here it is!

Check out how-to-use-excel.manha10.com

I plan to continue to write additional content in this blog and also make updates to the site as kind of a catalog of the entries. I hope you’ll find this useful.

Excel on Mac Get Web Content (Get HTTP)

I made the switch to Mac and now some of my old Excel macros that are depended on the operating system do not work. One particular item is the ability to fetch data from the Web. So here I am after many years using Office and Excel learning again. This is fun!

Ok, let’s get series now…Today’s goal is to build a user defined function that can be called from the Excel datasheet and fetch a web page. We will call that function getHTTP and it will work on Mac (but not on Windows).

Luck is on my side as there are many others looking for the same solution. I found an entry called “How do I issue an HTTP GET from Excel VBA for Mac 2011” (See it here) and it has exactly what we need.

Navigation in Excel on Mac is just a little bit different so here are steps by step instructions:

1. Open an Excel file or create a new one.

2. Save as  “Excel Macro-Enabled Workbook (.xlsm)” file.

3. From the menu navigate to Tools->Macro->Macros…

4. In the “Macro Name:” field type the name of the new Macro we want to create. In this case getHTTP. You should see a screen like this:

Excel on Mac Create Macro

 

5. Click “Create” and you will find yourself in the VBA editor with an empty Sub called getHTTP.

6. Next we will copy paste the VBA code from below (taken from the link at stackoverflow listed above with minor change of the function name from HTTPGet to getHTTP just cause I like this format better):


Option Explicit
' execShell() function courtesy of Robert Knight via StackOverflow
' http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac
Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long


Function execShell(command As String, Optional ByRef exitCode As Long) As String
Dim file As Long
file = popen(command, "r")
If file = 0 Then
Exit Function
End If
While feof(file) = 0
Dim chunk As String
Dim read As Long
chunk = Space(50)
read = fread(chunk, 1, Len(chunk) - 1, file)
If read > 0 Then
chunk = Left$(chunk, read)
execShell = execShell & chunk
End If
Wend
exitCode = pclose(file)
End Function


Function getHTTP(sUrl As String, sQuery As String) As String
Dim sCmd As String
Dim sResult As String
Dim lExitCode As Long
sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sResult = execShell(sCmd, lExitCode)
' ToDo check lExitCode
getHTTP = sResult
End Function

7. And we are ready to give it a try… Leave the VBA editor and return to the Excel sheet.

8. Let’s put in cell A2 the value: http://api.openweathermap.org/data/2.1/forecast/city

9. Let’s pub in cell B2 the value: q=Boston

10. Let’s pub in cell C2 the value: =getHTTP(A2,B2)

11. The page should refresh and you should see a lot of gibberish JSON things. Something like this:

Excel on Mac Get Web Content (Get HTTP)

This is what we wanted! We are done!

Excel Protect Your Worksheet (or part of it)

When an excel worksheet is shared among multiple team members there is sometimes a need to restrict the parts of the worksheet that are open for changes and keep other parts of the worksheet protected from manual changes. It is not necessarily an information security control but more likely a productivity issue to protect against human errors of edits done to static or calculated cells.

Excel supports this scenario very well by selecting specific sheets, columns or even cells to be protected from changes. You can also decide what level of restrictions you would like to enforce, such as allowing copy/paste or not, allowing format changes or not and more. Let’s see how it is done…
The simplest way to setup protection is for the whole worksheet or to a specific sheet. For that on the main menu select “Review” and either select “Protect Sheet” or “Protect Workbook”. You will see the protection menu:

2014-01 Excel.data.protection.1

The first thing you could do on this menu is to setup a password too be used to unprotect the sheet or worksheet. The second part is related to what can and cannot be done on the locked cells. We will explore what it means the “locked cells” but for now it is enough to note that by default all the cells are locked. In this the two top options are checked and the rest of the options are unchecked. Which means that both locked and unlocked content can be selected and copy, but the locked content will not allow any changes to these cells.  And if a user tries to make a change to the content they will encounter the following message:

2014-01 Excel.data.protection.2

So this is cool but what if the same sheet includes some data that should be protected and some data that should not be protected. This is where the definition of “Locked Cells” and “Unlocked Cells” comme to play.

Let’s say that we need to protect column A but we want column B to be still open for everyone to change. To achieve that, we select column A, open the right mouse menu and select the “Format Cells…”. A new popup dialog will open and we select the right most Tab called “Protection”:

2014-01 Excel.data.protection.3

The “Locked” checkbox will be checked. Now we do the same for column B (that we want to enable for changes) and uncheck the “Locked” checkbox.

At this stage we should have column “A” with “Locked” checked and column “B” with “Locked” not checked.

Now when we the sheet is in Protected mode, column A will be locked for changes while column B will be open for changes.

See even more details and examples at the Microsoft site:

http://office.microsoft.com/en-us/excel-help/lock-cells-in-a-worksheet-HA010342979.aspx

Excel Worksheet with Inline Charts with Sparklines (Part 2)

In a previous entry I talked a little bit about an old way to embed create charts inside Excel cells (as appose to having the charts float on top of the sheet).  In this entry I will demonstrate the Sparklines cool ability. Sparklines are built-in ability to do the same but much simpler and much nicer. I believe Spaklines are relatively new and were introduced in Excel 2010.

The goal of the feature is to embed a mini-chart inside a cell so your datasheets become more visually pleasing without going to the extra sophisticated workarounds that we’ve all developed over the years.

So this is how it works…Let’s say that we have a simple Year-Over-Year datasheet and we want to add column E with embedded charts:

2013-12 Excel Worksheets with Inline Charts & Graphs (Part 2).1

Select cell E2 and click on the Ribbon menu on “Insert”. You can see that right next to the Charts option there is a box called Sparklines. Like this:

2013-12 Excel Worksheets with Inline Charts & Graphs (Part 2).2

Click on the line option. You will be prompt to a Data Range dialog so specify A2:D2 like the following:

2013-12 Excel Worksheets with Inline Charts & Graphs (Part 2).3

Copy the same from E2 to the rest of the E column and you should get the following result:

2013-12 Excel Worksheets with Inline Charts & Graphs (Part 2).4

You can as easily do the same with Column chart:

2013-12 Excel Worksheets with Inline Charts & Graphs (Part 2).5

The nice thing is that this is part of the Excel Sheet and not an overlay. It is also very easy to do and provides your Excel Sheets a little bit more clarity and visual representation.

Excel Worksheet with Inline Charts & Graphs (Part 1)

Most people know that Excel has a solid chart system that can draw variety of chart types ranging from simple pie chart to a more complex 3D Radar chart and for each chart types there are tens if not hundreds configuration options. Most people also know that these charts are auxiliary to the Excel worksheet itself. One can overlay a chart on top of a worksheet but one can not embed a chart into one of the cells. It usually looks like the following:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).1

Note that the bar chart is somewhat disconnected from the source data and the chart is an overlay on top of the worksheet. Well, there are better options to do this…

The first way to embed a bar chart in a cell is the old way. It is really a workaround and it is by dynamically generating a string that will look like a chart. We can do that with the function REPT to repeat a character such as “|” and generate the needed bar. This is something I saw first at the following place:

http://omberg.wordpress.com/excel/excel-guides/how-to-create-an-in-cell-chart/

Let’s assume that cell D2 contains a value and we want cell E2 to include a graphical representation of that value, then we can use the function:

=REPT(“|”,D2)&” “&D2

Which means: Repeat the “|” character D2 times and at the end add the string representation of D2.

If we repeat that calculation for every cell in the D column we will have something like this:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).2

I cheated a little bit as I used conditional formatting for the colors and used one of the narrow fonts like “Arial Narrow”.

Now let’s continue. Replace “|” with the character “g” but use the funky font Webdings. Also reduce the font size to 4 or so and we get something that looks closer to charts:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).3

That is nice but we can do better. In Excel 2010 there is a new thing called Sparklines. I will talk about this next time…