Hi Folks,

I’ve been busy and not able to blog much lately, but I just go an email from Chandoo that I thought I’d share.

Chandoo is taking another round of students for his VBA classes. I’ve heard very good things about it. Here’s what Chandoo has to say:

So far, we have enrolled 180 students in to this batch. We are eager to enroll as many more as possible during next one day.

We will be closing enrollments for this on September 16 by 12 Midnight Pacific Time. Please tell your subscribers & readers about this so that they can join in time.

The course comes in 4 flavors:

• VBA Classes with Online Access – $97
  
• VBA Classes with Downloadable Access – $147
  
• Excel School + VBA Classes – $247
  
• Excel School + Dashboards + VBA Classes – $347
  

You can sign up for one of this classes by clicking this link.

(FYI, I’m hoping to be back blogging and posting again soon… things have been – well actually… continue to be – crazy! But I should be able to carve some time out of my schedule again shortly.)

I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here’s how:

• Copy the range of numbers
• Right click the destination and choose PasteSpecial
• Select Values and Subtract:

  

• Say OK and voila:

  

Now interestingly enough, if there are values in the cells, it overwrites them. If there are formulas in the cells, it subtracts them from the formulas. Kind of odd that it isn’t consistent.

(And of course, if you subtract a negative number, it turns into a positive too.)

Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”

Wow, cool. This is a great thought. Combined with the weather, this makes it a forward looking document that should be really useful.

It’s not like we don’t have the information, either. We have a shared Outlook calendar that we use for recording all of our events. So it’s just a matter of getting the appointments out of the Calendar and into Excel. Easy, right? Ha!

Outlook has a weird way of storing appointments… especially when you get into recurring appointments. It seems that if you run code looking for appointments it ignores the recurring ones. Run the code to get the recurring appointments and it gives them to you, but with the date of the first recurring appointment… even if you select a recurring appointment from within a date range. (I pulled all appointments from 2011-04-04 to 2011-04-11, and was getting appointments from 2010!) Apparently Outlook doesn’t actually store the dates of the recurring appointments at all, only the first and the recurrence pattern.

After a couple of days fighting with this, (on and off,) I happened to hit Bing looking for help. Lo and behold, I found an article by Jimmy Peña that does it all! Sweet! (Thanks Jimmy!)

I had to make a very simple change to the article in order to make it work on a shared calendar, but that was it. Jimmy’s code pulls down the key pieces I needed and places it all in a nice table:

Now, what we did with it…

I really wanted something to put on the bottom of the report that looked nice, and gave the users appropriate information. I could have pulled this into a PivotTable and put it on the report, but I didn’t really like the look of it. Then I got to thinking… I like the way the slicer looks, I wonder if… So on a whim I decided to try something:

• I created a column for each type of event I wanted to display

  

• I created a PivotTable that showed the list of events
• I added a slicer off the Pivot Table for each of the event categories at the bottom of the report

So now, I have the following to place on the bottom of my Dashboard to show the team what events are upcoming over the next week (You can picture the charts and tables above that.)

I don’t think that anyone at Microsoft ever intended that someone would use slicers are the report output, but for our purposes it works.

Now, some observations here…

• I can’t get rid of the blank lines. The table formulas use “” at the end of the IF statements, but they still show in the slicers. Using NA() returns #N/A in the slicers.
• One could argue that it might be better to have these grouped by date, rather than by category. We may still do that, depending on the feedback from the team.
• It’s unfortunate that I had to link these to a PivotTable… I wish I could just create a slicer and give it a range to populate, like a chart
• Slicer formatting options are severely limited. You pretty much can choose a colour, to sort ascending/descending and if you’d like to shade unavailable options. (How about hide them all together?)

Granted, this may not be perfect for everyone, but I kind of like the use for this. It adds a bit of polish to our overall report, as the slicers are a bit more glamorous than a standard Excel table (with the rounded edges and all.)

I’m curious what you think of it.

Hi everyone,

As per my last post, I recently set up a free public Excel Help Forum on my website. I’m pleased to say that over the past week and a bit we’ve attracted 50 new users, and now have over 40 threads with over 200 total posts on the site. Not bad for 10 days!

I’ve spent a lot of time configuring different options to try and make this forum as consumable as possible for people. In addition to the basic forum functionality, some of the modifications include:

• Serious anti-spam solutions to keep the board all about questions and answers, without having to wade through garbage
• Facebook integration, for those of you who like to tell your friends what you’re doing
• RSS feeds, for those who like to keep up to date on the topics, and drop in when you see one that interests you
• Tapatalk integration, for those who want to keep up with the site on a mobile device

And those are just the big ones. There’s been a lot of tweaks under the hood to make the experience as optimal as possible, and a few others are coming soon.

The latest thing I’ve worked on is adding what I hope to be THE Excel Events Calendar on the internet…

I’d like to invite everyone to use the Calendar on the forum as a public Excel Events Calendar. If you have, or know of, an Excel event in your area, please post it for the world to see. I’d like to make this the most comprehensive Excel training calendar on the internet.

There are only 2 rules I’d like to attach to this at this point:

• Put the location of the event in the title, if applicable. The goal is that people will be able to find events that they can attend in their own backyard, worldwide.
• Only the date(s) of event should be posted. (I don’t want a post each week reminding people.) If there is a registration cutoff date, please post it in the event information.

I think if everyone observes the above, the calendar should remain relevant and helpful to everyone.

Events I would expect to see include training courses (live or online), new book release dates, conferences that deal with subject matter relevant to the users on this site. (Be it BI, SQL, Sharepoint and more, so long as it has an Excel flavour, it counts.)

If you are a trainer, teacher, publisher, or whatever, I invite you to participate. Register for the site, if you’re not already, and post your event to the Excel Event Calendar. The more up-to-date and accurate we can make this, the more our community can rely on this being the source to come to for training courses. The more that happens, the more likelihood you’ll get signups. The more signups you get, the more likely you are to want to put on more training events, and the more the users win. It’s a self-fulfilling cycle that is in all of our best interests.

Oh, and one final word on this to everyone. I am not doing this to solicit commissions, affiliate links or funding in any way. There is already enough advert links on the site. This is about people helping people in our Excel world.

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time… creating my own Excel forums.

I’ve been absent from the online forums for the last few years, and I’ve greatly missed it. With the amount of teaching I’ve done over the past few years I’ve constantly been referring people to other forums and blogs, and I’ve received several comments from those people asking about a forum on my own site. So during this time I’ve been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I’ve been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I’ve put off for my job over the past few years.

To this end, I’ve begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I’ve also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it’s important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You’ll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you’re trying to do. After all, a picture saves 1000 words, right?

And if you’re an expert, or even if you’d just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won’t always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you’d prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

I was writing going to write up an article for my site tonight to show how to create a table of contents using native Excel functions, rather than resorting to VBA. Naturally, I figured that it would be great to put up an interactive example with the Excel WebApp, but I ran into an issue: I found a formula that works fine in the client, but just returns #VALUE! Errors in the Excel WebApp.

Debra did a great writeup of the =CELL function back in her 30 Excel Functions in 30 Days series. The examples work great in the client, but not the webapp. Too bad, really, as it’s a great function that can be used for a lot of things.

I don’t know how much people have played with this, but if you encounter a function that doesn’t work, post it in the comments. It would be nice to get a full list.

I’m a huge consumer of Data Validation in Excel. Techniques I use include:

Pro-active Data Validation:

• Excel’s Data Validation toolset
• Form controls
• ActiveX controls
• PivotTable filters
• Slicers

Re-active Data Validation:

• Conditional formats
• Contextual formulas (IF)

So naturally, as I was trying to convert one of my web pages to use Microsoft WebApp off SkyDrive, I ran into issues. Unfortunately at this point in time, the majority of the techniques that we use for data validation in the client are not yet supported in WebApp. And yet, if we’re trying to build a web application, sanitizing the data is really important to make sure it works correctly.

I decided to convert my “Automation Evaluation” worksheet, which was intended to display how much it cost someone to do repetitive tasks over time. The overall goal of this file was to convince someone that they should pay me to automate their work, back when I was still doing consulting projects. I don’t do them any more, but I think the exercise is still worth having on the site.

In this file, it was really important to me to control the options people can select to work with for how many minutes/hours/days they spend on a task. I want the right data in there to drive my chart, yet my options are VERY limited in WebApp.

I tried the slicers, but they aren’t really built for this scenario. Set horizontally or vertically they take up way too much space. You can set them to a single column then make your user scroll down, but if you only have one option on the screen (like a combo box) then it gets awkward to figure out which option(s) are selected, and involves extra clicks. And that doesn’t even touch the “how do I return the clicked value from a slicer” issue. (I haven’t figured out how to get a value from a non-PowerPivot slicer yet.)

So this time I reached to PivotTables. I ended up making some very small tables with the options I needed and created a PivotTable that uses only the Page Field. This give me the ability to get a drop-down with pre-defined options. It works, but it does have a couple of issues:

• In the client it defaults to a “Select one item” approach and you have to check a box to allow you to select multiple items. In WebApp, it allows you to select multiple items. So I had to add a contextual formula to feed back info to the users if this happens.
• There are actually hidden rows in the worksheet to allow the Page Field to be changed. Even though the options selected always end up in the page field line, it seems to need extra space to refresh the table.

At any rate, it’s not perfect, but it seems to work. Here’s a look at the file:

This isn’t done yet, and had some extraneous information in it as I was converting a file with much more in it to an interactive web version. This particular file (when complete) will show an interactive breakeven analysis for an event entailing food and beverage.

The link will update as I rebuild the file, but for right now, it’s working, and shows that you can interact with the file and have the chart redraw. Probably the best route is to adjust by very large amounts to see the effects. Once I’ve got it all up and running properly, I’m going to build this into a full page in my site, but I’m uploading for a demo for a client that I need for tomorrow morning.

Here you go, have a play!

The following article is courtesy of Yoav Ezer, who volunteered to do a guest post for me while I am at the summit. Thanks Yoav!

User Defined Functions versus Formulas in Excel

Which are better, Excel functions or UDFs? Use Microsoft Excel for long enough you are inevitably going to get to a situation where you are going to have to choose. In many cases they might seem equivalent. What are the pros and cons of each approach?

The main difference of course is that the developer of the spreadsheet designs the User Defined Function, it is user-defined after all! This means the function is exactly what you need right now, whereas formulas depend on using the (albeit powerful) built-in features of Excel.

In terms of readability, you will usually find well-written functions have the slight edge. Rather than a complex formula with potentially many levels of nested commands, you can simply use MyFormulaName() and so on.

What other comparisons can be made?

Testing Speed

To test to see if there was a significant speed difference we set up a spreadsheet as below. On the left we have some columns where we test to see if the number in column C is between the number in A and B. We use a function to check. Over on the right we do the same thing, but this time using formulas.

To fill out the rows, we increment the values in both sets of columns just to give us some values to work with.

In D our cell contains a very easy to read =GetBetween(C2,A2,B2)

In column I we have our formula =IF(H2=MEDIAN(F2:H2),”Yes”,”No”) which you may or may not find easy to read!

Obviously the function is hiding a bit more complexity…

The UDF

Our UDF is called, as you would expect, GetBetween and takes a value and two cell references. We check to see if the value is between the two cell values. It’s a simple IF statement to return our answers.

Which is Faster?

I copied these cells around 50,000 times and changed the values.

Disappointingly, at first I found no discernable difference. It seems in this case the formula and the UDF are pretty equivalent. Until, that is, you start to tax your computer. When I doubled that number of rows I started to see that UDFs can be almost twenty times slower than formulas.

Conclusion

It seems in normal day-to-day usage there is no clear winner, so use whichever approach works for you. In terms of developer flexibility the UDF will ultimately win but until you get to that threshold work with whichever approach suits your objectives and skill levels.

Once you start to build huge spreadsheets though pure performance is definitely going to become an issue. That is where you need to start considering where you can make easy gains in speed, testing formulas over your precious functions might be the place to look.

What do you think? UDF or formula?

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter

I love the MVP summit. Despite the fact that many of my friends are missing, and we had two throw away (general Office) sessions this morning, the Excel content today was VERY cool. VERY VERY COOL!

We decided to make our first session a trip to the Microsoft Company Store, then followed it up with an impromptu Excel session of our own. Roger Govier, Jon Peltier, Mike Rosenblum, Charles Williams and I sat around discussing issues with the charting engine in Excel. A lot of fun, and we learned some cool things from each other too. Very cool stuff.

And after lunch, we had our standard meet the Excel team session, which is always entertaining. I can’t disclose the content of it, but the Excel team banter back and forth as we discussed issues, wants, needs and such was good. I really enjoy the banter with the Excel product team, as I do believe that they really want to see the product be the best it can be.

After that, we had a session on Excel services. I figured that this would be better than the morning sessions, but honestly wasn’t really expecting to get much out of it. Excel Services is a Sharepoint thing, which we can’t afford, and has always left me feeling like yelling out “To the Cloud!” with disdain. It’s always been something that is totally out of reach of us who don’t have Sharepoint, and has always been useless to me.

Wow… things changed for me in that regard today!

Again, I can’t discuss some of what was in the session, but there is some stuff I can talk about, as it’s already live.

Amy (who led the session) wanted to demonstrate to us how useful Excel Services and Excel WebApp are. So she spent some time trolling the MVP websites to find an example of a website with a good page to demonstrate what she wanted to show… and she picked one of my articles. I have to say that was a little humbling, and it felt kind of awkward as she’s showing my page off to the collection of my peers sitting around me. The page picked was my “Five Very Useful Functions For Working With Text“, which has also just been the source of an article for CMA BC’s Update Magazine.

Amy mocked up a copy of my website page to demonstrate injecting Excel Services WebApp into my site in order to give an interactive experience with the user. Rather than look at pictures of spreadsheets and have to download the example file, she used the webapp components, stored on Windows Live SkyDrive, to let the users actually type in the formulas and display them in the webpage, like this:

 

“Okay”, I’m thinking, “this is neat, and I can see the use, but it’s going to be a pain in the backside to get it to work.” So then she kicked us into lab mode and gave us an hour to try out the webparts.

After a bit of futzing around, learning how the webparts interact with a worksheet versus a named range, and how hidden rows behave, I implemented the parts on my site. Including that play time, I had the article converted to be interactive in less than an hour. I’m floored. The article is now live on my site, and you can fool around with it!

This is very cool. The workbooks I used reside in a public folder on my SkyDrive so you can open them and play with them in Excel WebApp (click the icon on the bottom right of the control). You can’t save them on my SkyDrive due to the sharing permissions, but you can save them from SkyDrive.

For an article that tries to teach, like this one does, this is perfect. I’m definitely going to be making much more use of this feature, and will have to put up a blog post/article on how to do it. J