Microsoft Excel

The Inherent Bias in LinkedIn Featured Skills & Endorsements

A network graph of LinkedIn logosFor anyone who seriously uses LinkedIn, you know that your profile is really important.  Our profile is our modern day resume, and it is how we will be judged by potential clients and employers.  One part that is especially important are our listed skills.   I have been fortunate that many people have endorsed me for many skills without me ever needing to resort to any form of “hacking“.

But I have come to realize that there is an inherent bias in the LinkedIn Featured Skills & Endorsements, and it is one that cannot be easily fixed, because the bias has to do with who knows what you do, and feels competent enough to make a judgement about it.

Read More →

Big Excel Spreadsheet Tip: Don’t Use Smart Tags to Convert Text to Numbers

Don't do this for big spreadsheets!
Don’t do this!

I often work with really big Excel spreadsheets (>500,000 rows) to do various types of data analysis.  And often I’m grabbing data from databases (how else are you going to get such big spreadsheets???)  And a common thing that can happen, is that numbers will be copied in as being in a text format, and many math and lookup formulas won’t work on them.  One tempting way to convert these is to use a “Smart Tag” where, when you select all the numbers that are being treated as text, you get an ! in a diamond, that you can click on, and it gives you the “nice” option to just “Convert to Number”.   If your spreadsheet is over 10,000 rows, DON’T DO THIS.  It will take forever to complete…  (ok, not really forever, but possibly several days to complete.) Instead you should do the following:

Read More →

Fixing Formula Auto-Fill / Replication / Calculated Column Problem in an Excel 2016 Query Table

One of the great new features of Excel 2016, is that it has Power Query and Power Pivot built-in.  This makes connecting to a database a LOT easier, where a database table, view, or query will get imported into an Excel table, and then new columns can be added to that table with formulas.  For folks like myself who are good with Excel formulas, this is often easier (and sometimes more powerful) than trying to have the SQL query itself do calculations.

But, I had run into a problem, which was that on one of my spreadsheets, every time I would refresh a query, all the new data from the query would not have the formula column automatically fill in like I was expecting.   And since I had a fairly complex spreadsheet, if I forgot about this problem, I would get bad data and reports….   So I needed to fix it.

Read More →

Using Match and Index on a Pivot Table: Sometimes Excel has better Performance with “Old School” Formulas

I am a huge fan of pivot tables, and often use them to help transform raw data into a different table, by “mirroring” data from a pivot table, where I have a table to the left of the pivot table, and I want to have the table show the same value the pivot table has, or possibly transform it with a formula.  Often you can just choose the cell reference that the pivot table has and you are done…  But sometimes the pivot table may have multiple columns, that vary, and you wish to do some sort of lookup on the pivot table…  Traditionally, to do this, it would require creating a GETPIVOTDATA formula.  These have always been a bit complex, and now with PowerPivot being built-in to Excel 2016, they are even MORE complex.  This is why some people choose to use cube functions instead, which can now be done on PowerPivot tables….  But I have tested both of these, and they are very slow when you have a lot of rows.   So, I have gone back to an old school method of using the MATCH function (with absolute references) to determine which column a particular heading is in, and then use the INDEX function to select the value from that heading.  This old school trick turns out to be a LOT more efficient than using GETPIVOTDATA, and thus I thought I should share this with any other Excel nerds who read my blog.

Macro to Find and Delete Blank Strings produced by Formulas

Some day I’ll write a “Top 10 list” about the problems with Excel, and workarounds, and when I do, this post will be on the list! While I know the title of this post sounds obscure, I run into a problem ALL THE TIME with Excel, that often I have a formula that if there is an error I want it to resolve to being blank, and I can kind of do this with =iferror(formula,””)  And while this looks blank on the screen, and many formulas will treat it as being blank, unfortunately both PivotTables and charts will not!  For years, I have solved this problem by manually going to each cell that has a blank, and pressing delete. (Because find and replace can’t do this!!)  But that wastes a lot of time, so today, I finally decided to take the time to create a macro to fix this, because in my searching on the Internet, I never found one.

Read More →

Python Script to Automate Refreshing an Excel Spreadsheet

Excel Logo Plus Python LogoOften I run into situations where it makes sense to do analysis of a lot of database data in an Excel spreadsheet, but due to the amount of processing the spreadsheet requires when updating, it takes a long time for the spreadsheet to “Refresh All”.

One solution to this problem is to automate the spreadsheet so it refreshes every night.  The following is a small Python script that can do this using the Python for Windows Extension:

Read More →

Solution to Excel/Access/ODBC to PostgreSQL Integration Problems

In order to do more in-depth analysis of our student data, we have recently setup ODBC access to our online SIS, which is running on PostgreSQL.  My goal was to have Access connect to the PostgreSQL data source, so I could create queries (views) in access, which I could then import into Excel.  While Excel should easily connect to Access, as they are both Microsoft products, this has NOT been the case. But now that I have been able to figure out how to get it to work, I’m posting the solution for anyone else who might run there head into the same brick wall.

Read More →

Excel VBA Code to Scrape California State Job Openings

I have become interested over time in web scraping and potentially using this to do a massive job market study to determine what education should truly focus on to ensure that we are providing the right skills to our students.   While I’d ultimately like to do this with a website like Indeed, and probably would use something like Python and PostgreSQL, I did a quick VBA script in Excel this morning to scrape the California Job Openings, putting a new opening per sheet, and I thought I’d share the code.

Read More →

A use for “Data Tables” in Excel

Excel Logo and an Icon of a SpreadsheetOne of the features of Excel that I really have never liked is that of “data tables”.  First, they sound too much like “tables” which is a newer feature that really is cool (yet, surprisingly my experience has shown that many otherwise knowledgeable Excel users still don’t know about them).    The other problem with the data table, is that it was really meant to be a what-if tool, but I have not found a situation yet, where I couldn’t create the equivalent of a data table through the use of mixed references within a formula, and the method of setting up a data table has always been tricky, as they use an array function.  (Yet unfortunately, they are still commonly taught, and Microsoft includes them in their advanced certification test.)

Read More →

Five Fundamental Principles about Learning & Teaching Spreadsheets (Excel)

A 3-D Icon for ExcelAs an instructor who has been teaching Microsoft Excel to students for over a decade, I have found key problems of learning to often occur with students due to the structure of curriculum.  The following five fundamental principles are those that I have found to be true for how students can best learn spreadsheets.

Read More →