In Alice in Wonderland by Lewis Carroll, at the trial of the Knave of Hearts, the White Rabbit asks the King how to read the evidence. “Begin at the beginning, the King said, very gravely, and go on till you come to the end: then stop.” It would be easy if all computer programs did…
At this point, you might be wondering, “What if I get stuck?” Fear not, help is at hand! Microsoft offers extensive online documentation of the VB language. To be fair and warn you, it’s often not that useful. You might find more assistance if you Google for your topic. There is a lot of programming…
In an earlier post, we recorded two Excel macros in Visual Basic, and ran them. Then, in the next post, we viewed the code in the Excel Visual Basic Editor. Code you have recorded will usually run. However, you may want to change it for all sorts of reasons. To tidy it up, make it…
In our previous post, we looked at the steps to record and run a macro. Practice makes perfect, or at least helps us improve, so let’s do an exercise. We will record a simple macro to format the current cell in bold, italics, and single-underscored. Then it will move one cell to the right, ready…
The easiest way to create a macro is: The instructions below are for Excel. Word is similar, but has its own quirks (and no “Absolute or Relative”). If you want to look at programming basics first, see this previous post. 1. Recording a Macro Absolute or Relative What does that mean? When you record Absolute,…
We’ve seen in our earlier post on Range Naming how you can give a cell or range of cells a range name, and then use that in formulas. However, there is a powerful yet little-known feature (little-known to me until recently, at least!) that you can create a range name that instead uses a formula….
For this Project Planner, you will learn how to build an Excel Gantt Chart using Data Validation Message Help and Cell Shading using Conditional Formatting based on clever Range Name Formulas. Free Gantt Chart included.
This post follows on from the discussion of time formats in the post: Working with Times in Excel. If you would like a copy of the sample file Timesheet.xlsx used in this post, click here and send the resulting email. Otherwise, you can build up the file as discussed below. 1. Setting up the Time…
We have various ways of solving simultaneous linear equations. In this post, we will look at a couple of other methods, and then how we can do it with matrices in Excel. We first consider two variables, and then more. Let’s be clear before we start: What are linear equations? They are of the form…
In the previous post, we introduced array formulas. These are formulas you enter into a range of cells using Shift+Ctrl+Enter. That block, or array, works as a unit. You can extend the array easily or, with some difficulty, reduce it. We looked at some array formulas: TRANSPOSE, COLUMN, SUM, and LINEST. If you didn’t read…
One of the most obscure things about Excel has to be array formulas. Weird as snake’s suspenders, they are. We are familiar with the idea that a single formula in Excel occupies a single cell. If you want that formula to be in many cells, you simply copy it to those cells. It will adjust…
Do you ever need to convert units, increase or decrease a range of data, or change its sign? Then this technique should work for you. Excel’s Copy > Paste Special > Add / Subtract / Multiply / Divide lets you transform data in place. You don’t need an intermediate column or row. Say, for example,…