Using superscripts in Excel’s custom number formats

One of the most under-appreciated features of Excel, I think, is Excel’s custom number formatting.

I will be alluding custom number formatting in a future post series as well, but here is a quick custom number formatting tip.

Let’s say I want to work with area or volume, and need to indicate that using custom number formatting (e.g., m2, or cm3). How do we add those in Excel?

Note, here, that I am talking specifically about custom number formatting. I can type m2 very (well, relatively!) easily in Excel—all I need to do is edit the cell, type “m2” and then select the 2, and use the Format Cells dialog to make it superscript. But that is not what we are looking at here. Say I have a formula, and whatever value the format provides, must be shown together with the m2 suffix. Or I have a blank cell, and when I enter a value (e.g., 3), then it is displayed with the custom format suffix (i.e., 3m2).

The problem, of course, is that we use the Format Cells dialog to create the custom format, and we also use the Format Cells dialog to add the superscript, and the dialog cannot be invoked on itself (i.e., we cannot format the 2 to be superscripted while entering it into the custom number format box in the Format Cells dialog).

Your first thought might be: Type the m2 and then format it to be superscripted as described above, copy it, and then paste it into the custom format section of the Format Cells dialog. But a long-standing gripe I have with Excel is its inability to really work with rich text, and when you copy text while editing a cell, all formatting (such as bold, italics, or superscript) is lost.

So we need a different approach.

To do this, we start, instead, with the Insert Symbol dialog. Because the Format Cells dialog is modal, we cannot invoke the Insert Symbol dialog while adding the custom number format (so we cannot use Insert Symbol to add the superscripted two to the Format Cells dialog). But what we can do is learn the character code of the 2. So I open the Insert Symbol dialog. Figure 1 shows this dialog, with the Superscript Two symbol selected. Note that, if I wanted something like cm3, the Superscript Three symbol is there as well.

Figure 1    Insert Symbol dialog

The next step is to find out what the ASCII code of this symbol is. Of course, we could look this up in a table, but I am using Excel here to find it. Nonetheless, in the From list box, I choose ASCII (Figure 2). That shows (in the Character code box) the value 178. We could, of course, insert the value into a cell, and then use the =CODE function to get the same information, but this is slightly quicker.

Figure 2    Insert Symbol dialog showing ASCII codes

Once I have this information, I can use standard custom number formatting procedures to create my format. So, for example, showing a number with one decimal, I would use the format #.0. I would then just add “m2” to that (including the quotation marks). I add the 2 by holding down the Alt key while typing 0178 on the number keypad. Figure 3 shows the creation of this custom number format in the Format Cells dialog.

Figure 3    Adding the custom number format

And, to wrap it up, Figure 4 shows the custom number format in use. Note that $B$2:$C$10 use a custom format #.0″m” and $D$2:$D$10, $E$2 uses to format I just demonstrated, #.0″m2“.

Figure 4    Custom number format in use

Share

Why I would not do my thesis with Word 2013 (and why I might)

I have been working with Word 2013 since the public beta was made available in October 2012—more than two years now.

While I have always had my pet peeves about the Office products, I have, in many ways, also been a bit of a fan boy for products like Word and Excel. Even with Word 2007, which many people deplored, and about which I had my own complaints, I was still quite able to see improvements which made the upgrade worthwhile. However, even after two years of use, I find myself particularly unimpressed with Word 2013. In fact, most of Office 2013 has left me rather unimpressed, if not downright disappointed. A prime example is the dumbed-down Presenter view in PowerPoint 2013—which Microsoft touted as a big improvement, but which is, in comparison to what PowerPoint 2010 offers, so pathetic that I refuse to do live presentation with PowerPoint 2013 if I can help it. Excel 2013 is the only program where, along with the regressions (like the dumbed-down sheet tab controls that I detest, and the dumbed-down charting tools that I find actually hinder the quick creation of charts), there are also noticeable improvements that matter to me. I suppose that’s an important point to make, because, of course, Microsoft has made many “improvements” to all their products in the 2013 iteration, it’s just that most of them are the kind that I could happily live without, and the things that are important to me, I find frustration.

In fact, if I stop to think about what it is I find so distasteful about Office 2013, it is that I feel it is a definite dumbing-down of the product. Some things are superficial and cosmetic (even after two years, I prefer the Office 2010 ribbon—which is, itself, a toned-down version of the too-loud Office 2007 ribbon—to the bland, dead Office 2013 ribbon). Or the new UI messages which are just too informal, even for me (actually, it’s not the informal that bothers me, but that fact that they have, at the same time, become uninformative). Other things are more serious, like the removal of tools that I actually use (see autocorrect below).

Positives

But before I make this one long gripefest, let me highlight some of the positives in Word 2013. You can see this article to see what’s new in Word 2013, most of my comments here relate to the new features.

Microsoft has made some significant changes to reviewing. While you will see below that some of them are negative, one positive is that reviewers can now comment on each other’s comments. The process is quite smooth, and, if a student and multiple promoters are all into electronic reviewing, a real time saver. Promoters can see what other promoters have said, and can weigh in themselves. Of course, we will all debate like adults, won’t we! J

Word 2013 now also offers the option to embed videos into a Word document. While PowerPoint has been able to do that for a very long time, it has always seemed a bit pointless in Word, but that has changed with the rise of eBooks and the rising popularity of multimedia content in eBooks. So while you still can’t print out a video on paper, put it into an eBook, and it could work. So there, Word is keeping with the times, and this an important addition.

Furthermore, Word 2013 now also has the image guides which were added in PowerPoint 2010, but not the rest of the 2010 Office suite. This also makes working with images much easier, and is a welcome addition, although it should have been available in Word 2010 already—I sometimes get the impression that the Office team doesn’t get time to complete all the planned work before the scheduled release date, and so we have one feature being progressively made available throughout the suite across several releases.

The integration of OneDrive giving you cloud storage of your documents is also a boon, but I must add that one does need to use the cloud “carefully.” If your account gets hacked, and your files get deleted, and those are the only copies around, you might be in for some trouble.

The layout of the Office 2013 applications are supposedly to also make them easier to use on small devices like tablets, but I find that a bit of a non-argument, as I really can’t see why I would want to do my thesis on a small device like that in the first place. Maybe for some extra work at the airport or on the road, yes, but for the data-to-day work needed to complete something big like that? No way!

Negatives

The Resume reading feature is a waste, since Shift+F5 has worked in every version of Word except Word 2007, and in any case the little message often disappears before you can click on it—what good is it then?

While I like the ability to comment on comments, the change to “simple “reviewing is a downright pain. Again, Microsoft dumbs things down, which may make them more unobtrusive, but also make them less efficient.

Microsoft removed AutoCorrect. I’m not alone in missing it. They say people never used it, which just means that they never survey people like me, who do use it. Fortunately, Greg Maxey shows how to fix this.

Microsoft also made important changes to the way their citation tools work which render even advice Microsoft posted about how to customise the tools invalid. I discuss some of these changes here. What really irks me is that they did not announce these changes in any way that I could find—they just made them. That is not the way to win or keep the confidence of your users.

Did I mention how much I detest the flat, uninspiring interface of Office 2013?

One of the things I really hate is the vanishing scroll bars. Type in Word, and the scroll bars disappear. Move the mouse and they momentarily reappear. I suspect this also has to do with getting Word ready for smaller screens, but for goodness sake, I have a big screen, and I am proficient enough to type for an extended period of time without have to resort to the mouse. I have grown accustomed to glancing at the scroll bar, as it provides important visual feedback as to where I am in a document (I read a lot of other people’s documents, so I need this information very frequently), and I still get irritated when I look in its direction to get that information, and it’s not there!

I have also found Word 2013 to be significantly slower than Word 2010. This is in all areas, from running macros (I have documented tests for running the same macro in both versions, showing Word 2013 to be up to 40% slower), to loading documents, saving documents, and even just the time it takes to open. I have Word 2010 and 2013 installed on my current system, so it has nothing to do with the system. The degraded performance is noticeable.

With Office 2013, Microsoft have gone all out for online help. That already irritates me, because getting online help is necessarily slower than getting installed help. When you click on Help in Office 2013, you don’t get a help browser that open—no, you get your web browser opening to go to Microsoft’s site. That takes a few seconds at best, and each time a web page loads, more time is lost. Thankfully, you can still set the Application help to Offline help, but the help from the VB Editor goes straight to the browser. And that’s the help I use the most. But it’s not just that that frustrates me. Microsoft has changed the very nature of their help content. The impression that I get is that, I would assume to save costs, they seem to have fired all their help-writing staff, and now all you get in your web browser is a search of various Microsoft sites and the rest of the Internet on your help topic. If I had wanted to go to a search engine to see what the rest of the world has to say on a topic, I would have done that. When I click on help, I expect to find the documentation provided by the people who made the program on how their program works. It seems harder to get that now than ever before.

One last thing (I think). I simply deplore the new dumbed-down spell checking pane which has replaced the old spelling and grammar dialog. Not only is it not now possible to ignore grammar rules, you can now also not edit the misspelled item in the dialog (e.g., if none of the suggested items matches what you actually intended to type–yes, I do type that badly!), but have to jump to the text to change it, and then resume spell checking. These are small changes, but I find that spelling & grammar checking now take much longer than they used to (i.e., a decrease in productivity). Oh, and to make matters worse, despite the buttons in the pane being marked with accelerators, they do not respond to the keyboard, so you have to manage the process with the mouse now, which is even slower! And it’s not just in the pane that the dumbed-down spell checking annoys me. I have already mentioned the missing auto-correct, but the long history of red, and green underlining for spelling and grammar errors, respectively, with blue added for contextual errors since Word 2007, has been changed. Now, in Word 2013, we have red for spelling errors, and blue for grammar and contextual errors. I realise that the choice of red, blue, and green may have been unfortunate for those who are colour blind, but why not include options in the interface to change those colours? Now, however, Word 2013 is giving me less information than Word 2007 and Word 2010 in terms of inline spell checking. And I find that annoying.

Summary

In short, if you asked me for advice, then unless you were a hardcore going-paperless-all-the-way student (who has promoters who can actually play along) or a cutting-edge creative arts student, where your thesis will incorporate video, I would tell you to stick with Word 2010 for your thesis.

I am still rooting for Microsoft, and I am hoping that Office 2016 will be to Office 2013 what Office 2010 was to Office 2007—the way it was meant to be.

Share

Filtering for only the first instance of all duplicate items

If you’ve been using Excel for a long time (i.e., at least since Excel 2003), then you will know that the Filter tool has come a long way. Already in the earlier versions of Excel (i.e., ?Excel 2003) it was a very useful tool, but it underwent a big makeover in Excel 2007, which was not without its problems, and Microsoft ironed those out in Excel 2010 to give us a really nice tool. But even now, it still has its shortcomings. I quite enjoyed Dick Kusleika’s posts on Daily Dose of Excel about “An Even better AutoFilter” (although I don’t quite see it as something that I will be adding to my armoury). See:
http://dailydoseofexcel.com/archives/2014/10/16/a-better-autofilter/
and
http://dailydoseofexcel.com/archives/2014/10/21/an-even-more-better-autofilter/

In this post, I will present another interesting use case that extends the capabilities of the filter.

Consider the following list (Figure 1). It presents the exported data from a great time management tool I use (ManicTime). The export is of the application-level data recorded by the program, exported as csv and imported into Excel (I also modified the column arrangement). There are a lot of duplicates (quite understandably), such as those in E5, E7, E10, E12. But what if I only wanted to see one instance of each item, regardless of whether it is duplicated or not?

Figure 1    Sample data list to be filtered

One method could be to make use of a helper column, but I thought it might be better to do without that.

Filtering for only duplicates or only singles is easy—just apply conditional formatting to highlight duplicate items, and then filter to show only those items with or without formatting, respectively. But filtering for only one of each item requires showing all the singles, as well as the first of all duplicates.

Working with ranges in dialogs

Illustrating this also allows me to highlight some tips of using the Range text box (I am actually not certain whether this is its “official” name, but you will see what I am referring to)—which appears in numerous places, amongst others the New Formatting Rule dialog. The range text box is identified by the Range Select tool (ditto about the “official” name) which appears to its right—clicking on this will shrink the current dialog (and another click will expand it), and while the I-beam is in this text box, you can select a range on the sheet, the range address of the selected range will then be entered into the text box. Essentially, clicking in the Range text box is akin to editing a cell’s contents (you can enter a formula, select ranges, etc.).

New Formatting Rule dialog

Figure 2    New Formatting Rule dialog, showing Range text box

Range text box tip #1:
Sometimes, when you are typing a formula in the range text box, you make a mistake and want to go back and correct it. If you’re like me, that does not entail abandoning the keyboard, grabbing the mouse, clicking where you want to edit (sometimes a few clicks to get it in the exact right place) and then returning to the keyboard to edit, but rather just a few quick left arrows with or without Ctrl) to get to where you want to be and edit. But there is a problem. Because you are in the Range text box, Excel assumes you want to select a range (starting from the Active cell), so when you press the left arrow (as an example), Excel dutifully selects the cell to the left of the Active cell and enters its range in the middle of your formula. But remember that working in this text box is akin to editing a cell formula—in a cell, you switch (toggle) between moving within the cell contents, and moving across worksheet ranges, by pressing F2. Same here. So press F2, and then you can happily move around within the formula you are typing.

Range text box tip #2:
Whenever you do select a range while in the Range text box (whether with the keyboard or the mouse), Excel enters the range address as an absolute address, and this is definitely by design, as most of the time, this is exactly what you need. But there are times that you want relative addresses. Again, you can edit the formula just like in a cell: Move to the range address, and press F4 until you get the reference type—Relative, Mixed (x2), or Absolute—that you want. But here is the real tip: When using relative references (or, for the relative parts of mixed references), the formula is defined for each cell in the selected range in the same relative way that the formula applies to the Active cell. So always look to see what the Active cell is, and write your formula accordingly.

Now that may be confusing (I am finding it hard to explain), so let me illustrate.

The data shown in Figure 1 actually covers this range: $A$1:$E$607 (it was a quiet day on my laptop, and I did most of my work for the day on my office PC). If I select $E$2:$E$607 by starting at $E$2 and pressing Ctrl+Shift+Down, Excel then extends the selection all the way down to row 607, showing me, then, those bottom rows; but the Active cell remains $e$2 (Figure 3). So when I enter my formula into the Conditional formatting dialog, I must keep in mind that it is relative to E2 for E2, and that it is adjusted accordingly for every cell from E3:E607 in the range.

Figure 3    Active cell in a selected range

This is important, because even though E2 is the Active cell, I can’t see it. And if, for example, I had select E607 and pressed Ctrl+Shift+Up, I would then see E2, but E607 would be my Active cell.

Filtering for only the first instance of all duplicate items

Now after all that, we can turn our attention to the filter problem. This, of course, actually very simple. I add a new conditional format, using a formula, to mark all duplicates from the second on (note that the formula is relative to E2):

=AND(COUNTIF($E$2:$E$607,E2)>1,MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1))

Let me unpack that.

The AND function ensures that both criteria must be met: 1) It must be a duplicate, and 2) it must be a subsequent occurrence of the duplicate, not the first occurrence.

COUNTIF($E$2:$E$607,E2)>1 uses $E$2:$E$607 as the comparison range (we keep that absolute, because we don’t want it adjusting down for all the cells below E2), and counts all the occurrences of the current cell (because E2 as the criterion is relative). For each cell, if it occurs in the range more than once (i.e., >1), it is one of a set of duplicates. All singles will return FALSE for this, and all duplicates will return TRUE.

The second AND argument is perhaps slightly more complex:
MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1)
MATCH returns the first instance of the criterion (E2—relative, so it is always adjusted for the current cell) in the lookup range ($E$2:$E$607, again, absolute)—Note that I am using exact matching. So it will always return the position of the first occurrence of the set of duplicates to which the current cell belongs, if the current cell is one of a set of duplicates, and it will always return the position of the current cell if the current cell is a single. However, MATCH returns the position in the range, not the row number (i.e., position 1 in $E$2:$E$607 is 1, not 2). But I need to check whether the row number of the first instance of the duplicate set (not its position) corresponds to the row number of the current cell. The relative ROW(E2) gives me the row of the current cell (whichever one of the 606 cells in this example that may be). I then just need to compare that to the row corresponding to the position returned by the MATCH function. Now I could have simplified things and said: MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-1, but that would mean that my target range must always start in Row 2, as I have essentially hard-coded the row into the formula. Instead (perhaps somewhat pedantically), I avoid the hard-coding by supplying the first row of my range ($E$2) and returning its row— thus ROW($E$2)—and then subtracting 1 to find the row just before it). I suppose I could also have used ROW(OFFSET($E$2,-1,0)) to eliminate any confusion about what this part of the formula does. Nonetheless, this gives me the more complex, but more flexible, MATCH(E2,$E$2:$E$607,0)<>ROW(E2)-(ROW($E$2)-1).

Now that we have the formula, we just select $E$2:$E$607 and click on: Home | Styles | Conditional Formatting | New Rule…. That opens the New Formatting Rule dialog (Figure 4) and we add our formula (note in Figure 4 that E2 is my Active cell).

Figure 4    Creating the Conditional formatting

Once this is added, all subsequent duplicates are highlighted, but not the first instance of the duplicate set, and not any singles (see Figure 5).

Figure 5    Conditional formatting successfully applied

Once the conditional formatting has been set, it is a simple matter to filter for those cells showing no formatting (Figure 6), which gives us all singles, and all first instances from all duplicate sets.

Filtering for no formatting

Figure 6    Filtering on formatting

Share

Table styles and multiple-row table headers

This post might present you with something small, to which you say: “I can’t believe he didn’t know that,” or it may just show you something so deceptively simple that you end up saying “I can’t believe I’ve missed that all these years—it’s so obvious, but simultaneously so counter-intuitive.” So this post won’t be “up there” on the Wow-factor scale, but it may just simplify one little aspect of working with tables for you, I hope.

As readers of my book will know, I am a (cautious) fan of Word’s table styles. Cautious, because while they can drastically ease and speed up the process of creating multiple uniformly formatted tables, they take some getting used to, and you can just as easily do something wrong and “blow up” your tables if you don’t know what you’re doing.

One of the nice things about table styles is that you can set formatting for the table as a whole, and then for each specific constituent part of the table (Figure 1). You can specify individual (and thus differing) formatting for:

  • Whole table
  • Header row
  • Total row
  • First column
  • Last column
  • Odd banded rows
  • Even banded rows
  • Odd banded columns
  • Even banded columns
  • Top left cell
  • Top right cell
  • Bottom left cell
  • Bottom right cell

Figure 1    Choosing which portion of the table formatting is specified for

This feature makes table styles very flexible: Note the group of Table Style Options on the far left of the Table Tools: Design ribbon (Figure 2). These setting, as it were, turn the differentiations you specified in the process of defining the various elements of your table style, on and off. Thus, if you specified a bold header row, then the text in the header row will only be bold if the Header Row check box in this group is selected. If not, then the text in the header row will appear as defined for the whole table.


Figure 2    Table style options

Probably the most-used of these various constituent parts is the Header Row (and probably then First Column after that). It stands to reason that the Header row formatting applies to the first (i.e., top) row of the table. However, here we have a dilemma. What if you have specified a certain header row formatting, but some of your tables have headers that span two (or three) rows?

Once you see it, it is obvious, but it’s one of those things that is so obvious that we (myself included) often overlook them. You should know that the Repeat Header Rows function (Figure 3) can be used for multiple rows. This setting is used if you have long tables that span more than a single page, as it allows the header row(s) of the table to be repeated on each successive page.


Figure 3    Repeat Header Rows

The interesting thing (and this is not necessarily intuitive, and has not, to my knowledge, been documented by Microsoft), is that turning this setting on with multiple rows selected, even when your table does not span multiple pages, will instruct Word to treat all selected rows as header rows, and Word will then apply the header row formatting to all of those rows. Et violà!

Caveat:

Yes, there’s a catch. This works fine for settings like font settings, shading, even repeating header rows. But it doesn’t work for borders. So if you want, for example, only a bottom border and have that set on your header row formatting, then when you do this with multiple rows, each row will have the bottom border, not only the lowest one as you might have hoped. That will require a manual fix, unfortunately.

Share

Learning Keyboard Shortcuts

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail list about keyboard shortcuts. This led to me sending a mail to the list about how to teach oneself keyboard shortcuts (or, more precisely, the system I use to teach myself—and I am still continuously learning—keyboard shortcuts). Since I realise that it might prove useful to others, I have modified that mail slightly for this blog post. If you find what I write below useful, this topic is discussed in greater detail in my book on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: http://www.databison.com/so-how-many-of-these-excel-shortcuts-do-you-know-punk) then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

Share

Harvard SA update for Word 2013

As you can imagine, it’s been a while since I’ve used the Word referencing system (having switched totally to Mendeley, as my book on the topic attests).

So it was a bit of a surprise when a student came to me and said that my Harvard SA style for Word is not working in Word 2013. It took a bit of head scratching, and then some internet searching, to get a solution. As usual, it was Yves Dhondt of BibWord who had already come to the rescue.

http://bibword.codeplex.com/discussions/404418#post956736

I have made the change he recommends, and it now works in Word 2013.

It still took a little more trying, after making his recommended modification, to find that I needed now to add the HarvardSA.XSL file to

c:\Users\Jacques\AppData\Roaming\Microsoft\Bibliography\Style\

and not (on my system):

c:\Program Files (x86)\Microsoft Office\Office 2013\Office15\Bibliography\Style\

In short, I recommend doing a complete file search for Chicago.xsl, and copying your extra style files to each place where you find that file from Microsoft.

The updated file can be downloaded from my example files page:
http://insight.trueinsight.za.com/word/dissertation/examples (or directly from here). Unzip it, and copy it to the aforementioned folders.

The new file works on Word 2007, Word 2010, and Word 2013 (tested on my system), so there’s no need for separate versions.

I must say that I am a bit disappointed (understatement!) in all this. Think about it. Microsoft creates a new tool (in Word 2007). They even encourage us to customise it for our own use:

http://blogs.msdn.com/microsoft_office_word/archive/2007/12/14/bibliography-citations-1011.aspx

http://blogs.office.com/2009/04/29/bibliography-citations-102-building-custom-styles/

Then they go and change the way the tool works, so that the customisations they encouraged us to make are rendered inactive. But they don’t post any notice about these changes, not in their online documentation (such as this page or this page or this page), not in their blogs, nowhere. This is not the kind of thing I expect form a company that charges you serious money for their product (I confess that I still feel it’s worth the money, but then they need to give the support the product’s price demands).

Ah well, ranting like that doesn’t help. But at least you can still do your thesis referencing, even if you are using Word 2013! Thanks Yves, you’re a hero!

Share

Using the Date picker

I was working on a new tool for the Word uTIlities, when I discovered that there really doesn’t seem to be particularly much available by the way of good information on how to use the date picker control. Not even the two “standard” works I have on my bookshelf (John Walkenbach’s Excel 2010 Power Programming with VBA and Bovey, Wallentin, Bullen and Green’s Professional Excel Development) make much mention of it.

So, while this is not intended to become the be-all and end-all of your search for information on the date picker, I do want to add here what I have learned so far.

Why use the date picker?

In all honesty, getting date and time information from users can be done with standard tools like text boxes or combo boxes. But the date picker offers a large number of advantages, and the main reason for using it is the huge convenience it offers. Firstly, it is more convenient for your users, as it offers them date/time specific ways of providing information (e.g., a “digital clock” for time, and a calendar for dates), while still allowing keyboard folks like me to type in the information they want to add. And convenience for users is not a frill, but a necessity.

Having said that, the date picker also offers huge advantages for the programmer. First and foremost is that it delimits the information users can add, so that you can by definition not get invalid dates or times from your users, obviating a large amount of code that would otherwise be required to test and validate what they have entered. Furthermore, it can be customised to let users enter only the information you want (more on that below). And finally, its values are already in the date format (which in VBA is date/time), so it both accepts and provides date/time information without the need for conversion.

Getting the date picker

Finding the date picker is not obvious. In the VBE form editor, right-click on the toolbox and choose Additional Controls… (Figure 1).

Figure 1    Additional controls for the Toolbox

Then, in the Additional Controls dialog, find the Microsoft Date and Time Picker Control and make sure that the box next to it is marked (just clicking on the control doesn’t mark the box, it just selects the control—you have to click the box itself), as shown in Figure 2.

Figure 2    Additional Controls dialog (Showing Microsoft Date and Time Picker Control)

Click OK, and the control will appear on the Toolbox (Figure 3).

Figure 3    Date Picker added to Toolbox

Now you can click on the tool, and add date pickers to your user forms as you would any other control. Figure 4 shows a new tool I am working on for the Word uTIlities (this will be version 1.9), although I am not certain that all the options shown here will appear in the final version. This is suddenly quite a heavy usage of date pickers—eight at the moment (the three on the right might not make it, though…). Note the three different applications of the date pickers. Some are used to give dates, some for times, and one (bottom right) for only hours.

Figure 4    User form showing various date picker controls

Customising the date picker

I will limit this section to things specific to the date picker. Since it is a form control, it shares many of the normal form control formatting settings, which I will not discuss.

To set how the control works, it is best to work with the Properties pane. Figure 5 shows the Properties pane for a date picker control, with the relevant items highlighted.

Figure 5    Date picker properties

Note that I haven’t highlighted the Calendar color settings, but these are only for window dressing, and should be easy to figure out if you should choose to modify them.

Here’s a quick rundown of what you can do with these properties:

Use MaxDate and MinDate to set (respectively) the latest and earliest dates from which the users can choose (the default range of 1601 to 9999 might be a bit broad…).

The Value property allows you to choose what value the date picker displays when the form is loaded. See below for more details on changing this at run time.

The most important settings are the Format and CustomFormat settings.

For Format, Figure 6 shows that you have four choices: Long date (0); Short date (1); Time (2); and Custom (3).

Figure 6    Format property settings

Figure 7 shows what each of the first three look like (Short date on the left, Long date in the middle, and time on the right).

Figure 7    Date picker formats

Note that time defaults to a 12 hour clock with hours, minutes, and seconds.

So what if you don’t want one of those formats (for example, you would want date and time, or you don’t want seconds, or you want a 24 hour clock, etc.)? In these instances, choose the last format—Custom (3). Then, in the CustomFormat property, define the format you want. I am not going to go into an extended discussion of the formats now—if you are familiar with number formatting (e.g., in Excel, or in Word Merge fields), you will understand what is required. So, for example, to leave out the seconds, use hh:mm. To leave out the seconds, and switch to a 24 hour clock, use HH:mm.

But there are problems. Figure 8 shows a date picker set to the Time (2) format on the left, and one using the Custom (hh:mm) format on the right. The problem, as is patently obvious, is that when the custom format is set, the spin buttons are removed (they are quite important for a time picker) and the drop-down again gives the calendar (essentially rendering it useless for setting a time).

Figure 8    Standard time format vs. custom time format in date picker

So what to do? Well, if you’ve been observant, there’s one highlighted property in Figure 5 that I haven’t discussed—UpDown. That’s what turns on the spin button (and removes the calendar) for the time picker.

Furthermore, the Value, MinDate, and MaxDate properties remain set to dates, even though the custom format specifies only time, although that is not really a problem—just type a time in, and the VBE will accept it.

Also take note of the (Custom) “property”—note that it is in parentheses in the properties pane, indicating that this is not actually a property. Sure enough, click in the property value field, and the dialog launcher appears (Figure 9).

Figure 9    (Custom) date picker “property”

Click the dialog launcher, and the Property Pages dialog appears. I will not discuss the Font, Color, or Picture tabs. But let’s take note of some of the settings on the General tab. Many of these are in the Properties pane ad well, but you can set them here if you wish.

Figure 10    Property Pages dialog

How the date picker works for users

The beauty of the date picker tool is evident when the form is loaded and the user has to set a value. Clicking on the date picker reveals a calendar (Figure 11) that users can navigate (note the previous and next month selectors at the top) until they get to the right value. There, they can click on the date they want, and it is set as the value in the control. And, of course, the picker only presents users with valid dates to choose from. And if users type in invalid dates (e.g., 2014/02/29), the control won’t accept them.

Figure 11    Using the date picker

Times are also easy to set (Figure 12). The user clicks on the time part they want to set (e.g., in Figure 12, the minutes section is clicked). Then the user uses the spin button to move the value up or down. If it reaches the maximum allowed for that time unit (e.g., 59 for minutes or seconds), it just moves on to the minimum again, and vice versa. Users can also type in times if they want.

Figure 12    Using the time picker

Setting the date picker values

One of the nice things about the date picker is the ease with which you can set its values when you load your form (or at any other event, such as when another control on the form is set—the form in Figure 4 does exactly that). For example, for some unknown reason, the value property of the date picker is set to the date you add it to the form. But this means that, for example, a year hence, the date displayed initially by your form will be a year old, when you would probably want it to display the same date on which the user has launched the form.

So what do you do if you want to load specific values that will be determined at run time? The date picker makes this incredibly easy, since it accepts date values. So, for example, some of the underlying code for the form shown in Figure 4 is:

Dim DateTimeFirst As Date
[…]
DateTimeFirst = rev.Date
[…]
If Left(Me.CBxDateSelector1.Value, 6) = "Before" Then
Me.DTPicker1 = DateTimeLast
Me.TMPicker1 = DateTimeLast

Note that I set the date-formatted date picker and the time-formatted date picker to the same variable (the Date data type in VBA stores both date and time), but each date picker uses only the portion relevant to its formatting—the date for the former, the time for the latter. Of course, you could make doubly sure, or convert variables of other types (like string variables) to date or time values like this:

Me.DTPicker1 = DateValue(DateTimeFirst)
Me.TMPicker1 = TimeValue(DateTimeFirst)

Which of course also means that you could specify something like:

Me.DTPicker1 = DateValue("2014/04/01")
Me.TMPicker1 = TimeValue("14:05")

Although why you would want to hard-code values like that and not add them manually as defaults is beyond me.

One last thing: to set the date or time to the current (date or time), simply use

Me.DTPicker1 = Now ()
Me.TMPicker1 = Now ()

Getting date picker values

Opposite to the above, once the user has entered a value in the date picker, you would obviously want to get that value and use it in your programming (typically when the OK button has been clicked).

The code is essentially the reverse of what we have seen above. Note that since VBA only has the Date data type (and not a separate Time data type), you would declare your variable which is to hold the date picker’s user-set value as a Date Variable. Then simply set it to the date picker’s value:

Dim Date1 As Date
Dim Time1 As Date
[…]
Date1 = DTPicker1.Value
'Of course, the '.Value' is optional, since value is the default property.

Time1 = TMPicker1.Value
'Of course, the '.Value' is optional, since value is the default property.

Again, you can make more certain with

Date1 = DateValue(DTPicker1)
Time1 = TimeValue(TMPicker1)

But that does beg the philosophical question of how more certain you can be when you are already certain….

[And I think that’s my cue that this blog post has now gone on long enough!]

Share

Separate table (or figure) numbering for appendices—Take II

This post will only make sense after you have read the first part. If you haven’t read it yet, go and read it first, and then follow the link at its end to return here.

If you’ve used my uTIlities, then you know that I do a little programming on the side. As such, although I am by no means an expert, or even a professional, programmer, I do have sympathy for the challenges that programmers face, like, for example, the team at Microsoft responsible for Word or Excel (despite my occasional griping about things I don’t like). One of the biggest challenges I find in programming is that I cannot anticipate all the contexts and ways in which some tools might be used. For example, how will the Fit Hyperlink tool work when the hyperlink is in a table? At first, I didn’t program for that eventuality, and, of course, the first time I tried it in that context, it didn’t work.

Of course, that doesn’t necessarily only happen when we do programming. About a year ago, I wrote a piece about how to get caption numbering set up in appendices. Of course, it worked, and the post has been reasonably popular, and, I am glad, seems to have been helpful to quite a few people. And then someone contacts me and lets me know that my solution is not working. Why not? They have tables and figures in their appendices, and I only set it up for tables. My solution would work for tables, or figures, or any other caption, but only when only one of those is present in the appendix. When tables and figures appear in the appendices, then the List of Tables and List of Figures (actually, in terms of Word, the TOC for tables and TOC for figures) contains all the tables as well as all the figures.

So how to fix this?

Well, the approach I suggested previously for the numbering is sound (the problem is with the TOCs), so for the tables, use SEQ fields with the customised identifier AppendixTable and use the customised identifier AppendixFigure for the figures. But for the List of Tables (and the List of Figures), we now need to split up the process. First, add this field: { TOC \H \C “Table” }. Then, right next to it, this one: { TOC \H \C “AppendixTable” }. What we are doing, is essentially creating two TOCs for the List of Tables: The first one finds all the tables in the main document (i.e., our chapters), then the second one finds all the tables (marked with the SEQ identifier AppendixTable) in the Appendices.

There is one trade-off. When the fields are built, an extra paragraph is inserted. It doesn’t actually look that bad, though, and the break between the main tables and the appendix tables seems natural.

There are some alternatives to this, if you really don’t want the break. Firstly, you could manually mark all the tables (and figures) with TC fields (I explain how to do this in my book). This will allow you to build a single TOC for the tables and a single TOC for the figures. But this is a lot of manual work, and I want an approach that is relatively automatic.

If you want to see this in action, this sample document shows the effect.

Share

It’s here! The first book published on Mendeley

After a year’s work, I can now proudly announce that it’s here:

Mendeley: Crowd-sourced reference and citation management in the information era (ISBN: 9780620594424).

book front

This is not the first book that has ever mentioned Mendeley, but to the best of my knowledge, it is the first book in the world devoted entirely to Mendeley.

It covers everything you need to know to get on the go and make a success of your research with Mendeley.

If you are in SA, it can be ordered here.

I will update this post when it is available in South African bookstores.

Alternatively, go here:
France: http://www.amazon.fr/dp/062059442X
Germany: http://www.amazon.de/dp/062059442X
Italy: http://www.amazon.it/dp/062059442X
Spain: http://www.amazon.es/dp/062059442X
UK: http://www.amazon.co.uk/dp/062059442X
US and Rest of the world: http://www.amazon.com/dp/062059442X

Enjoy!

Share

Using Wildcard Search and Replace to switch from manual numbering to automatic numbering

This is again in response to a question from the Word-PC e-mail list.

You have a document where the numbering has been entered manually, and you would like to convert this into a document which uses the different heading styles with associated numbering.

This is a slightly modified version of Example X (that’s ten, by the way) from Application 10.3 (Practical wildcard searches) in my book Doing your dissertation with Microsoft Word.

Students have manually numbered and formatted their thesis before learning about the principles taught in this book which allows Word to do their numbering for them. They want to start using this system (e.g., so as to still be able to use Word to create their table of contents), but cannot simply apply the heading styles, as all the old numbers still have to be deleted manually, and in any case it would be great if Word could actually find all their headings and update them to the styles automatically, without the students having to apply the style to each heading.

The secret is to start with the lowest heading level, hierarchically speaking. Thus, if your numbering is like this, then level 4 is your lowest hierarchical level:

Level

Numbering example

1

Chapter 1

2

1.1

3

1.1.1

4

1.1.1.1

Since the principle is the same for each level, I will only do levels three and higher in this example.

Also note that this assumes that the students very consistently did their numbering—they missed no separating periods, made no formatting mistakes, etc. Every time one of those mistakes is made, it means that the search and replace might miss something. In short, given the human propensity for error, it is best to first make a backup of the document (use my Word uTIlities to make that easy), and then to do a thorough check once the search and replace process has been completed.

For heading level three, the search specification is “([1-6].[1-9].[1-9] )(*^13)”—obviously without the quotes, here and further—which assumes that the students did not ever use more than nine third level subheadings to a single second level heading, the same for second to first level headings, and that the student has six chapters in the thesis. When in doubt, go big and specify it as follows: “([1-9].[0-9]{1,2}.[0-9]{1,2} )(*^13)” which allows for up to a single digit first level, a two digit second level, and a two digit third level (i.e., 1.1.1 through 9.99.99). Very important here is to note the space before the first right parenthesis.

To explain what this is doing, the search specification consists of two groups (as defined by the two sets of parentheses): The first group consists of the number, which is, in the first example, a digit between 1 and 6 for the chapter, followed by a period, a digit between 1 and 9 for the second level heading, followed by a period, and a digit between 1 and 9 for the third level heading, followed by a space. The second group is any text (in this case, the heading text) up to a paragraph mark. The replace with criteria is simply \2 with the formatting specification of Style: Heading 3 (Click on Format, Style in the Find and Replace dialog, and choose Heading 3). This (\2) puts only group 2 (the text and the paragraph mark) back in the replace action. The style is what adds the numbering.

Thus the number is stripped away, and the style applied, all at the same time.

In addition, the formatting for the Find What box should be set to the formatting used for the third level heading. This is actually quite necessary, as without that, the search and replace will also find cross-references in the text to those heading numbers, which is not what you want to do!

So it would look something like this:


The process can then be repeated, with ([1-6].[1-9] )(*^13) for the second level heading, and exactly the same replace with specification, except that now the formatting is set to Style: Heading 2, and of course the appropriate formatting for the Find what box too.

For the Chapter titles, again the appropriate formatting for the Find what box is set, and then the specification is (Chapter [1-6] )(*^13) with exactly the same replace with criteria and with the formatting specification of Style: Heading 1. In this way, an entire manually numbered thesis can be transformed into an automatically numbered thesis in a matter of minutes.

Since you have made a backup of the document, give it a bash, thrash it out, and see if you can get it working. I have actually used this technique on a number of theses already, and while I do concede that it is complex and heady, I can also attest that it is amazingly effective, giving great results and saving hours of manual work. I have sometimes just done a few Find Next operations until I am certain that it is finding what it needs to be finding, and is not finding the cross references, and then, when I feel bold enough, I hit Replace All (easy enough to do if you have a backup!).

Share