Category Archives: Uncategorized

Todoist vs GoodTodo

Recently some guys I work with started using Todoist. It’s a todo list application with collaboration features. They set me up with an account and I tried it for a month. Here’s what I learned.

I’m sticking with GoodTodo. Sorry for the early spoiler, but since this is an Excel blog, some of you may not care about the details.

I meant to post this earlier, but never got around to it. Then I got an email that GoodTodo is raising their prices. It’s still a good deal to me. The email just reminded me to get off my butt and post my review. Also, my “really inexpensive” comment from six years ago needs to me modified to “moderately inexpensive”.

One thing before I get to the details: I’m only evaluating these applications based on features I care about. I don’t care about collaboration, so Todoist doesn’t get any points from me. If you care about collaboration, well, we’re different then.

Exporting
Remind me never to write mystery novels. I’ve already told you I’m sticking with GoodTodo and now I’m starting with the number one reason why. Anytime I want, I can send all my past, present, and future todos from GoodTodo to my inbox. Not so much with Todoist. Their export feature doesn’t export completed todos nor does it export the comments associated with the todos.

They do have an API. Presumably I could write my own code to get whatever I want, but that was beyond what I was willing to do.

This was a deal breaker for me. It’s my data even if it’s on your server and you shouldn’t restrict my access to it.

Point: GoodTodo

Entering Todos
GoodTodo has a feature where if you send an email to today@goodtodo.com from an email it recognizes, it will create a todo from the email. It supports all kinds of email addresses like tomorrow@, nextfriday@, and even specific dates.

As I’ve used GoodTodo over the years, I literally only send things to “today” and I move them from within the site if I need to. The fact that I don’t use this feature to it’s fullest doesn’t mean it’s not useful. I just means Mark did a lot of email parsing code that I never use. I still email todos quite a bit.

The downside to entering emails is that the subject of the email becomes the title of the todo. It’s pretty rare that I would have titled my todo like that if I were creating it from scratch, particularly if the email originated from someone else. I’m pretty careful with my email subjects, but I’m the exception, not the rule. If the subject is particularly egregious, I can just change the title in GoodTodo. If it’s good enough, I generally don’t.

Todoist does not support emailing to your list. If I had an email that I needed to put on my todo list, I copied the contents and pasted it into a new todo. That’s not as convenient as forwarding, but it honestly wasn’t that bad.

Todoist does support keyboard shortcuts in its web app. I had a twitter back-and-forth with them about how I couldn’t do certain things without the mouse, the result of which was I figured out how to do everything I need with the keyboard. It’s clunky. Things lose focus and, in one case, I have to attempt to close the todo without saving, cancel the close when I get the “save now?” message, and that returns the focus to where I need it. Not great, but workable.

Goodtodo has no keyboard shortcuts. I can navigate fairly well with my normal web page navigating tricks, but there is at least one task where I must use the mouse.

Point: A slight edge to Todoist because I’m a keyboard freak.

Editing Todos
GoodTodo provides a title field and a description field. I use the description field to record what I’ve done and what my next action is. It’s all freeform with no real structure. I have an AutoHotKey so that if I press Ctrl+; it enters the current date wherever I am, not just in Excel. So a todo might look like this:

NA: EM KK for truck revenue files

8/4/2016: Got revenue file and processed them

NA: EM JA for fee structure

8/5/2016: JA says fees will not be ready until Wednesday

Basically it’s a date stamp with something I’ve done (like EM (meaning I emailed someone)) and a bunch of NAs (next actions) for what I need to do next. It works and it’s fine.

Todoist has comments that are time stamped. I didn’t have to put the date next to my action, it was already done. That was pretty handy. I also like the structure of comments being a separate entity from the todo item.

Point: Slight edge to Todoist, but I can live with either one

Completing Todos
When you complete a todo in Todoist, it goes away. And it’s really hard to find after that. It’s there and you can get to it, but it’s not easy.

GoodTodo puts a checkmark next to it and moves it to the bottom of the page. If you want to know what you’ve done today, scroll down. If you want see what you did yesterday, navigate to that page and see what’s checked off.

It probably doesn’t seem like a big deal, but it turned out to be important to me.

Point: GoodTodo

Not Completing Todos
Yes, this does happen from time to time. If I don’t get something done today, GoodTodo moves it to tomorrow sometime after I’ve gone to bed (I think).

Todoist still shows me the todo, but it’s under a heading with the date it was added. It’s a lot of wasted space and clutter. Just because I added a todo on a certain day doesn’t mean it has anything to do with that day. I have four things left on my today todo list and I’m not going to get to any of them. I put them on there in case I did get to them, not because they were due today.

Point: GoodTodo

So I’m sticking with GoodTodo. I’d love to have a todo list app written just for me and maybe someday I will. It will have the rolling forward of GoodTodo, the timestamped comments of Todoist, the keyboard shortcuts of Todoist but slightly better, and most importantly I can export all my data any time.

This Theme Stinks

I found this nice plugin called wp-markdown. It lets you write DDoE comments like Stackoverflow answers, that is, markdown with preview. But it doesn’t work with my 10 year old theme.

I’m sure I’ve needed a new theme for a while. But if you’re like me, and you probably are if you’re reading this, you don’t like change. Now get off my lawn.

So if I switch to a fancy new theme, wp-markdown works. Seriously, try leaving a comment at the end of this post and see how awesome it is.

I don’t know why themes don’t take up the whole screen. I hate it when the words are all squished. I paid for a 22″ monitor and I want to use it!

I’ll leave this new theme up for a few days and see how it goes. In the meantime, I’ll still be looking for a new theme that looks and acts more like the old one.

New Excel and PowerBI user groups in Wellington

Do you live in Wellington, practically *live* in Micosoft Excel, day in, day out, and want an opportunity to pick up tips, tricks, hacks, and code from other Excellers? Have some tricks of your own to pass on? Need a second opinion on an approach you’re taking with a spreadsheet, or help untangling the monster mission-critical minefield of an XLSX file you just inherited?

If so, then boy do I have a group for you: I’ve just set up a dedicated Excel-focused meetup group at http://www.meetup.com/Wellington-Microsoft-Excel-Meetup/ that is just itching for folks like you to join. This Excel-centric group will pretty much cover the spectrum in terms of Excel’s incredibly deep functionality: Formulas, Tables, PivotTables, Charting, PowerPivot, PowerQuery, VBA, spreadsheet auditing and optimisation, dashboarding tips, information visualisation , the works.

I’m in the process of securing a venue for the first meetup, so expect to see more details soon. (And if you can help out with a venue, let me know).

At the same time I’ve come across Phil Seamark’s PowerBI group that has its first meetup on Wednesday at TradeMe. See http://www.meetup.com/Power-BI-User-Group-Wellington-NZ/ for details). No matter where your interest lies on the Excel/PowerPivot/PowerBI spectrum, between us we’ll have you covered. (I’ll be at the PowerBI event on Wednesday night at Trademe, and would love to see you there.)

So regardless of whether you’re a VLOOKUP virgin, or a VBA Virtuoso, a PivotTable pariah, or a PowerBI Professional, between these two groups we’ll have community led content, comment, and camaraderie relevant to you, your job, and your career.

So what are you waiting for? Sign up TODAY()

Why I’m going to Excel Summit South. (And why you should too).

At first I wasn’t going to. I live in Wellington, which is 636 km to the south. (395 miles, to those of you in the dark ages). And this being Middle Earth, to get there by road I would have had to run the gauntlet of Goblins, Orcs, and cliche tourists like Zack Barrasse likely driving on the wrong side of the road as he heads to Hobbiton behind a grin bigger than one face could safely hold:
Zack

Here’s a picture of me imagining I’m speeding around a corner only to find Zack heading the other way:
Jeff

And then even if I manage to avoid Zack, there’s no avoiding the famous Auckland traffic. I still haven’t gotten over how bad it was last time I was up there:
Auckland traffiic

(Don’t get me wrong…the traffic south of Auckland is not without its own challenges):

And then there’s the whole “Paying for Excel feels a little like the concept of paying for sex…I’m good enough at it these days that I really shouldn’t have to” thing. (Excel, that is.)

But then I read the program. And there is truly something for everyone. Even me, who’s a virtual demigod at it compared to the poor suckers around me who don’t use it at all and have no desire to start now. Not to mention the outstanding cast. These guys and gals are like the Dirty Dozen:
speakers2

Or at least, they would be, if there were one more of them. Oh wait, look: there’s two more of them below. Okay, so they’re like the Dirty Baker’s Dozen, then.
yigaledery_2 ben_rampsonThose two extras are a couple of guys called Yigel Edery and Ben Rampson from the Excel Project Team. These guys think they’re here to discuss the future of Excel. But I still live in the past – along with 99% of the rest of us – so I’ll make sure they find some time to answer all my tricky questions, like “How ’bout refreshing the Conditional Formatting Dialog. Have you ever had to actually use it yourselves? Huh? Huh?” …and… Where the hell are my Dynamic PivotTable References already? We’ve got ’em for Tables, but not for PivotTables. Have you ever tried to integrate PivotTables into a formula-driven spreadsheet? Huh? Huh? …and… Why does every ‘new’ Excel feature have the prefix ‘Formally Known As’? Have you ever tried to write a book about characters that kept changing their names half-way through? Huh? Huh?

Boy do I feel sorry for those guys: They’ll be dead keen to talk about the new extensions, while I’ll be dead keen to point out that the stairs that lead to them still are a little unsafe to use.  Let’s hope they’re still smiling like that when they get back on the plane.


I’ll also get to meet this guy, Ken Puls, Ken…who wrote the most useful Excel blogpost ever in the history of most useful blogposts ever on the strength of it’s excellent existential first line alone: Do you know why you are here? He’s moving on to something more future focused in his presentation: Do you know where you are going? (A little place called PowerQuery apparently, located in the hip new suburb of Get and Transform.) Can’t wait, because I know zipcode about it.


Mythbusters jon And following straight on from that, I’ll either get to meet the Myth-Buster shown left, or the Chart-Buster shown right. (Apparently speakers are provisional and may change.) At least one of those guys needs no introduction: He’s Jon Peltier, and he’s a regular addition to my Google Search Terms whenever I try to squeeze out a good chart.  And I owe him an entire beer of gratitude thanks to the best macro I never paid for. He’s going to pick up where Ken left off: Using Ken’s Powerquery data to build a Dashboard.


jelen I also finally get to meet Bill Jelen. I’m gonna ask him nicely to autograph my stack of Excel books…even the ones he didn’t write. (He didn’t write like 0.01% of them, so it won’t take him long).

The only problem is that Bill’s talk coincides with another from Ken on PowerQuery. Damn. But maybe I’ll just have to skip Ken’s sequel, because Bill’s gonna dive deep into Data Visualisations (yes, it’s spelt with an s down here), Conditional Formatting, and PivotTables.


ZackAnd of course, Zack isn’t here merely to look down Hobbits’ holes. He’s also here to talk about Tables, and perhaps to drink me under one afterwards.


charles And I’m super-excited to be meeting Charles Williams, who’s like the Phar Lap of Fast Excel. The Edmund Hillary of Excel’s capillaries. The Ernest Rutherford of Excel under-the-hood. If you’ve got spreadsheets that take about as long to open as the running time of your average installment of The Hobbit, then you need to read this, and then you need to come meet this guy.


And that’s just a select few of the wizened, Excel-scarred faces that I’ll see there. There’s a whole bunch of other international stars and local heroes coming too (including from that sleepy continent-sized Island that lies to the West of New Zealand) that I just can’t wait to INDEX and MATCH.

So yes, I’ve got my ticket. And I suggest you get yours pronto, because – as we say down here – it’s going to be O for Owesome.

You going? Give me a shout out in the comments, and we’ll CONCATENATE. You thinking of going, but haven’t quite committed? Shout out below anyway, and perhaps one of the Dirty Baker’s Dozen will drop by and talk you around.

Stack Cred

Ooh, look how special I am.

As a reward I bought myself some new monitors.

The real story is that one of my 22″ monitors died last weekend. It would display normally for about a second before going black. The consensus on the internet was to throw it away and buy a new one. It’s been a while since I bought monitors and I was expecting that 30″ monitors would be in my price range by now. I was disappointed that I they’ve only fallen in price enough that I could get 24s. But I’m pretty happy with monitors that are identical. And the boy is pretty happy to get my hand-me-down, working 22″ monitor.

My next problem was that my video card was too old to drive these beasts. I solved that problem by allowing the boy to open an early Christmas present.


EVGA GeForce GTX 970

He gets a better gaming card and I get his old video card. Easy-peasy. Except that it took me four days to get the cards swapped out as am I not the hardware genius I sometimes make myself out to be.

Have a lovely holiday if your religion or culture celebrates one this week. I’m going to see Star Wars on Christmas Eve, so if you see me walking around Denver this week, don’t spoil it for me (but be sure to say ‘hi’).

Filtering PivotTables with VBA? Deselect Slicers first!

I’m in the final stages of coding up a commercial add-in that gives you lot more filtering options for PivotTables at your fingertips than you get out of the box. Here’s how it looks, along with the PivotTable its connected to and a native Slicer for comparison:
 
Pre Invert
 
 

As you can see, it offers you a lot more tricks than a native Slicer – including a nifty ‘Invert Filter’ function. It also lets you see a lot more items displayed in it compared to a Slicer: 22 items are visible in mine, vs just 17 in the correspondingly sized Slicer. And that’s one of the many beefs I have with Slicers…they take up far too much screen real estate for the scant options they offer. In fact, my version takes up no screen real-estate most of the time: it launches simply by double-clicking the PivotTable field header, and you can dismiss it when you’re done to free up space if you want:
 
Slicer Dismissed
 
 
So about that Invert Filter function. I love clicking on that sucker over and over again. It takes about 7 seconds to invert my sample PivotTable that has 1000 items in it (12 items of which are selected in this example). That’s actually pretty fast as far as inverting a PivotTable, because you need to change the .visible status of all 1000 PivotItems, and as per a previous post that’s very slow to do unless you get tricky. And 7 seconds is a vast improvement on the method Microsoft gave you…none. I can’t comprehend why simple options like these are not built in to Slicers and Tables, but your filtering pain is (hopefully) my financial gain. (Yep, this works on Tables too.)

If I click that Invert Filter label, then here’s the after-effect:
 
Inverted
 
 

Beautiful: 7 seconds, and she’s turned completely inside out. Unless that is, that Slicer happens to be selected. Then it takes a full minute. Why? No idea. Moral of the story: if you’re writing code to filter PivotTables, then you probably want to make sure any Slicers for that field are deselected first. Not disconnected…you can leave ’em in place quite happily, and the code won’t suffer.

Anyways, that’s the first look at my new baby. It does a lot more than just this inverting trick, too. Among other tricks, it lets you filter PivotTables based on external ranges, and gives you some absolutely fantastic new tools for filtering PivotFields based on just about any tricky search conditions you might want to string together – but that’s a subject for my next post.

Excel User Voice and Workbook Calculate

Excel User Voice

At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.

https://excel.uservoice.com/

There are 2 important things to note about this:

  1. The Excel Dev Team actually read your suggestions on User Voice!
  2. Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.

So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.

Workbook Calculate

To try this out I made a suggestion (about calculation of course).

At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).

But you cannot calculate a single workbook.

This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.

My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10601079-workbook-level-calculation

So please vote for my suggestion if you think it’s a good idea!