INDIRECT worksheet function

Tuesday, February 2nd, 2016

I have a formula which is used to find out if a particular description is available. It works well except that I have to keep readjusting the range in the formula because it is shortened every time I delete the rows within the range. In the end, I have to make use of the INDIRECT worksheet function to freeze it so that it is not affected by the deleting of rows.

Do not mix numbers with formulas

Wednesday, June 17th, 2015

In Excel, it is better to separate numbers and formulas into different cells. For example, if you wish to multiple by a row of numbers by a certain rate, it is better to create one row of numbers and another row for the rate. Then add in a 3rd row to multiple the first 2 rows together. In this way, you can always see your numbers and rate you entered and you can also change the rates easily. Do not try to save rows by putting the number and rate into one cell or row.

Excel 2003 vs 2007 file format

Wednesday, April 2nd, 2014

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.

Major uses of Excel

Friday, February 7th, 2014

Excel is usually used for one of the following five purposes:

  1. Collection of Data
  2. Data Processing
  3. Tracking and Monitoring
  4. Analysis
  5. Charting and Reporting