I use Excel for a ton of stuff, both personal and work. As an example, on the work side I use an Excel workbook to track our apps’ hidden prefs—which are set using a long ugly Terminal command1Something like this: defaults write com.manytricks.Moom “PMWindowFadingDuration” -float 0.
These hidden prefs can be used to invoke features we’re testing, or to revert a behavior we’ve changed at some point, etc. For example, Moom has hidden prefs to use the full screen grid without clicking in the big box and to disable the fade in/out of the keyboard controller.
We don’t publish all of these, as we’re not necessarily ready for them to be put to use by everyone (otherwise, they’d be visible prefs). But there are cases when a user has a specific need for a setting, or when troubleshooting, that these hidden prefs can be very useful. As such, I often have to send someone a defaults write command.
Read on to see how I use Excel’s formatting features—plus the ever-valuable Keyboard Maestro—to disguise some of this workbook’s formula results, yet still easily copy them for sending to a user.
2019 update: I’ve uploaded new files (in one zip archive this time) with a few changes and fixes. These files are also set up as “master” files: The idea is you duplicate one, rename it for the current year, then use it. When the next year rolls around, repeat the process. This way, you don’t have to use the macro-enabled version to delete data at each new year. Download the new files.
About two years ago, I created a basic-but-functional run tracking workbook (created in Excel). It worked well, and helped me through my 2,016 mile year in 2016. I didn’t run nearly as much in 2017 (on purpose), but 2018 is upon us, and I’m going to up my mileage this year—probably not to 2,018, though!
In preparing this year’s version of the workbook, I addressed a few things that bugged me about the first one: It was ugly, changing years was difficult, and it was ugly. It was also really ugly. Did I mention it was ugly? Anyway, here’s what I’ve changed with the new version:
- Years are now easily handled; just input the year you wish to track, and the workbook does the rest, including leap years.
- All run data can be deleted with one button click—and yes, there’s a confirmation first. (Requires macro version of workbook.)
- The pace calculator is no longer a separate worksheet; it’s integrated into the Overall worksheet.
- It’s not nearly as ugly as it was before—layout is improved, gridlines are gone, tables are cleaned up, etc.
As noted, there are two versions of the workbook—one contains a macro that can erase the run data from each monthly worksheet, the other does not contain that macro. This is something you’ll only do once a year, but it’s much easier with the macro version.
I recently explained how to use the keyboard in Excel’s Paste Special dialog box, and this is a great timesaver on its own. But I use Paste Special a lot, especially with Formats, Formulas, and Values, so I made those three even easier to use via the keyboard…
Each one has its own direct keyboard shortcut, courtesy of Keyboard Maestro. Here’s how I set it up; these instructions should work (with some changes, of course) for any app that can script keystrokes.
First, I created these macros in an Excel group, so they’re only active when Excel is frontmost (no need to create global hot keys that you only use in one program). The actual macros are pretty trivial:
- Send Command-Control-V to bring up the Paste Special dialog
- Pause just long enough for the dialog to appear onscreen
- Send the chosen shortcut key—T, F, or V in my vase
- Send the Return key to execute the action
Then I just assigned each one to the same key used within the dialog, but with Command and Option to make it usable from anywhere within Excel.
When I’m working in Excel, I spend a lot of time in the Paste Special dialog box—pasting formulas, pasting all but formats, pasting only formats, etc. You can call up the dialog with a keyboard shortcut (⌃⌘V), but it then looks like you’re stuck using the mouse, because there aren’t any keyboard shortcuts for the various actions. But really, there are…
(Note: This applies to the current version of Excel, i.e. the one in Office 365. Based on the comments, it apparently also works in Excel 2011 if you add the Command key.)
On Excel for Windows, one character in each option has an underline, indicating that option’s keyboard shortcut. The good news is that these same shortcuts work on the Mac, even though they’re not shown. (There is one apparent oversight: The O key should select Operation: None, but it doesn’t seem to work on the Mac.)
Here are all the shortcuts, graphically:
Press the highlighted key, and that action will be selected; press Return to execute the chosen command, and you can use the Paste Special dialog without ever touching the mouse. (Note that the Paste Link action executes immediately when chosen, so it’s a one-key operation.)
Because graphics are horrid for web searching, the text version of each shortcut, in alphabetical order, is shown below.
This morning, while working on a customer request, I had to create a list of words by randomly choosing words from two lists, and then mashing them together. This isn’t something that I’ve ever done before, and I’m not sure how relevant it might be for others, but I’m documenting it here just in case someone is searching for such a solution.
Here’s how my little test spreadsheet looked when I was done with it:
I didn’t realize it at the time, but this could be a “modern paint color” name generator…”Yes, I’d like two gallons of the Pickle-Purple, please.” Anyway, the COMBINED column contains the final result, with the FOODS and COLORS columns showing randomly-selected entries from the two lists. Each time you recalculate the sheet, all the selections will change.
The key bit is the formula to grab a random entry from the list; here’s what that looks like in cell C12 (“Apple”):
It’s just a basic VLOOKUP that uses RANDBETWEEN to grab a random row from the lookup table. Not rocket science, but nothing I’d tried before. (For this to work, your table entries need row numbers, obviously.) The COMBINED column is just a simple text formula, i.e. =C12&"-"&F12 to combine the two random values.
I also wasn’t aware of the RANDBETWEEN function—it returns a whole number between the values you specify. That is so much easier than using RAND and then having to multiply and round off, etc.
Feel free to download the workbook if you’d like to take a look.
I’ve created a much nicer run tracking workbook
. Please use that version, as this one is out of date and is no longer maintained. I’m leaving it here because some of the “how to” bits are still applicable to the new version (and it’s linked from that post), but I’ve removed the download link.
To help with my 2,016 miles in 2016 running project, I created an Excel workbook to track my progress. A couple people have asked for the workbook, so here it is…with some caveats and instructions.
First off, this was written for Excel 365, though it should work fine in recent versions of Excel. There is no Numbers version, there is no Google Sheets version…this is it.
Start by downloading the worksheet and opening it in Excel.
The first thing you’ll notice is that this is a really ugly workbook. The only thing I spent any time “prettifying” at all was the actual vs. goal chart, as that’s the thing I tended to look at most often. The second thing you’ll notice, depending on when you open the workbook, is that it appears nothing is working. The formulas will not work properly until January 1st, 2017.
Earlier, I sent out this hopefully-humorous tweet about the difficulty involved in clicking one’s passwords into the new Apple TV password input screen:
Presenting LIMNOPHILE, a 10-character yet easy-to-type Apple TV password.
The chart is just an Excel file, with absolutely no logic—I just colored the squares and counted to fill in the data. But then I got this reply…
So I thought “Why not?,” and created an actual spreadsheet that will “click check” any all-letter password you feed it. Here’s what it looks like in action:
Just replace RIDICULOUSLYLONGWORD with whatever you like, and see how it’ll “click out” on your Apple TV. Obviously, this tool is totally tongue-in-cheek!. Any password built with this tool will be weak as heck. It’s just for fun, so don’t take it seriously.
Feel free to share and modify, but I’d appreciate a credit back if you do so.
Download Apple TV Password Tester (44KB)
Please note that this is an Excel file, and it relies on conditional formatting, so it may not work in Numbers.
In case you missed it, Microsoft just released Office 2016 for Mac. Well, released if you’re an Office 365 customer. I am, so I downloaded the release version today. I’d been playing with the betas, and one thing bugged me: the installer wouldn’t let you pick an install folder.
Sadly, the same holds true for the release version; after installation, my Applications folder was the mess as shown in the image at right. Ugh.
My Applications folder resides on my boot SSD, and I like to keep it tiny and tidy. Tiny in the sense that only my most-used apps reside here; others are on my RAID. Tidy in the sense that I don’t like looking at long lists of apps that all start with the same word, e.g. Microsoft. So things like Office go into a folder, helping at least the tidy side.
Eric Schwiebert of Microsoft tweeted an explanation for this user-unfriendly behavior:
While I understand the rationale, I don’t agree with it. Office isn’t yet in the App Store, and even if it were, that’s not where I got it from. So why are you affecting my options for a version that neither exists nor that I even have? In any event, I wanted Office 2016 in a folder, so I set out to find a way to do that.
I was looking for a way to print a large image across multiple pages, so I could make my own do-it-yourself poster-size printout. By way of background, I wanted to print a huge virtual fire, to cover a piece of insulation we put in front of a drafty fireplace in the winter. (We don’t like to burn wood, so the fireplace goes unused, but staring at a piece of shiny foam insulation all winter isn’t all that interesting.)
Conceptually, this seemed pretty easy: find a huge image, open it in some app that handles images, and print. What I found is that doesn’t work, at least not in the apps I had at hand (Acorn and Preview). After some web searching, I stumbled across an odd but effective solution: use Excel.
Open a new blank Excel workbook, then select Insert > Photo > Picture From File, and select your massive image. Now when you hit Print, you’ll see the output spans multiple pages. I used Page Setup to select a borderless US Letter size, and printed out 16 pages of a roaring fire.
After some cut-and-tape operation, the drafty fireplace’s insulation became more visually appealing:
Note that this was a “proof of concept” operation, so I printed in draft mode (hence the vertical striping on the printout) and wasn’t overly careful about lining up the pages. I had originally planned to print the final version on glossy photo paper, but instead opted to buy a 36×48 poster-size printout from an online vendor. (I haven’t yet received the print, but when I do, I’ll post about its quality. Until then, though, I don’t want to link to the vendor, as I don’t yet know what I’ve bought.)
I knew Excel could do a lot, but I never thought to try it for printing huge images across multiple pages.
In my article on the (possible) vanishing monthly discount for AT&T Family Share Plan users, I included a table with some cost estimates for a given phone and service.
Given the popularity of that article, I’ve done a bit of work to clean up my spreadsheet to make it usable by anyone. You choose which phone, and the size of your shared data plan, and the calculator spits out the results (click the image at right for a large version). As you can see, I’ve added a column for the Next 18 plan, too.
Download the calculators here. The zip archive includes versions for Excel and Numbers. Usage is pretty simple: Click the two red-text cells to set the desired iPhone and your data plan size, and that’s that.
To summarize what I saw in building the worksheet:
- If you’re on a shared plan with under 10GB of data, going contract will save you a bit of money over two years. You’ll give up flexibility over buying or Next, though.
- If you’re on a 10GB or more shared data plan, then going Next or buying outright is definitely the way to go. More flexibility, and you save money—assuming you do not upgrade at 12 or 18 months under Next by just sending in the old phone!
Note: I do not vouch for the accuracy of this thing, beyond its role as a “what if” tool. I took the values from AT&T and Apple sites, but those figures could change. Feel free to modify as you wish; it’s a quick and dirty spreadsheet with minimal formatting.