Excel Events Calendar!

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.

New Excel Help Forums at Excelguru.ca

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!

Excel WebApp – Formulas that don’t work

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.

Data Validation in Excel Services

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:

Another Excel Services (Skydrive) Example

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!

I Love MVP Summits aka Excel Services is Useful!

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