I spent yesterday afternoon taking the site back from spammers. Over the past two years they added over 32,000 fake groups, which got my site highly ranked on Google for for Spanish language tips on exterminating bed bugs. There’s something to brag about in the Christmas newsletter!
As of today Rate My Excel is officially back to being focused on the best free Excel skills assessments and spreadsheet tips and tricks. In the next few weeks you’ll see new quizzes on formulas, Excel 2016 and possibly VBA. And also new articles on best practices with VBA and the new Excel functions like IFS(), TEXTJOIN() and MAXIFS().
It’s going to be an exciting time. Unless you’re here because of a bed bug infestation, in which case lo siento para su mala suerte.
How To Bulk Delete Groups From WordPress
There’s no way to mass delete Groups from your WordPress page. The workaround is to install the Bulk Delete plug-in and mass delete the users that created the groups. Deleting users deletes all content they have created. If you don’t have that many rogue groups you can click on Screen Options in the upper right of your page and change the setting to show (and delete) 250 groups at a time.
PowerPivot adds a lot of functionality to your spreadsheets (via an add-in in Excel 2010 and enabling it in Excel 2013). PowerPivot (and friends PowerQuery, PowerMap and PowerView) allow you to automate repetitive data clean-ups, crunch hundreds of millions of rows of data and build stunning visualizations.
In 2012 I had heard of a new Excel add-in that promised “never write a VLOOKUP ever again.” And at first using PowerPivot was like driving a Ferrari straight into a brick wall – corrupt files, non-stop Excel crashes, glacially slow calculation speeds and 80mb file sizes. Now three years later I look back on those early days and shake my head. But as a wise dog says, “Sucking at something is the first step to becoming sort of good at something.” Hopefully you can learn from my many mistakes, the top six are outlined below.
Import Large Excel Tables
In PowerPivot you can pull data in from places like the internet, a database, another excel file, a CSV file, Facebook or a table right in your spreadsheet. Excel table imports appear to be the most convenient – what better way to keep all your information in one place and make fast edits? However several tables of 300,000+ rows sitting in duplicate in your Excel file and PowePivot will bog down your file. For larger data sets using any other import method is the way to go.
PowerQuery is a bit like Excel’s Macro Recorder for importing data. Trim and clean your data the first time, and the next time you hit Refresh everything is performed in the background. Neat! But why not just import everything? You might need that Esparanto_Product_Name column later and you can always clean your data with by tons of new calculated columns. You guessed it – larger file size and poor performance. So use PowerQuery to remove unnecessary columns, handle errors, change data types and sort. Sort is a funny one – for some reason sorting columns directly in PowerPivot can bloat your files by up to 25%.
Use RELATED and Calculated Columns everywhere
Excel power users constantly join two+ tables with VLOOKUPs. Or they add helper columns to perform common calculations like unit price, profit or net days. And then they create a pivot table off of the resulting mega table. Raw data, VLOOKUP, helper columns, pivot. Rinse and repeat multiple times per day.
So of course a new PowerPivot user’s first instinct will be to construct a mega table. The RELATED DAX function is equivalent to VLOOKUP and pulls in matching data from other tables. However the beauty of PowerPivot is that pivot tables can be built from multiple data tables. As long as a relationships exists between two data tables you can use the Product Details table items as row fields and Sales Transaction table items as value fields.
Besides RELATED other DAX functions can create those helper columns and widen your data table. Because of the way PowerPivot’s calculation engine works wide and fat tables quickly lead to performance issues. A better option is to create DAX measures or calculated fields to do your calculations. As long as you don’t need to put your DAX measure in the row or column of a pivot table it’s always better to use measures instead of calculated columns.
Use 32bit Office
There are two versions of Microsoft Office – 32bit and 64bit. Using 32bit Excel with PowerPivot is not fun – suddenly Excel crashes every ten minutes and refreshing workbooks takes a long time. This happens because 32bit Office is limited to 2GB of memory. To get around this switch to 64bit Office or trick your computer into using 4GB with this patch.
To see which version of Office you have in Excel 2013 click File > Account > About Excel in the lower right.
Rely On Implicit DAX Measures
In a normal pivot table it’s easy to see the Sum of Grizzly Bear Attacks – you pull in the Grizzly Bear Attacks column to the pivot table’s value field. Doing the same thing in PowerPivot creates an Implicit Measure. The problem with implicit measures is sometimes they start to throw out incorrect values when you filter your pivot table or add information from other tables.
The better approach is to create a DAX Measure called Grizzly Bear Attack Total:=SUM(BearFactsTable[GrizzlyBearAttacks])
Check for Implicit Measures by clicking the Implicit Measure button in the Advanced ribbon of your data model.
Go It Alone
PowerPivot is not intuitive for people with years of experience in excel or databases. It’s easy to get overwhelmed by the new DAX functions, PowerQuery’s M language or those mysterious error messages. Luckily there are now a ton of resources online to help.
Adventures in Competitive Excel: Recap of the 2014 INFORMS Spreadsheet Guru Contest
A few weeks ago I stumbled across a contest page on the INFORMS association website promising “a test of head to head individual spreadsheet skills.” Intrigued, I registered and looked forward to spending some quality time in Excel at the end of March.
The preliminary Round One came with a spreadsheet and short instructions. The goal of the contest was to advise a fictitious cardiac charity on the country most deserving of its attention. Your starting point was tables of raw data and a half-completed picture of the end result. Rounds Two and Three were very similar. The average time for completing the report was given as 1-3 hours.
Here are a few tips I picked up after making it through all three rounds —
How to Survive Any Excel Competition
Know what you’re getting into. There wasn’t much information out there on past years’ contests so I read up on INFORMS itself. The association website frequently mentions big data and analytics. I guessed the contest would be wrangling large data sets and then drawing conclusions from them.
Work from somewhere quiet. Round One I had to take frequent breaks to setup an office prank and my time suffered from the multi-tasking. Round Two I retreated to a conference room only to be chased away by industrial glue fumes from our long overdue office carpet replacement.
Use keyboard shortcuts. Text to Columns (ALT + AEF), Auto Filter (ALT + AFF), Change Tabs (CTRL + PgUp or CTRL + PgDn) and more were all huge time savers.
Set up as many formulas as possible. I lost time here because it had been so long since I used RATE() that I didn’t even realize it had been deprecated in Excel 2007 in favor of RATE.AVG() and RATE.EQ(). Ouch. Knowing my basic functions would have shaved at least 5 minutes from my Round One time.
Read the instructions three times before starting. That sounds a little excessive but trust me it’s not. Very similar to MBA case studies – there will be small footnotes that seem unimportant and skim-worthy. In this case the minor note was that the charity prefers working in countries with republic government types. Anyone who missed this note would have mistakenly recommended Tuvalu.
Check Your Work. I almost submitted the wrong answer in Round One because of a simple typo. Not good.
All in all it was a great time. The organizers did a stellar job with creating the contest and coordinating everything. I plan to use the prize money to become a card carrying member of the INFORMS association and maybe one day sponsor cool prizes for a similar contest on this site.
I’ve been kicking the idea for fun Excel assessments around for a while as a competitive person who lives and breathes spreadsheets. The article The Degree is Doomed from January’s Harvard Business Review was the spark I needed to start executing on the idea.
In the article, the author asserts that degrees will be replaced by other, more practical signals to prospective employers of an applicant’s competence. Coders, designers and artists all have online communities to showcase their work and build a reputation – why not finance and analytics professionals? LinkedIn is a good tool but having 15 endorsements for Microsoft Excel won’t convince recruiters that you’re a spreadsheet ace.
From the article —
The value of paper degrees will inevitably decline when employers or other evaluators avail themselves of more efficient and holistic ways for applicants to demonstrate aptitude and skill. Evaluative information like work samples, personal representations, peer and manager reviews, shared content, and scores and badges are creating new signals of aptitude and different types of credentials.
While I agree that degrees can be overrated – we’ve all had that coworker who brags about their alma mater while confusing SUM() with COUNT() – I would not call degrees doomed. The fact is that a lot of hiring managers got their MBA’s from the local college’s evening program back in the day and expect the candidates they hire to hold similar credentials.
I do think we’ll slowly see a demand for more pragmatic signals – assessments, quizzes, spreadsheet samples,and more over time as a way for employees to show Excel mastery in a way that GPA cannot.