Office

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Manipulating and Massaging Data in Excel and VBA

Chad Rothschiller, a program manager on the Excel team has written a lengthy article discussing using formulas to 'clean up' data in Excel. It is very indepth and covers these topics

jethro's picture

Excel 2007 Tips

Jean Philippe Bagel works for Microsoft. He has written some great tips for Excel 2007.

Of course the right click on the sheet tab navigation buttons will work in older versions of Excel and is a great way of navigating rapidly through large workbooks. Navipane provides a great alternative that is not as limited as Excels built in display of available sheet tabs.

Excel 2007 has some very advanced and easy to use conditional formatting and Jean explains one very well. However please be very careful with using this feature in any Excel 2007 workbooks that need to be viewed or edited on a Excel 2003 or lower machine. The conditional formatting in 2007 inst backward compatible. In fact it is quite different in both how it works and how it displays to previous versions of Excel.

jethro's picture

Bill Jelen Is Giving Away His Book For Free

You can get a low-resolution e-book version of his new book, Learn Excel
97-2007 from MrExcel
.
This is the complete book. It is in low resolution, so it will appear
fine on your computer monitor. It is not suitable for printing.

jethro's picture

Dynamic Ranges - and using VBA to create them

Andrew recently posted a piece of VBA code used to create dynamic ranges. Very useful.
Here is my original post on dynamic ranges posted back in 2004 repeated.
If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.
Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.
Assume for all these examples that column A has a mixture of text and numbers for several cells.
Click Insert - Name - Define on the menu.
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.
1: Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)
2: Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)