Numbers’ cell controls and formatting
are a great scam for easy entry
Perhaps it’s down to the twin personal
failings of fat fingers and a lack of co-ordination, but after carrying an iPad
around on a few trips over the past weeks, I’ve learned the hard way that
quickly editing the contents of cells in a Numbers table – often awkward enough
for me on a laptop – is a battle on iOS devices.
It also means that over the same time
period I’ve become a cheerleader for Number’s special cell controls: Checkbox,
Slider, Stepper or Pop-Up Menu for four separate reasons. The obvious one is
that they make editing cells much easier, particularly on an iOS device. Rather
than the usual hunt-and-peck approach to editing I usually employ on an iPhone,
cell controls let you change the content of a cell without having to type
anything in. instead, you can adjust its value with a slide of the finder or
tap of a pop-up menu.
Keep
it in check: Even though Numbers’ checkboxes themselves can’t be exported to
Excel, the underlying data remains intact, and formulas that refer to them
should be unaffected
Reason number two: converting a standard
number cell format to one of Numbers’ special cell controls is simpler than you
might think and it’s reversible.
On the Mac, you select the cells whose
format you want to convert and either choose the cell control from the
drop-down menu in the Format bar or open the Cells Inspector and choose the
cell format from the drop-down menu.
On an iOS device, you select the cells, tap
the paintbrush icon at the top of the screen and choose the format under the
Format tab. By tapping the blue button at the side of the format’s name in the
list, you can set options such as minimum and maximum values and increments.
A third reason for my love-in with special
cell controls is that while they don’t appear in other spreadsheet
applications, that doesn’t mean that the tables that employ them are
incompatible with Excel-using colleagues. The controls govern how the cell is
formatted, rather than its underlying data.
Type
cast: You can type into Stepper and Slider cell controls (the number will
automatically change to the nearest increment), but a Checkbox cell control can
only hold two values, checked or unchecked
So sitting underneath the Checkbox cell
control is a simple value: TRUE if it’s checked, or FALSE if not. If you export
a table containing these cell controls to Excel, the imported spreadsheet will
strip the appearance of the cell control, but the value currently in that cell
will be retained. And any formulas that rely on its contents should still work.
Cell controls are also a more secure way to
add information to a table. In most cases, when you create a cell controls, you
also set the constraints of their lowest and highest values or in the case of
the Pop-Up Menu, a set choice of options. This list of choices can be a pain to
build initially, but they save time later and as they restrict users to a set
choice, your spreadsheet data is kept more consistent.
Step
by step: For most cell controls, the Cell Inspector lets you set minimum values
for the cell’s contents, as well as specifying increments
The best reason of all for using cell
controls, though, is the flexibility they offer to your spreadsheets. Let’s say
you want to earn more money next financial year. If you’re a freelance
designer, you might have a particular target income that you’d like to aim for.
There will be two variables that affect how you can reach this target, how many
chargeable hours you can work, and the hourly rate you can get clients to pay.
Testing how much you need to charge, or how
hard you have to work to reach a target, is a classic piece of ‘what-if’
analysis that a Numbers table can help you calculate. All it needs is a cell
where you enter the number of hours you can work and one below with your hourly
rate.
The combined contents of these two cells
would produce a total income figure. Underneath would sit rows for fixed and
variable cost, which would be deducted from your income figure to produce a
profit total.
Conditional
love: By using conditional formatting, the background of a cell can change as
you adjust the slider
However, manually tweaking the two
variables by repeatedly entering a series of different figures would quickly
strain any enthusiasm for increasing your income. This is where cell formats
come into their own.
Instead of typing a new number into the
‘Number of Hours’ cell, you change the format of that cell to a Slider. In the
Cells Inspector in Numbers on the Mac, choose Slider as the cell format. Select
the minimum and maximum number of hours you’re prepared to work and set the
increment to one.
Now you can quickly adjust the number of
hours you work by moving the slider, and the total income and profit figures
will change accordingly.
Stay
in control: Numbers cell controls are easy to edit on the iPad, and you can
revert them to standard numerical or text content at any time
You can do something similar with the
hourly rate figure. This time, change the cell format to a Stepper, so you can
quickly increase or decrease numbers in specific increments. The result is a
table that lets you instantly visualizes how a change in your workload, or how
much you charge for it, can affect your bottom line. And if you create a chart
based on these figures, the updates as you move the slider.
Updating
game: One of the great benefits of using a slider cell control on conjunction
with a chart updates as you move the slider
You can also apply conditional formatting
so a cell’s text or background formatting changes according to the value of the
data. For example, if you set a maximum number of hours to work in the slider
cell control, you might like the cell to change color as you approached the
maximum number. That’s easy to do, and it can be done from the Cells Inspector.
If the maximum number of hours is 60, create a conditional format to change the
cell’s background color when the number of hours reaches 50. In the Cells Inspector,
click the Show Rules button and create a conditional format rule where when the
number is more than 50, the background fill of the cell turns red.
Star ratings
Star
ratings
Number’s cell formats are retained when you
move between iOS and Mac, expect for Star Ratings, a cell control exclusive to
iOS. A Star Rating has no options; it simply presents from one to a maximum of
five stars (if you convert a number above five to a Star Rating cell control,
it gives it five stars). To enter a rating, just tap the appropriate dot in the
cell. A handy way to use Star Ratings in iOS is in conjunction with a checkbox
control to create a to-do list. With each task allocated a row, assign a star
rating to each row for its importance. Add a checkbox control to the row so you
can tick it when the task is completed. You can then use sorting (tap the
column heading, then pick Sort from the floating menu bar to sort the column
descending) to organize the tasks, first in order of importance, and they by
completion.
