Learn to convert cross tab data into tabular data to which we can apply PivotTables,
Filters, and Sorts.
A Common Problem
The bottom table at right is an example Cross tab table. It looks great and is easy to add data to when small. But even when small, try summingTerritory sales from it.
If we are good with formulas, we rate this a trivial task until we consider, “what happens when we add a month?” Suddenly traditional formulas start to get a little messy. And what happens when we finish the year? Will we extend the table and re-label all columns to include year and month? Or will we create a new worksheet for the next year and start this table all over again? Hmmm… what would a total formula across multiple worksheets look like then?
Again, those of us with formula skills know this is possible. Good for us. But why go through all that effort when PivotTables sum data without formulas? No matter how quick we are with formulas, we can create PivotTables over tabular data in a fraction of the time with superior results. PivotTables provide slicers, drilldown, PivotCharts and a whole lot more.
The benefits of well-structured data far outweigh the aesthetics of cross tab formats. That does not mean we have to abandon aesthetics when aesthetics count because we can present tabular data in PivotTables easily.
The UnPivot add-in makes converting cross tab data to PivotTable friendly, Auto-Filter friendly, Dynamic Form friendly, formula friendly, database friendly tabular formats quick and effortless. Plus, UnPivot provides a bonus feature that allows us to use cross tabs as input forms and append data entered into these forms to our master table so we can perform XL miracles over tabular data AND have the ease of entry associated with cross tab data..
Here’s how to get UnPivot. And yes, it’s free and the code totally unprotected so those new to VBA can learn from it or modify it as needed.
|Download the PDF at: https://dl.dropboxusercontent.com/u/13737137/Projects/UnPivot/UnPivot.pdf|
|Discuss this post or other BXL topics at: facebook.com/BeyondExcel|
Want a data entry form that creates itself?
Forms make presenting and changing information in remote tables possible. Dynamic Forms make creating those forms effortless. Read on to learn how to create forms that design themselves to accommodate any XL table complete with data validation. The user just selects a row, presses SHIFT-CTRL-D, and the form creates itself instantly.
Why I Did This
I created an app to design my kitchen. The app uses XL’s shapes to draw floor plans and cabinet positions. Each shape had to be precisely drawn and positioned which required entering dimensions into a table. But having the table on the drawing’s worksheet was clumsy and ugly. I needed to work with shapes on one worksheet and enter dimension in a table on a different worksheet at the same time. That requires a form.
Options 1 – Create UserForm Manually
I considered creating a form the old fashion way:
- manually adding labels and text boxes
- setting each control’s properties
- coding routines to move data between the table and the controls
- coding validation routines
For the particular table I was working with, that meant 21 labels, 20 text boxes, 1 combo box, and 2 command buttons. That seemed like a lot of duplicate work considering the table had the data validation already.
Option 2 – Use XL’s List Form
Since 2003, XL has had an option to create dynamic forms over lists using a single line of code (see: Add Forms to Edit Tables). Unfortunately XL’s dynamic form has two shortcomings: it only works with the active worksheet and it can’t start on a specified table row.
Option 3 – Create a Dynamic Form
I wanted a method that:
- created the form for me using the table’s data validation, column headings, and cell protection
- worked over tables not necessarily on the same worksheet
- worked only on rows I specified.
Solution – frmData
frmData is a blank user form with:
- OK and Exit command buttons
- a textbox for user messages
- code to add all other controls based on the table sent to it.
frmData is really all I needed but I wanted more.
Enhancement #1 – clsInpMsg
I also wanted the data validation’s input message displayed when editing a table’s cell. For that I created class clsInpMsg. clsInpMsg responds to labels, textboxes, and combo boxes being selected or the mouse hovering over them and puts the associated cell’s data validation input message in frmData’s user messages textbox.
Enhancement #2 – clsForm
I also wanted the form to be a little more dynamic and fun than the standard drab gray user form. So I included class clsForm. clsForm adds a worksheet’s theme colors and “glowing” effects for command buttons when the mouse hovers over them. If you want normal, just remove two lines from frmData and (optionally) remove clsForm.
DynamicForm.xlam is an Add-In with all forms and classes assembled together for easy workbook integration.
AddInDemo.xls demonstrates the DynamicForm Add-in and shows how to dynamically install/uninstall add-ins when projects open/close.
Want it? Get it.
Here is a PDF explaining everything: DynamicForm.PDF
Introducing a new approach to Gantt Charts in XL – BabyGantt.xls
This type creates a stacked bar chart with the first series being the duration from project start to task start. This series’ fill color is set to “No Fill” so it appears that the second series – the actual task – is floating by itself. It is a clever and effective means to small Gantts – about 30 or less tasks. It can leverage XL2007 and later’s improved aesthetics.
This type uses spreadsheet cells and conditional formatting to make colored cells into Gantt Chart bars. This method can accommodate many more tasks but also creates a lot of formulas and calculations.
BXL’s Baby Gantt uses shapes and VBA. It has the advantage of Conditional Formatting’s “many tasks” without the formula overhead. It also shares the Bar Chart’s improved graphics capabilities. But best of all, there’s no setup – at least – not for the chart.
All Gantt Charts require tasks and dates. But that’s all Baby Gantt requires us to enter. We don’t need to add and configure charts or enter and autofill formulas across massive ranges. The VBA in Baby Gantt reads our task entries and generates the graphics on the fly. And because it is Excel, we can use formulas to calculate task start and end dates.
You can build BabyGantt.xls yourself or download it from my DropBox. Here is a link to the instructions:
The instructions include:
- User Instructions – Step by Step guides on how to enter your project with examples
- Download Instructions – Links to a fully functioning, totally free, unprotected BabyGantt.xls
- Technical Documentation – All of the code with explanations and all data validation entries with screenshots.
Moma Gantt Testers Wanted!
If you would like to be part of the MomaGantt.xls test team please email me at Craig_Hatmaker at Yahoo.com
Getting data from IBM’s iSeries into XL is normally no different than other platforms. There are exceptions. The iSeries’ multi-member files, such as source files, are problematic for SQL. So are data areas. This post deals with simple Data Areas.
To read simple Data Areas from SQL we need a little help from IBM’s iSeries. We need a “stored procedure” which we must create, which means we must program on the iSeries. This post assumes you have access to your iSeries via a “green screen”. If you don’t know what I’m talking about, bale now because this won’t make any sense to you.
I’m going to use an example to help explain things. In this example imagine we have a data area containing a numeric ID. The steps to create this stored procedure are:
- Code CL source to pass parameters and retrive data area’s contents
- Create a CL Module from the code source
- Create a Service Program from the CL Module.
- Code an SQL Script to create a stored procdure using the Service Program
- Run the SQL script
The first step is to write the CL source. Let’s put it in member GetMyData in QGPL/QCLSRC. Here is the example:
PGM PARM(&ID) DCL VAR(&ID) TYPE(*DEC) LEN(10 0) RTVDTAARA DTAARA(MYDATA) RTNVAR(&ID) ENDPGM
The next step is to create the CL Module. At a command prompt type:
CRTCLMOD MODULE(QGPL/GetMyData) SRCFILE(QGPL/QCLSRC)
After the above executes succesfully, type this command:
CRTSRVPGM SRVPGM(QGPL/GetMyData) MODULE(QGPL/GetMyData) EXPORT(*ALL)
Next we write our SQL Script. Create source member GetMyData in QGPL/QDDSSRC:
Create Function GetMyData() Returns DECIMAL(10, 0) Language CL Specific GetMyData Deterministic No SQL Returns Null on Null Input No External Action Not Fenced External Name 'QGPL/GetMyData(GetMyData)' Parameter Style SQL;
Lastly, we run the SQL script from a command line:
RUNSQLSTM SRCFILE(QGPL/QDDSSRC) SRCMBR(GetMyData)
Now all that is left to do is test it. Using command STRSQL to start an SQL session, run this SQL statement:
Select GetMyData() From SYSIBM/SYSDUMMY1
SYSIBM/SYSDUMMY1 is a special one record file we can use for this sort of thing.
As you can see, there’s not a lot of work involved once you know how. And if you look carefully, you can see many possibilities for doing things far beyond reading simple data areas!
- IBM iSeries Redbooks: http://www.redbooks.ibm.com/portals/systemi
- A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support: http://publib-b.boulder.ibm.com/abstracts/sg245183.html?Open
- Stored Procedures, Triggers, and User-Defined Functions on DB2: http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf
This question was posed and answered by “Mr. Excel” and “Excel is Fun” in a recent YouTube post by the same title. Their solutions work well. I’m going to answer with what I believe is a better approach. It uses Tables. Tables have some great characteristics:
|Dynamic||Tables grow/shrink as rows/columns are add/deleted.|
|Nieghbor Aware||Tables automatically move other tables as they grow (with some restrictions)|
|Structured References||In Excel 2007 and higher (XL’07+), tables create special names so we can address a table’s column by its heading. Ex: =Table[Column]|
|Stylish||Table Styles (in XL’07+) dramatically improve data formatting.|
|Links||They can link to external data.|
|Duplicate Removal||XL’07+ includes a menu option to remove duplicate rows.|
Creating tables is easy. If you have a range that contains data, simply select any cell in the range and use the keyboard shortcut: CTRL-L (in XL2007 and later you can also use the more intuitive CTRL-T). Once a table has been created, you can change its name by selecting any cell in the table and using the menu tab Table Tools. The Table Name input box is on the left in the Properties grouping.
Tables have some drawbacks.
|Not Supported Prior to XL’03||So if you’re using old versions of Excel, use the “Mr.Excel” or “Excel is Fun” approach – or upgrade and start using Excel’s Tables.|
|External Data Source Restriction||MS Query and Data Validation can’t see tables without a Named Range Wrapper.|
A Named Range Wrapper (NRW) is simply a name assigned to a range. To create an NRW for a table just select the table’s entire range and assign a name by typing it into Excel’s Name Box or using the menu path Formulas > Define Name. If you are working with a .xls, you’re done. But if you’re working with a any of the new formats (.xlsx, .xlsm) you must change the name’s Refers to: reference from a table reference to A1 notation. That’s it. The new range is every bit as dynamic as the best dynamic named range formula – without the formula.
“Can there be Dynamic Ranges for VLOOKUP?”
In XL’07+ VLOOKUP works with tables already. If you have a table called “Products” you can use it in VLOOKUP like this:
=VLOOKUP(“ABC”, Products, 2, 0)
Where “ABC” is the value in the first column of Products you’re searching for. In XL’03 VLOOKUP needs an NRW. Once the NRW is created, we use it as we did above.
“Can there be Dynamic Ranges for Data Validation?”
Data Validation does not recognize tables but we can still use them. We can either apply a “Named Range Wrapper” over the table and use the name in Data Validation or we can exploit a quirk of using tables.
QUIRK! Any reference placed over a table becomes dynamic, even absolute cell addresses.
So if we have a table in cells $A$4:$C$10 and we want our Data Validation rule to use the first column’s values (excluding the header) as a list, we can put =$A$5:$A$10 in Data Validation’s Source: box. Now if we add entries to our table, the Source box’s values will change automagically! How cool is that!
Clearly, tables are far simpler than the “Mr.Excel” and “Excel is Fun” approaches (both good approaches, both compatible with older XL, neither requiring VBA). Note that we will never have to worry about other tables on the worksheet like we would with the traditional “COUNT” approaches. We don’t have to worry about the table’s location – ever. We don’t have to worry about blanks, or numeric data, or character data or any combinations thereof that can throw off COUNT methods.
The company president’s mother died. The staff wanted to express their sympathy. Cards were purchased and now we needed to make sure everyone got a chance to sign them. We needed names on a check list to circulate with the cards so when someone signed, they could check their name and hand the card to someone whose name remained unchecked. So how do we make a list with everyone’s name and make sure no one is left off?
Everyone in our office has a computer. Everyone uses Outlook. All we needed was a quick way to get all user names into Excel to edit, format and print. This is different from the contact list. The contact list may, or may not have all users in it and it definitely has far more names than users.
NOTE! Requires Excel 2003 or later because of ListObject
Open Excel and get to the VBE (Alt-F11)
- Use Ctrl-R to bring up the Project Explorer
Double click ThisWorkbook to bring up its code window
Enter this code then run it.
‘ Date Ini Modification
‘ 04/10/11 CWH Initial Programming
‘ 04/03/14 CWH Added email addresses
Dim olA As Object ‘Outlook.Application Start Outlook (OL)
Dim olNS As Object ‘Namespace OL identifiers context
Dim olAL As Object ‘AddressList An OL address list
Dim olAE As Object ‘AddressEntry An Address List entry
Dim lo As ListObject ‘An Excel Table
On Err GoTo ErrHandler
‘ Create a ListObject/Table in the spreadsheet
.Cells.ClearContents ‘Clear worksheet completely
.Cells.ClearFormats ‘Clear formats as well
[A4:B4] = Array(“Names”,”Email”) ‘Add a column headings
Set lo = .ListObjects.Add(1, [A4].CurrentRegion, , xlYes)
lo.Name = “Names”
‘ Open Outlook, set context, open “All Users” address list
Set olA = CreateObject(“Outlook.Application”)
Set olNS = olA.GetNamespace(“MAPI”)
Set olAL = olNS.AddressLists(“All Users”)
' Add each address entry name to the Excel Table For Each olAE In olAL.AddressEntries With lo.ListRows.Add .Range(1) = olAE.Name .Range(2) = olAE.GetExchangeUser.PrimarySmtpAddress End With Next
‘ Format Results
lo.HeaderRowRange.Style = ActiveWorkbook.Styles(“Heading 1″)
ActiveWindow.FreezePanes = True
‘ Do this ONLY if you want to close Outlook
ErrHandler: If Err.Number <> 0 Then MsgBox _ “Network_Users - Error#" & Err.Number & vbCrLf & _ Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext On Error GoTo 0
I’m departing from my usual post to share these gems with our community. Here are my top “10″ freebies:
#1 XenServer – Virtualize your physical servers with this free hypervisor.
#3a Microsoft Visual Studio Express – Develop fully functional, professional windows based applications with this little brother to Microsoft’s enterprise platform.
#3b Visual Web Developer Express – Create dynamic professional websites with this free version of Microsoft’s enterprise web development platform.
#3c VBA – You may not even know that if you have Excel, Word, Access, PowerPoint, Visio, Project, or Outlook … you also have a powerful development platform that can even (gasp) update mainframe databases. And that VBA you use in Office products is almost identical to the VB code in Visual Web Developer Express’ Active Server Pages.
#3d Microsoft Visual Studio Express for Phone – Need to create mobile apps? Start here.
#3e Microsoft Excel – Well, Excel isn’t free, but all you need for a roll-your-own Business Intelligence/Business Analysis solution is there. Many BI/BA solutions either use Excel directly as their front end, or their human interface looks a lot like it. All you have to do to make Excel emulate the big boy BI/BAs is some VBA code and a link to the data.
#3f MS Query – Bundled in every version of Excel, MS Query can link to any database and provide a graphical view of your company’s information.
#4 Skype – Free video calls to anywhere on the planet. Need to support remote PCs? Chat and share desktops. And if a picture is worth a thousand words, use Skype on a webcam laptop to show “Peggy” that dead server in your rack so you don’t have to repeat yourself a thousand times.
#5 Dropbox – Automatically sync local documents to cloud storage and access them from any computer connected to the web. Share them with anyone you choose or make them available to the public. No FTP skills required.
#6 Youtube – Broadcast training videos, product demonstrations, how-tos, president’s message, whatever to a select few or the entire world for nothing.
#7 Linked In – Network with other professionals in your field. No dues required.
#8 Google/Bing/Yahoo/et.al. – Okay, I know. These are not new. But how much are you paying for answers to tech questions available free from these web search engines?
#9 Facebook – Facebook? For the IT Director? Really? Yep. Throw that paper newsletter in the recycle bin and join the social media revolution. Put you company’s events, anniversaries, employee recognition, and more on Facebook and lock it down to employees only. Save paper. Save trees. Save time. Save money.
#11 Craig’s List – I had to add this one. We’ve been using Craig’s list to advertise job openings and the responses from it far outnumber paid media advertisements. Some of the applicants are even quite good.
Dynamic named ranges have been a subject of interest on Excel boards for sometime. Lots of people want to know what’s the best way to create a named range that expands (or shrinks) according to the data entered, and can be used in formulas like VLOOKUP without change. For some background on this subject see: Dynamic Ranges – Overcoming Shortcomings. In that post I promised to talk about what I believe is truly, the one best way to handle this kind of thing, Excel’s Tables.
In 2003, Excel introduced something called a “ListObject” also known as a “Table”. There are several features packed into Tables, but the main feature of importance in this discussion is their ability to grow or shrink dynamically. Out of the box, a table is a dynamic named range. So why agonize over what formula best creates one?
Creating tables is easy, simply select any cell within the desired range of data and press Ctrl-L (in Excel 2007 or 2010 you can also use Ctrl-T). Excel will probably guess correctly what range you meant but gives you the opportunity to specify exactly what you want. Whenever your selected cell is in a table Excel adds the Table Tools tab to its ribbon. There you can change the table’s name using the convenient “Table Name:” box in the upper left corner.
Unlike formula approaches, tables don’t mind other tables on a worksheet. And Tables come with right click menu functions for adding/inserting/deleting rows/columns. As you add rows or columns, the table automatically encompasses the new data, extends formats and formulas, and when rows are inserted, moves tables below to make room.
Tables are superior to named ranges in every way except one: they can’t be used as datasources. That’s easily overcome. Simply select the range you want to use as a datasource (that can be the entire table, just the data, a column…) and give it a name using the name box in the upper left corner of each worksheet, or using the name manager. The new name is every bit as dynamic as the best dynamic range formula – without the formula.
It’s Time to Move Ahead
At the time of this blog post, I still meet Excel fans enthusiastic over dynamic ranges. When asked “why not tables?” I usually get a blank stare. Some point out tables don’t work with all versions of Excel. True enough. But it’s been almost a decade now since XL 2003 came out. I’ve long since upgraded every user in the company I work for to Office 2003 or higher. I’ll bet your company has too. So put your favorite dynamic named range formula in a frame and hang it on the wall with other momentous from your past. It’s time to move ahead with tables.
This post shows how to animate a logo in Excel using VBA. (Click here to download code)
[kml_flashembed movie="http://www.youtube.com/v/97tZL3TJhWs" width="480" height="292" wmode="transparent" /]
Logos are an easy way to add a sense of professionalism to your work. Splash screens and logos are part of every software product purchased and should be part of every application you provide you customers. Besides, animating logos is fun. It entertains users and sets your work apart from the average Excel programmer. I use them when the workbook first opens. Here’s how it’s done.
Open a blank workbook. Rename the first tab “Data”. This will be the worksheet displayed when our workbook opens. Now get a copy of your company’s logo. Your company’s website probably has one about the right size. Paste it onto the Data worksheet. We need to name it “Logo”. To do that, select it, then use Alt-F11 to get to the Visual Basic Editor. Use Ctrl-G to display the Immediate Window. Type “Selection.Name = “Logo” and hit enter. Now we’re ready to code.
Use Ctrl-R to bring up the Project Explorer. Double click “ThisWorkBook.” That brings up the workbook’s code window. Our first routine must run when the workbook opens so we will code it in the Workbook_Open event. This routine activates the worksheet we renamed “Data”, and passes our “Logo” shape to two routines. The first “Grows” the logo into view. The second spins it once.
Private Sub Workbook_Open() Worksheets("Data").Activate ActiveSheet.Shapes("Logo").LockAspectRatio = False GrowShape ActiveSheet.Shapes("Logo"), 10 SpinShape ActiveSheet.Shapes("Logo"), 10 End Sub
The “Grow” routine expands any shape into view. It first turns off screen updating. This allows us to make changes behind the scenes and display the results when we’re ready. Next, the routine remembers the shape’s dimensions. Then the routine changes the shape’s size, displays it, freezes the screen, and loops back until finished. Before returning, the Grow routine makes sure the shape is exactly as we found it.
The Spin routine is nearly identical. It freezes the screen, remembers the shape’s original dimensions, then alters the shapes size in a loop that simulates the shape spinning around its Y axis. When finished, the routine restores the shape to its original size.
That’s all there is to animating a logo.
Function GrowShape(ByRef Shape As Shape, Step As Integer) As Boolean
' Description:Expands a shape into view
' Parameters: Shape The shape to animate ' Step Larger #s animate faster
' Example: GrowShape ActiveSheet.Shapes("Logo"), 10
' Note: For best results, shape should be hidden before calling ' this routine
' Date Init Modification ' 01/10/11 CWH Initial Programming
Dim lCenterX As Long 'Shape's center X coordinate Dim lCenterY As Long 'Shape's center Y coordiante Dim lWidth As Long 'Shape's width Dim lHeight As Long 'Shape's height Dim l As Long 'Generic Counter for the loop Application.ScreenUpdating = False With Shape 'Remember shape's original dimensions lCenterX = .Width / 2 + .Left lCenterY = .Height / 2 + .Top lWidth = .Width lHeight = .Height 'Animation Loop For l = 0 To lWidth Step Step .Width = l .Height = l * lHeight / lWidth .Left = lCenterX - .Width / 2 .Top = lCenterY - .Height / 2 Shape.Visible = True Application.ScreenUpdating = True Application.ScreenUpdating = False Next l 'Restore shape's original dimensions .Width = lWidth .Height = lHeight .Left = lCenterX - .Width / 2 .Top = lCenterY - .Height / 2 End With Application.ScreenUpdating = True
Function SpinShape(ByRef Shape As Shape, Step As Integer) As Boolean
' Description:Expands a shape into view
' Parameters: Shape The shape to animate ' Step Larger #s animate faster ' Steps should divide 90 evenly
' Example: SpinShape ActiveSheet.Shapes("Logo"), 10
' Date Init Modification ' 01/10/11 CWH Initial Programming Const Pi = 3.14159265358979 Dim sng01 As Single '1 Degree in Radians sng01 = Pi / 180 Dim lCenterX As Long 'Shape's center X coordinate Dim lCenterY As Long 'Shape's center Y coordiante Dim lWidth As Long 'Shape's width Dim lHeight As Long 'Shape's height Dim l As Long 'Generic Counter for the loop Application.ScreenUpdating = False With Shape .LockAspectRatio = False 'Remember shape's original dimensions lCenterX = .Width / 2 + .Left lCenterY = .Height / 2 + .Top lWidth = .Width lHeight = .Height 'Animation Loop For l = 0 To 360 Step Step .Width = lWidth * Abs(Cos(l * sng01)) .Left = lCenterX - .Width / 2 If l = 90 Or l = 270 Then .Flip msoFlipHorizontal Shape.Visible = True Application.ScreenUpdating = True Application.ScreenUpdating = False Next l 'Restore shape's original dimensions .Width = lWidth .Height = lHeight .Left = lCenterX - .Width / 2 .Top = lCenterY - .Height / 2 End With Application.ScreenUpdating = True
(By Guest blogger Yoav Ezer)
Many times when a workbook is crammed full of numbers, your data can be difficult to read. This is bad enough when you are sure the data is correct, but If you are supplied a spreadsheet that contains potential errors you really want to be able to detect them so they can be fixed.
A common error in inputting is where your work contains duplicate records. There are a couple of ways we can delete duplicates, but what if you only want to see them rather than delete them? Here is when conditional formatting can help. With this little technique you can make the duplicates jump out at you!
Check out this screen grab below.
See how the duplicate rows are highlighted? In this sheet, the highlighting helps us indentify duplicate invoices.
First you need to go to the conditional formatting dialog as you normally would.
Then in the “Edit the Rule Description” box:
Enter this formula:
The formula might look complicated, and it kind of is. It relies on a function you might not have seen much called SUMPRODUCT. If you are curious about the function, this article is a great introduction to the topic.
In this formula, SUMPRODUCT will return the sum of rows from rows 2 through 16 where columns A,B and C are equal to the current row. If the result is greater than 1 then the format is implemented on that row.
As you can see, when you are given a spreadsheet containing problems, you don’t always want to nuke error rows, some times you need to know about them so you can deal with the issues at source. Conditional formatting can raise your awareness without changing the content of your spreadsheet. Give it a try!
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.