Get More from Your Excel Spreadsheets
Excel users…this is the one of the best NEED TO KNOW guide I’ve seen in a long time, thanks to brilliant Bit.
Don’t like that annoying habit Excel has of trying to guess the right format for a cell you’re updating? Here’s how to turn it off, plus other handy tips.
When it comes to organising data, Excel is tremendously capable. However, the wealth of tools available can be daunting, and many powerful features are easily overlooked.
Here’s a selection of 15 of our favourite one-click tools and simple features that can help you whip a worksheet into shape in moments. These tips all work in Excel 2007 and later (except where stated), and many are available in earlier versions, too.
Formats and fills
1 Format Painter
The Format Painter tool lets you copy the formatting of a cell onto one or more other cells, leaving the contents unchanged. Place the cursor in your source cell, click Format Painter (under the Home tab), then click in another cell to apply the formatting. You can drag to apply the format to a range of cells at once.
The Format Painter automatically disengages when you release the mouse button: if you double-click its icon, however, it will stay active until you click again to disable it, or press Escape – handy for formatting non-contiguous ranges.
2 Clear Formats
When you type a number into an empty cell, Excel tries to guess the right format: for example, enter “17/2” or “25%” and your cell will automatically switch to Date or Percentage format. This isn’t always what you want, and it’s annoying if you later change the cell contents, since Excel won’t thereafter update the applied format to suit new data. You can remove all formatting from any cell (or range) via the dropdown labelled Clear, which you’ll find to the right of the Home tab, within the Editing group.
Other options under this dropdown let you clear the contents of cells, leaving the formatting untouched, or remove comments or hyperlinks. If you don’t want Excel to automatically format your cells in the first place, you can precede a cell’s contents with an apostrophe to make Excel interpret it as text.
3 Quick cell format changes
You can set the format of a cell directly from a dropdown menu, or with the click of a button
You can specify a format for any cell or range via the Format Cells window (click the pop-out icon in the Number group under the Home tab). It’s quicker to use the dropdown menu within that icon group, though – you’ll see the default setting is General.
The buttons below this dropdown can save time too. The one that looks like a banknote and coins sets a cell to Accounting format (click the dropdown to choose a currency), while the percentage sign does what you’d expect. The comma icon punctuates large numbers to make them easier to read, so 1000000 becomes 1,000,000 (this doesn’t affect your ability to use the number in calculations). Lastly, the decimal icons make the selected cells show more or fewer decimal places, making it easier to deal with calculated values that present an unneeded degree of precision.
4 Autofill
Most people know that you can quickly fill a column or row with copies of the same number or text by entering it once, then dragging the marker at the bottom-right corner of that cell to cover the range you want to fill. This works with numerical series, too: if you have two adjacent cells containing “1” and “2”, you can select them both and drag onwards in the same direction to automatically count as high as you like.
This also works with days of the week, calendar dates and other types of data Excel recognises. You can even use numbers that follow simple patterns: drag to extend the series 12, 17, 22 and Excel will correctly fill in the next cells with 27, 32 and so forth.
5 Text to columns
If you have to process a mass of imported text, you can split it across multiple columns by clicking Text To Columns in the Data Tools group under the Data tab: the dialog box that opens will allow you to split cell contents according to either character counts or separator characters.
In Excel 2013, you can also extract specific elements from a column of data with a new feature called Flash Fill. To illustrate how it works, let’s imagine you have a column of computer memory capacities such as “2GB”, “1GB”, “4GB” and so on. If you manually enter “2” in an empty cell next to the 2GB value, then drag down to autofill the cells below, Excel will by default fill all the cells with the same value. But if you then click on the Smart Tag that appears at the bottom corner of your new range and select Flash Fill, Excel will use values extracted from the neighbouring column instead, using your originally selected cell as a model: you’ll see that the cell values change to 2, 1, 4 and so on.
Managing your data
6 Remove duplicates
If you’ve imported a large amount of data from another program, you may have any number of duplicate entries. You can get rid of these manually by sorting and deleting cells, but Excel can do it for you: simply select the table, then go to the Data tab and click Remove Duplicates. If you’ve selected a two-dimensional range, you can specify which columns must all match for a row to qualify as a duplicate.
7 Name your cells and ranges
In a large spreadsheet, you’re likely to be working with numerous ranges of data, and making sense of your cell references can quickly become confusing. You can make life easier by assigning names to important cells and ranges so that, in place of opaque formulae such as “=SUM(A19:J31)+L16”, you can use readily readable descriptions such as “=SUM(Payments)+Bonus”. You’ll find the Define Name tool under the Formulas tab. Once you’ve created a name, you’ll see it come up as an autocomplete suggestion whenever you type in a formula, although old cell references in existing formulae won’t automatically update.
8 Trace precedents and dependents
The Trace Precedents feature shows you exactly which cells are referred to by a formula
No matter how neatly you organise your spreadsheet, sooner or later you’re likely to come across a confusing formula that seems to draw data from a dozen unexpected places. You can get a visual indication of exactly which cells it’s referring to by selecting the cell, then opening the Formulas tab and selecting Trace Precedents (under Formula Auditing). A handy set of arrows will show exactly which values are used. Similarly, a click on the Trace Dependents button will reveal, at a glance, exactly which cells in a worksheet contain references to the selected cell. Note that if you have a range of cells selected when you click, only the cell that’s actually active (that is, the unshaded one from which you started dragging) will be traced.
9 Show formulas
In a large spreadsheet you can lose track of which cells contain raw data and which contain calculated values. A click on Show Formulas – again under the Formulas tab – will expand all cells containing calculations to show their formulae instead of the results. Click again to return to the regular view. In Excel 2013, you can also create a Conditional Formatting rule to highlight cells where “=ISFORMULA(A1)” is true (replace A1 with the reference of the top-left cell of your selected range).
Another easy way to find formulae and other types of cell is with the Go To command, which is located on the Home tab under Editing (you can also access it by pressing Ctrl-G or F5). The Go To window shows you a list of named references you can jump to; click “Special…” and you can use it to select cells of many types, including precedents, formulae, blanks and comments.
10 Paste Special
The Paste Special dialog lets you turn formulae into values, carry out multiple mathematical operations, and switch rows and columns
You probably know that you can use the smart tag that appears when you hit “paste” to specify formatting options for pasted data. You may not know about the more powerful “Paste Special” dialog: you can find it under the Paste dropdown on the Home tab, or you can open it directly by pressing Alt-Ctrl-V. The Paste Special window makes it easy to paste only the values of the formulae you’ve copied, or to copy only aspects of the source’s visual style.
You can also use this feature to perform calculations on several cells at once. Select Add and, rather than replacing the destination cells, the source cells’ values will be added to them. As a final trick, try ticking Transpose in the Paste Special window: you’ll see that your pasted cells are flipped around, so columns become rows and rows become columns – something that’s otherwise a pain to achieve.
Views and visuals
11 Freeze panes
When you’re working with large tables of data, row and column headers tend to get pushed off the edges of the window, making it easy to get lost. The answer is the Freeze Panes feature, which you’ll find on the View tab. With one click, you can freeze the top row or the first column of your worksheet, so it will remain visible as you scroll around.
If you want to freeze both at once, simply position your cursor at the top left of your table data (so it’s below and to the right of your column and row headers) and select the general-purpose Freeze Panes option from the dropdown. Now you can scroll around your data to your heart’s content, while your headers remain visible at all times.
12 Conditional formatting
Conditional formatting can provide an at-a-glance indication of the highs and lows of a set of figures. To see it in action, select the range of cells you’re interested in, then click the Conditional Formatting dropdown from the Styles group on the Home tab. The top two options let you highlight cells according to a particular rule – such as those greater than a specific value – or choose a Top/Bottom rule to automatically mark the highest or lowest values.
The most powerful conditional formatting options automatically add graphical tags or coloured backgrounds to the cells in your range, to give a clear visual indication of high and low values. Hover your mouse over the various options under Data Bars, Colour Gradients and Icon Sets to see how your data will look with each set of formatting applied.
13 Sparklines (Excel 2010/2013)
For Office 2013 users, Sparklines provide a handy visual indicator of trends and trajectories across multiple datasets
Conditional formatting is great for comparing a single set of figures, but what if you want an at-a-glance overview of multiple trends? One option is to insert a graph, but a neater solution is to use Sparklines – groups of miniature graphs that each occupy a single cell. To add Sparklines to a worksheet, select a two-dimensional table of data, then go to the Insert tab and select Line, Column or Win/Loss from the Sparklines group. You’ll be prompted to specify where you want the Sparklines to go drag along an available column or row and click OK and you’ll see Sparklines appear.
You can configure the appearance of a Sparkline group using the Sparkline Tools | Design tab that appears when you click on a Sparkline cell. By default, each Sparkline automatically scales to use the full height and width of the cell; under the Axis dropdown you’ll find options to use the same minimum and maximum values for all Sparklines, so you can compare values directly across an entire Sparkline group.
14 Collapse grouped cells
Many spreadsheets include large tables of figures with summary rows at the top or bottom. Sometimes you just want to work with these summaries, and temporarily ignore the data from which they derive. This can be done easily with Excel’s Outline tools. Select the relevant rows or columns, then go to the Outline group on the Data tab and click the Group icon. You’ll see a new column open at the left or top of the window, bracketing your range together with a minus sign icon. Click this icon and the rows and columns will be temporarily collapsed, so you can get a clean overview of your spreadsheet without having to move or hide your data.
15 Print Area
Excel’s print dialog tries to fit your spreadsheet to the desired number of pages, but it’s fiddly. It’s much easier to use the Page Break Preview, which you’ll find under the View tab. This view overlays thick dashed lines and page numbers onto your worksheet, so you can see exactly what falls where – and if you’re not happy with the layout, you can drag the page boundaries with the mouse to specify precisely what should go on which page.
You can also specify that only a certain area of the worksheet should be printed – useful if, for example, you’ve made notes off to the sides of a table of data. You can do this by dragging the outer page boundaries in Page Break Preview to exclude unwanted cells. Alternatively, while in Page Break Preview, you can select an area of your worksheet, right-click and choose Set Print Area from the contextual menu. You can also do this using the Print Area dropdown from the Page Layout tab.
If you wanna get really into it you’ll love >>> THIS ARTICLE <<< too.
Happy Days 🙂
