Andrew's Excel Tips

Excel 2010

andrewe — 2009-12-06T09:19:11+09:00

Got Excel 2010? So do I. Looks like some good stuff has been added, right? In case you are wondering, I have updated JMT Excel Utilities to work with Excel 2010 but it's still in "beta" at the momment. I will do some more testing over the next week or...

Auto Cell Edit

andrewe — 2009-12-06T09:03:51+09:00

Auto Cell Edit is an addin that edits cell entries as you make them. Toggle the addin on and also what other options you want to be edited - Column Width (Autofit) Row Height (Autofit) Proper Case First Letter Upper Case Spell Check Whate...

DIY Slicers

andrewe — 2009-11-03T06:44:33+09:00

Better late than never... After seeing Microsoft’s post about Slicers on their Excel blog, I thought it might be fun to make my own version. Maybe I can’t wait until the official release of 2010? Anyway, I put together something that would show wh...

Latest Version Of JMT Excel Utilities

andrewe — 2009-09-23T20:10:46+09:00

Recently I have made a number of improvements to my utilities. Sorry for not posting sooner! CHOOSEFROMRANGE and CHOOSEFROMLIST are 2 useful new functions. You can use them to return a random value from a range (for the former) or a custom list (for...

Days Per Week Between 2 Dates

andrewe — 2009-09-03T19:18:05+09:00

Out of all the formulas (formulae to the more educated than me) that I work with, I would say that Date formulas are the hardest to get right. Maybe not so surprising considering how weeks don't divide nicely into months. It really is very naughty of...

More Chart Tools

andrewe — 2009-08-30T14:34:22+09:00

I recently added 2 new chart tools to JMT Excel Utilities - Reposition Data Labels Resize Chart They are rather simple tools compared to what you might pay for. Then again, they are free :-) Here is the download link.

Excel 2007 Colors - Color Tools

andrewe — 2009-08-05T19:24:15+09:00

Better late than never, I have made some improvements to my main utilities again. The color picker and the GETTINTANDSHADE function are more acccurate. I also added a new tool caled "Show Excel 2007 Color Details" which shows the Theme, Tint And Shade,...

Formula Tools

andrewe — 2009-07-20T19:37:42+09:00

2 new tools have been added to JMT Excel Utilities. Find Precedents and Dependents An alternative to using Excel's built-in auditing tools. A form appears listing the addresses of all precedents or dependents of the active cell, including other worksh...

Using APIs with UserForms

andrewe — 2009-07-04T10:58:52+09:00

Here are a few things you can do with APIs to make your userforms more functional. All of the code shown will work with Excel 2000 or higher. Note that I am using modeless forms which enable you select ranges and the user interface (menus, other useforms...

Custom Lists

andrewe — 2009-06-28T10:55:55+09:00

Custom Lists Custom Lists are great but I've always felt they could be used more easily. I hate dragging down with the plus cursor if I can help it, so I made a new toy to make life more bearable. Here is what I wrote in the read me file. Custo...

JMT Excel Utilities Download

andrewe — 2009-06-26T19:30:42+09:00

Some people might have be having problems accessing the JMT Excel Utilities download site. So I have made a temporary download site where you can download them here. Do you the most recent version? I’m always adding new stuff! While I am at it,...

Cell Watch Form 2

andrewe — 2009-06-06T08:25:25+09:00

No pictures this time but here is what I have changed with the new version of the Cell Watch Form. If the workbook of the cell you want to watch is not open or has been deleted or renamed etc, the watchbook referred to will be the active workbook. The...

Cell Watch Form

andrewe — 2009-05-16T11:22:18+09:00

At one of my reader's request, I have improved my Cell Watch Toolbar Addin. This time it's not a toolbar though, it's a userform. As you can see, there have been some changes. Add Cell Watch allows you to watch a cell in any worksheet, in any workb...

Drawing Tools

andrewe — 2009-04-18T09:15:07+09:00

Recent changes to JMT Excel Utilities - Make Transparent Shape This is something I did long ago and thought it might be a handy tool to use. Don't forget that in addition to being a great spreadsheet prgram, Excel is also very convenient to make simp...

Chart Tools

andrewe — 2009-04-10T07:23:02+09:00

I am a chart newbie. At least, compared to some of the chart gurus I see, I am still taking my first steps. That has not stopped me from making some simple chart tools - I figure it's a good way to learn and we all have to start somewhere,...