Category: Excel
-
New features in Excel 2016
Excel 2016 has a lot of new features to wow the power user including support for cascading IF statements and a better CONCAT function. There are also a huge number of improvements to the Pivot Table function as it integrates even more closely with the Power BI tool. https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73?ui=en-US&rs=en-001&ad=US Anyone who works with Sales will…
-
Microsoft Office 365 Excel is a mobile app platform
I help run an underwater hockey club. To smooth administration I wanted to create an mobile application to record attendance and the payment of weekly subs. My usual approach would be to create a WordPress application or perhaps a mobile app using Cordova to package an HTML and Javascript application. However I am trialling Microsoft…
-
Excel CSV is not UTF-8, fix it with Notepad++
Excel CSV is ANSI encoded (the CSV specification does not define an encoding). Many web applications are UTF-8 encoded. If necessary you may open the file in Notepad++ and change the encoding. I had to do this to successfully import to Formidable forms on WordPress.
-
Excel coding style guide
I am introducing some students to Excel and have produced this brief style guide to producing more readable code which I think gives you 80% of everything you need to know. The strategy is to use Excel’s built-in ability to name sets of cells. Please add your comments. All tabular data must be in a…
-
How to create a list from an crosstab table in Excel
If you analyse data in Excel you know that Pivot Tables are magic and that they are generated from simple lists of records (“facts”). Unfortunately very often you data will be given to you by a user who has put it into Crosstab format and to get this back into a list you have to…
-
Humour: Excel and big data
As consultants our job is to help the user and unlike the poor second line support person we usually have the time and infrastructure to do it. Here is an amusing rant from one such Sys Admin in The Register. The scenario will be familar to anyone who has ever done a data warehouse project…
-
How to create a union of n tables in Excel
In response to my post showing how to union two tables in Excel a reader writes, I have seen your blog post about combining two tables, which I have working for me, however I would like to combine an ever increasing number of tables …, into a common “list”. How to do it without ending…
-
Create the Union of two tables in Excel
— 2017-04-03 I have new post for Excel 2016 (2013) — It is very common in Excel to need to create a single table from the two or more other tables. For example, different users may edit different worksheets but you need to aggregate all the data for processing. This can be done relatively simply…
Dr James Bayley 