Our primary goal through these 24 days is to provide "stuff" and information that will boost your performance and/or save you time and/or increase your general productivity.
We will talk about our focus (product data) but we'll try to keep it at a high level rather than "pushing our products." For example, we're pretty sure that Product Data is becoming more important strategically - not just operationally. Google, for example, in our view, HAS to do a MUCH BETTER job with product data if it is to arrest its continued loss of share against Amazon, let alone reverse it. And Amazon itself has some horrible problems with product data that retailers competing against the 800-lb gorilla can exploit.
Some of the stuff we'll be providing will be related to product data but much will be general - applicable to anyone. An example is today's "stocking stuffer" below which can help anyone dealing with Pivot Tables in Excel.
Note that you won't have to be an Excel jockey to find something useful in the 24 days. We're a widespread team, all working remotely from differnt corners of the world, and some of the productivity measures we've adopted - either individually or collectively - will likely be of interest to people looking to maximize their effectiveness and efficiency/productivity.
Lastly, we want to have fun ourselves and be entertaining to you - even a steady daily diet of good stuff will get tiresome if we don't throw in some intriguing and/or provocative ideas/thoughts. One idea we're storyboarding is having some "time-travelers" come back from 2030/2040 and provide their perspectives on the State-of-Search today (i.e. 2020). What are the odds that we 2020ers will find that we have a pretty good idea of the future of Search and how it will develop versus finding that there are developments coming that will be just as suprising to us - and impactful on existing industry heavyweights - as the iPhone was in 2007, sending Nokia reeling. What do you think those odds are? Please let us know in the comments below.
Stocking Stuffer - Automatically Formatting Numbers in Pivot Tables
This macro will speed up the formatting of numbers in Pivot Tables. We recommend placing the code in your Personal.XLSB file so that it is available regardless of which Excel workbook you have open. And then adding it to your Quick Access Toolbar (QAT). If you're not familiar with how to add macro code to Excel, or setup your Personal.XLB, or add code to your QAT, the following resources should help:
- Adding Code to an Excel Workbook
- Create a Personal Macro Workbook - PERSONAL.XLSB
- How to Add a Macro to the Quick Access Toolbar in Microsoft Excel
When you run the code below on a worksheet containing Pivot Tables, it will display a dialog box for each column in each Pivot Table. The dialog box contains instructions but what you're going to do is type in 3 letters indicating whether you want numbers (num) or percentages (per) and the number of decimal places. For example, num0 will format the column as numbers with zero decimal places - so 32768 would be formatted as 32,768. When the macro moves to the next column, you can just click "Enter" if you want the same formatting as the previous column.
At the moment, the macro only works with numbers, percentages and currencies but other number formats could easily be added. Additionally, the macro could be made much smarter so (for example) its skips over columns that have already been formatted - unless you instruct it not to. Lastly, we could add this to one of the VBA Add-In Toolbars we've developed that we're contemplating giving away on some kind of raffle basis.
If you're interested in getting enhancements to this macro and other VBA Code that we've developed for internal purposes, please comment below. In the meantime we hope you find this helpful and please let us know if you have any problems.
Dim pt As PivotTable, df As PivotField
Dim sNumberType As String, sAbbrevType As String
For Each pt In ActiveSheet.PivotTables
For Each df In pt.DataFields
sNumberType = InputBox( _
PROMPT:="Enter 3 letters of Number Type & the number of " & _
"decimal points you want for field " & Chr(34) & df.Name & Chr(34) & _
vbCrLf & "e.g. num0, per0, num1, cur2", _
Title:="CHOOSE NUMBER TYPE & NUMBER OF DECIMAL PLACES", _
sAbbrevType = LCase(Left(sNumberType, 4))
Select Case sAbbrevType
Case "num0", "num"
df.NumberFormat = "#,##0"
df.NumberFormat = "#,##0.0"
df.NumberFormat = "#,##0.00"
df.NumberFormat = "0%"
Case "per1", "per"
df.NumberFormat = "0.0%"
df.NumberFormat = "0.00%"
df.NumberFormat = "$0"
df.NumberFormat = "$0.0"
Case "cur2", "cur", "curr"
df.NumberFormat = "$0.00"