Saturday, April 12th, 2014 by admin admin

I had an opportunity to test out one of the participant’s data comprising 500,000 (half a million) rows using PowerPivot with my computer which is running a 2 year old i5 chip with 6GB ram. The data failed to load into PowerPivot because of insufficient memory. The data source is an Excel file.

Excel 2003 vs 2007 file format

Wednesday, April 2nd, 2014 by admin admin

I had extracted data for a client from their Great Plains financial system quite a few years back and the report was saved in the older excel format that uses the. .xls extension. The file size was 300 MB because I used Macro/VBA to pull more than 3million rows from the system. Recently, my client called me to upgrade the report. I save the file in the newer .xlsm format. I know the file size is likely to drop with the new format but was surprised to see that it dropped from 300MB to almost 30MB. That’s a 90% savings in space. Well done, Microsoft for developing the new format.


Wednesday, April 2nd, 2014 by admin admin

I am currently trying to install OneDrive so that I could use it for Office for iPad. At the same time, I am also trying to compare the features with Dropbox, the current app that I am using to collaborate with my colleagues. For dropbox, there may be a sync problem because one of my colleagues claimed that he did not see the updated file I saved into the folder. I hope OneDrive will not give me this problem.

For OneDrive, there are 2 versions. One is for LiveAccount and another is for Business (formerly known as SkyDrive Pro). I am installing the OneDrive for Business because I have an Office365 account and would like to use it for collaboration with my colleagues. And OneDrive for Business may be the solution for corporations who have a much more stringent requirement on app that their staff can use. It is from Microsoft so security should be less of an issue as compared to Dropbox.

For OneDrive for Business, there are 2 versions. One is for 32bit and the other 64 bits. You need to find out which version you should be using. I thought it was tied to Windows and downloaded the 64 bit version. It turned out that I should be using the 32 bit version because my MS Office installed is 32 bit version. You can find the instruction to install OneDrive for Business in this link http://support.microsoft.com/kb/2903984.

Once downloaded, you can install it and follow the instruction given in the wizard. So far so good.

After the first installation, nothing happened. I thought I installed wrongly and installed a second time. Nothing happen again. Out of curiosity, I went to All Programs to search for it and found it in the Office 2013 folder (I don’t have Office 2013). The program just created a folder and don’t tell you anything. Once you click on it, the setup page is activated and all you have to do is to identify the folder you wish to sync and paste the OneDrive link (from your online OneDrive) into the form and the files are synced immediately.

MS Excel ranks Top 20 in App Store download list

Tuesday, April 1st, 2014 by admin admin

Excel for iPad is one of the most popular downloads since it’s launch on 27 Mar 2014. http://www.computerworld.com/s/article/9247317/Microsoft_s_Word_for_iPad_cracks_top_10_in_gross_revenue


Excel for iPad

Monday, March 31st, 2014 by admin admin

I have just installed MS Excel over the weekend on my iPad and have a quick look at it. The interface looks very much like Office 2013. The basic functions are there. The next thing I look for is Pivot Table which is missing from the App. Table is available although I have not tried it yet and not sure if all the functions for Table are made available in the app. To save or/and work on any document, you will need to have a one drive account. I read that it requires Office365 subscription to use Excel. Come back for more updates as I continue to explore Excel for iPad.

Substitute for VLOOKUP

Wednesday, March 19th, 2014 by admin admin

In a recent project, I have to work with a data file that has only 1,000 rows but 1,500 columns. When I need to compare the data between 2 similar documents, the first instinct is to use VLOOKUP. But I stopped immediately because I cannot imagine doing VLOOKUP for a range that is more than 1,500 columns wide and having to find out which column I am supposed to reference to. So for this case, I have to use the substitute for VLOOKUP, that is a combination of INDEX and MATCH formula. The results returned is the same as VLOOKUP but it is much faster in this case because I don’t have to count the columns and take up my computer resources to reference a range that is 1,500 columns wide.


Monday, March 17th, 2014 by admin admin

I was extracting data from the a SQL server to prepare a Pivot Table based P&L report for my client. During the extraction using VBA ADO, we found that the data was not presented correctly. Initially, I was led in the wrong direction by the vendor. Thanks to MSQuery which can present the records instantly, I was able to identify the cause of the error.

Pivot Table Training

Wednesday, February 26th, 2014 by admin admin

When it comes to Pivot Table training , we will divide the Excel participants into the following groups:

  • Never heard of it before
  • Heard before
  • Seen it before
  • Use it before
  • Know how to create one

Whichever group they are in, they will know how to create a Pivot Table by the end of the course. For those who already know, they will learn how to re-use the existing Pivot Table over and over again instead of re-creating them again.

Powerpivot vs Pivot Table

Tuesday, February 25th, 2014 by admin admin

PowerPivot and Pivot Table are the same and also different. The way they are managed is the same but the data sources are different. PowerPivot has it’s own working area to manage the data source while Pivot Table usually use data stored in an Excel worksheet.

To create a “Pivot Table” using the PowerPivot function, you have to use the Pivot Table icon in the POWERPIVOT ribbon.

To create a normal Pivot Table in Excel, you have to use the Pivot Table icon in the INSERT ribbon.