Excel tutorial: master 15 key features
Posted on 19 Apr 2013 at 08:58
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
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.
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 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.
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.
You should have been there
It seems that Osborne's Depression which we're now suffering was based on a duff Excel spreadsheet.
Krugman: The Excel Depression. http://3y5.r2.ly
You should have been there, Darien.
By c_webb31 on 19 Apr 2013
For more details about purchasing this feature and/or images for editorial usage, please contact Jasmine Samra on firstname.lastname@example.org
- Google I/O live stream and blog: how to watch 2014 Google I/O keynote speech live
- Google testing its own domain registration service
- Adobe announces first hardware: Adobe Ink and Slide
- Vote now in the PC Pro Excellence Awards 2014!
- What’s new in OS X 10.10? Apple Yosemite’s new features
- Samsung Z Tizen phone helps loosen ties with Android
- Microsoft rumoured to launch smartwatch this summer
- LG G3 launched: LG takes the wraps off smartphone that offers “more with less effort”
- LG G3 launch live video stream and blog: as it happened
- Apple fixes iMessage lock-in for Android switchers
- How Google Glass ruined my lunch hour
- Smartphone battery packs: can a USB power pack beat the festival battery blues?
- Windows Easy Transfer – not so "easy" in Windows 8.1
- Formula 1: what a difference virtualisation makes
- Office of the future: comfy chairs and tablets everywhere
- I went to Glastonbury and the only thing that got high was my smartphone
- Meet the robots helping teach children
- PaperLater: would you pay to print the internet?
- Amazon vs Kobo: how much to make the ebook switch?
- Phishing emails: how I nearly got caught out
- How to add in-app purchasing to an iPhone, Android or Windows app
- Remote-control ransomware: TeamViewer and software hardball
- Why laptops with serial ports matter to the Internet of Things
- Make your mobile battery last longer
- Small steps into handling Big Data
- Nexus 5: does it really run stock Android?
- How to get broadband to a garden office
- How to write your company's IT security policy
- Raspberry Pi and Wolfram: a must-have for every child
- Could you get by with Office Web Apps?