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,…
A Macro is a computer program that can duplicate actions you take in a spreadsheet. Macros perform key tasks faster. They can have a short-cut key combination to run them easily. Creating Excel and Word Macros is easier than most other methods of programming. You simply record the actions you took in the program. You…
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…
Here is a function that displays the formula in another cell: FORMULATEXT(reference). The argument, “reference”, is the cell address that contains the formula you want to display. This is most useful for documentation purposes. For example, when you want to print out a spreadsheet with its formulas. If you have spent much time on this…
We know about the MAX function in Excel, to get the biggest value in a range of values. But did you know that there is a function to give you the second biggest, third biggest, and so on? It’s the LARGE function. It can also give you the “first biggest”, replacing MAX. Syntax: LARGE(array, k)…
The adjective “Rank” means “having a foul or offensive smell.” (As in “the rank air of death after the battle”) and “very bad” (as in “the rank incompetence of the South African Post Office”). But Excel uses it as a noun, in the sense of “the position in an ordered list”. The RANK function returns…
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…
It is a sad fact of life that, as an engineer, you cannot escape accountancy. Finance gets in everywhere. Hence, you need to have a grasp, not only of engineering functions, but also of some financial functions –like depreciation. In this article, we will look at depreciation. You may be buying a new piece of…
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…