2 “bad” habits to avoid in Excel

Wednesday, July 23rd, 2014 by admin admin

During our Excel courses, we notice some participants have these 2 habits which cost them dearly (time savings) when they worked with Excel.

One of them is double clicking on the cell before they make entries. Because of this habit, they are not able to make full use of Goto Special, a time saving function.

The other habit is confirming cell entries or changes by clicking another cell instead of using the ENTER key. Because of that, they always find themselves getting an answer that they do not expect.

 

 

Calculate YTD Gain

Tuesday, July 22nd, 2014 by admin admin

Michelle has a template that lists the investments made by her clients. On a monthly basis, she has to check the prices of these investment and calculate the paper gain for up to 3 years. Currently, the gains are calculated manually and she would like to find out if the calculation can be  done automatically by Excel. Watch this video and find out how to arrange the data and apply the appropriate formulas to get the answers Michelle wanted.

excel files now open in Kingsoft

Tuesday, July 8th, 2014 by admin admin

After installing Kingsoft, I found that my Excel files now open in Kingsoft. If that happens to you too, you can follow the instruction on this page to restore back the setting. http://www.ksosoft.com/office/217-how-to-set-kingsoft-office-as-default-office-program.html#.U7tKXGCwrIU

 

Short cut key for paste value

Friday, July 4th, 2014 by admin admin

In today’s excel course Hidden Secrets In Data Analysis with Excel, one of the participants asked if there is a shortcut key for Paste Special specifically Paste Value. Unfortunately, there isn’t. Since she has to use Paste Value frequently and there is no shortcut keys, I recommended adding an icon into the Quick Access Toolbar so that she can do Paste Value with one click without having to go to the HOME tab. Here is the recording I have done when I was sharing with the group how to do.

Checking for the existence of Autofilter using VBA

Tuesday, July 1st, 2014 by admin admin

I was goggling for the code to find Autofilter and many websites (including reputable ones) wrote that I can use AutoFilterMode to check for the existence of AutoFilter in a worksheet. But when I tried to run the code, it fails to detect the Autofilter function. The code is

With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox “They are visible and in use”
ElseIf .AutoFilterMode = True Then
MsgBox “They are visible but not in use”
Else
MsgBox “They are not visible or in use”
End If
End With

I was working on Excel 2010. After searching further, I finally found the code in the blog listed below that works, at least for Excel 2010.

http://vbadud.blogspot.sg/2008/07/check-for-existence-of-filter-using.html

The code that he published is

If Not ActiveSheet.AutoFilter Is Nothing Then
‘Autofilter mode is on
End If

 

How to convert a pdf document to Excel

Friday, June 13th, 2014 by admin admin

One of the common challenges faced by Excel users is the receipt of pdf (Protable document format) documents containing tables of data that cannot be transferred to Excel easily. When you try to highlight and copy the data from a pdf document to Excel, the data is re-arranged from multiple columns into a single column, different from what was presented in the document. Purchasing an addin to convert PDF to Excel does not guarantee that the conversion is perfect. I encountered the same problem recently with an order list and made another attempt to convert the order list in pdf format to Excel and to my surprise it works! It has partly got to do with the PDF reader you are using. When I use Adobe PDF reader, it cannot be done. But when I use this free pdf reader called Foxit to save the pdf file as a text file, the layout is retained and allowed me extract the data using Text-To-Column without having to spend a single cent on a pdf converter. If you like to know how it is done, watch the video below.

 

WPS Office or Kingsoft – an alternative to MS Office

Wednesday, June 11th, 2014 by admin admin

Over the years, I have seen the development of many spreadsheets programs, from symphony to Lotus 1-2-3 before Microsoft dominated the market with the widely known Microsoft Office. We have seen lots of competitors along the way as Microsoft progresses. Today, I just found another called Kingsoft Office which I have just downloaded and installed, for personal use only. They do not allow companies or any profit or no-profit organizations to install on their workstation for free.

The need to pay for the software licenses by corporations would delay adoption of this new application. This is because Office users are the initiators and created the need to use MS Office at home. With corporations and non-profit organisations needing to pay for its use, the chance of it being adopted is reduced. There is still a chance for KingOffice to succeed, if the software proved to be as good as MS Office and is recommended by Excel experts who are key influencers of Excel users around the world.

The installation was real quick and I am done within minutes. It really have a look alike feel of MS Office. The very first function I tried is of course Pivot Table, using the data I used for our foundation course “Hidden Secrets in Data Analysis with Excel”. I am quite surprised that the Pivot Table interface looks like Office 2000 version and MS Office did not sue them for the use of the trade mark name “Pivot Table”. That is my first test and I should say that the functionalities offered by MS Pivot Table (Sort, Filter, Number format) are not available in Kingsoft’s Spreadsheet program. In the coming weeks, I will be doing more tests on Kingsoft’s Office. I will post my test results as I go along, so remember to come to check out the updates. Let me close with a picture of how Kingsoft Office looks like in my computer.

The look and feel of Kingsoft spreadsheet.

The look and feel of Kingsoft spreadsheet.

Pivot Chart

Thursday, May 29th, 2014 by admin admin

This is the Pivot Chart our participants for the Hidden Secrets in Data Analysis with Excel course learned to create today.

Stats on Gender

Pivot chart that makes a difference to your presentation

Adding Tabs into Excel 2010 on commonly used functions

Monday, April 28th, 2014 by admin admin

During the training course “Breakthrough Performance for Human Resources for Excel” today, participants asked how come they do not have a tab called “Applied Excel” which is showing up on my Excel 2010 program. So I showed them how I created it by customizing the Ribbon. The customized ribbon helps to put together your commonly used functions in one place so that you do not have to scroll through tabs to find them. In the video, I showed them how to create and name a new tab and add a new group called File in the new tab. I added in some commonly used functions such as open file command, new workbook, open recent files, print preview common into the group.

In the demo, I have also shared with them how to find and quickly added groups into the new tab instead of doing it one by one. In the last part of the video, I showed them that they could export the customized tab and import it into their computer once they are back in the office. Watch this video to get the step by step guide.

s2Member®