It’s been two years since Microsoft launched Excel for iPad, and while it may have seemed like a slightly foolhardy move at the time, Apple’s tablets have proved themselves to be more than capable of handling complex spreadsheet manipulation. Despite lacking a full keyboard and mouse interface, Excel for iPad is very nearly the equal of its desktop counterpart.
Here are our top 11 tips to get the best our of Excel on your Apple tablet.
1. Work on more cells at once with the iPad Pro
Apple’s mammoth 12.9in tablet has been touted for its use as an enterprise prodcutivity tool, and so it’s no surprise that Microsoft has brought out a special version of its app to take advantage of the iPad Pro’s larger screen.
In addition to a larger screen surface to work across, the addition of the Apple Pencil and Smart Keyboard means that users can have much more delicate control of their spreadsheets, without worrying about deleting something vital with one ham-fisted swipe.
2. Duplicate before doing anything complex
Excel for iPad can be too powerful for its own good. If you’re working on a spreadsheet created on a PC/Mac that has tons of formulae, pivot tables or other complex formatting, it’s possible to accidentally clear the contents of a critical cell or drag a table out of place as you’re swiping around the document.
If you’re planning to work on a complex spreadsheet on the iPad, it’s best to click the Save menu button (the little page icon with the two arrows) and click Duplicate before you get going. Then if you make any mistakes, you’ve got the original to fall back on, without having to rely on the Restore function.
4. Moving the contents of rows or columns
Sometimes you might find you want to move the contents of an entire row or column to a new location. Trying to manually select all the data in a long row or column might be awkward on an iPad, but there’s a much easier way to do it.
5. Easy Autofit
If you find the content of cells is not fully displayed because the column width or row depth isn’t sufficient, there’s a quick way to solve this: double tap on the column/row’s number or letter and the contents will Autofit so that you can read them fully.
If the content of only one cell in a particular row or column is running out of its box, it might be neater to simply wrap the content of that cell, rather than make the whole column/row bigger. To do so, tap on the cell to select it, and then tap again after a short pause (i.e. don’t double tap) to bring up the Edit menu, and then select Wrap.
6. Combining selected data
Let’s say, in the table shown in the screenshot below, that you wanted to create a separate row beneath the main table showing only the building costs (i.e. Premises + Mortgage Interest Payments) for each quarter. There’s a simple way of doing it without writing the formula by hand.
7. Look, but don’t touch
As with all the Office for iPad apps, there are features of the desktop version not ported across the iPad. Conditional formatting is one, as is Sparklines, mini graphs which fit within the width of a cell to give you a snapshot of trends within a particular row or column.
However, conditional formatting and Sparklines will be displayed on the iPad if you open a spreadsheet containing them, and they will even update accordingly if you change the contents of their cells.
8. Go online to work together
Although Word for iPad has a rather awkward means of letting two people edit the same document simultaneously, this isn’t possible with Excel. If somebody else has the spreadsheet open when you attempt to edit it, you’ll be restricted to read-only mode.
9. Beware of chart limitations
If you’re planning to add charts to your spreadsheets, it may be a job better left to the PC. Although on the face of it, the Chart options in Excel for iPad are quite sophisticated there are many different chart types, layouts and colours on offer there’s little flexibility once the chart is on the page.
10. Freeze Panes
With the limited screen space afforded by an iPad, you might want to “freeze” information on the screen so that it always remains within view. In our sample spreadsheet in the screengrab below, for example, you might want to freeze the dates in the top row so that when you scroll down to different categories of cost, you’ll always see the relevant date at the top of the screen.
11. Find recently used functions
Most people tend to use a small fraction of the functions available in Excel. Instead of ploughing through the Formulas tab every time you want to enter a formula, you can click on the fx button next to the Formula Bar and open a list of recently used functions. The full list of functions is available underneath, each with an explanation of what it does, which you can open by clicking on the little green i’ bubble next to the function’s name.
