Microsoft Excel – Tips & Tricks

By StoneHouse Logic

07 Feb 2014

Excel is one of Microsoft’s most useful business tools, but it can be difficult to master.

StoneHouse Logic has compiled the most useful Excel tips to ensure you're getting the most from your software. You don't need to be a spreadsheet wiz to use these tips, you don't even have to understand Excel's formulas. But this series of hints can help you cut down on keystrokes and errors.

Utilising the ClipboardThe clipboard can be very useful when using Excel, as you often need to copy and paste between sheets or workbooks. The Clipboard can make this much easier as it enables you to handle multiple clipboard items at the same time. On the Home tab of the ribbon, click the arrow button to the bottom right of the Clipboard group to toggle it open or closed.

Update calculations manuallyExcel will perform calculations in real time by default, updating each value as the data changes. However, with more complex spreadsheets, automatic calculation can cause a significant lag. You can disable this feature by setting the “Calculations Option” to manual in the Formulas tab of the Ribbon. In this instance the calculation will refresh on the command of [F9].

Lock Copy FormattingFormatting can be time consuming, especially when importing information from several locations. If you need to apply the same formatting to a number of different cells, you can do so quickly by locking the Copy Formatting tool. Set the format you require first, and then select this cell, double-click the 'Format Painter' button in the Clipboard group of the Home tab, and now any cell that you click into will take on the formatting of the source cell. Simply double-click the 'Format Painter' again to turn the lock off.

Customise the Quick AccessThe Quick Access Toolbar appears in the top left-hand corner of the Excel window in Excel 2007 and 2010. You can customise what appears here, for a more efficient work-flow. Click the 'Office' button followed by 'Excel Options' and 'Customize' in Excel 2007. In Excel 2010, choose “File > Options > Quick Access Toolbar”. Select a tool that you want to add in the left-hand column and click 'Add'.

Move the Quick Access ToolbarWhen you're customising the Quick Access Toolbar, you can opt to have it displayed at the bottom of the ribbon instead of at the top of the window. This means you won't have to move the mouse pointer as far to access each tool in the Quick Access Toolbar. Tick the box marked 'Show Quick Access Toolbar below the Ribbon' in the 'Customise Quick Access Toolbar' dialog. You can opt to apply your customisations to the current file or to all Excel files.

Copy as a PictureWant to use a spreadsheet from excel in a word document? Do so by importing as an image as opposed to struggle re-formatting in the new document. You will not be able to edit the information, but it can be more aesthetically pleasing in its new home. To do so select the chart or cell range required and click the downward arrow below “Paste” and select “Copy as Picture”. You can now paste the image file wherever it is required.

Error CheckingFormula error checking is very useful and can help resolve otherwise unsolved inconsistencies within a spreadsheet. However, there are times when you might need to leave out a few cells in a particular region and Excel will flag the error. You can get rid of this annoyance by turning off the error check. Go to “Excel Options > Formulas” and clear any of the error-checking rules that you need to turn off.

Create Custom Auto-FillsAuto-Fill is designed to help complete columns or rows of sequential data. To utilise this tool, simply enter the first two values in the initial cells to establish a pattern, then select the cells and drag the fill handle to extend the data. In addition to this, you can add your own custom lists by choosing “File > Options” and select “New List” and click in the “List Entries” column. Type each entry in the list, and click 'Add' to finish.

Auto-Correct HacksYou can save time typing by entering shortcuts for commonly used terms in Autocorrect. In “Excel Options”, choose “Proofing > Autocorrect Options”. On the Autocorrect tab, enter the shortcut you want to type, followed by the phrase you want to automatically replace it with. Make sure your shortcut isn't a number or letter combination that you're likely to use in your spreadsheets as it will be replaced automatically each time you type it.

Create Keyboard ShortcutsPress the [Alt] key and you'll be able to see all of the keyboard shortcuts that are available for each visible tab on the ribbon. Press the key that's related to that tab to go to it, and to see a map of the keyboard shortcuts for that tab. This is very handy as you can use it to remind you of the available keyboard shortcuts whenever you want to use them, saving you time and effort and maximising your efficiency as you work.

Data ValidationOne guaranteed way to mess up a worksheet is to enter the wrong kind of data in a cell—for example, text instead of numbers, or a decimal instead of a whole number. Excel has a handy tool to help prevent entering the wrong type of data. The Data Validation feature. In a table, select the cells that should only contain one kind of data. On the Table Tools tab, click Data Validation and specify the kind of data that can go into the cells.Freezing the Top Row of an Excel WorksheetIt is helpful to freeze the top row of your Excel document in order to view the column headings without scrolling to the top of the document. 1. Click somewhere in your data. 2. From the View tab, select Freeze Panes. 3. If you want to freeze the panes above a certain cell of your document, click Freeze Panes. OR If you want to freeze the top row of the document, click Freeze Top Row.

Enjoyed this? Read more from StoneHouse Logic

Latest news

1

Roundhouse secures approval for new £15m office and industrial scheme in Preston Momentum

Roundhouse secures approval for new £15m office and industrial scheme in Preston

25 Jul 2025

2

Homebuilder's high-performing sales advisers clinch awards Ben Garland and Julia Lorente

Homebuilder's high-performing sales advisers clinch awards

24 Jul 2025

3

Work is set to start on Multiversity project Multiversity Blackpool

Work is set to start on Multiversity project

24 Jul 2025

4

BAE Systems welcomes MOU between Republic of Turkiye and UK for purchase of Typhoon aircraft Typhoon production Warton

BAE Systems welcomes MOU between Republic of Turkiye and UK for purchase of Typhoon aircraft

23 Jul 2025

5

Assystem to double UK jobs to deliver Sizewell C EDF Assystem

Assystem to double UK jobs to deliver Sizewell C

22 Jul 2025

Background image for hub sign up block

LBV Hub

Leverage Lancashire Business View platforms

Post your news
Post your events
Post your offers
Build your network
Improve your SEO
Gain coverage in the magazine
Sign-up
Events
Lancashire Built Environment Conference 2025
BEC 315 X 315 Px
Networking
25 Sep 2025

Lancashire Built Environment Conference 2025

Blackpool Football Club, Blackpool, FY1 6HX

08:30 - 13:00

CMI Level 5 Management and Leadership Course
UCLanAerialCampus.jpg.jpg
LBV Hub Seminars
21 Feb 2025 - 21 Feb 2026

CMI Level 5 Management and Leadership Course

Preston Campus, Preston , PR1 2HE

09:00 - 17:00

CMI Level 5 Project Management Course
UCLanAerialCampus.jpg.jpg
LBV Hub Seminars
21 Feb 2025 - 21 Feb 2026

CMI Level 5 Project Management Course

Preston Campus, Preston, PR1 2HE

08:00 - 17:00

Preston Tech Connection: Buzz Word Bingo. Challenging Todays's Buzzwords In Tech
July PTC banner long updated.png.png
LBV Hub Networking
29 Jul 2025 - 29 Jul 2025

Preston Tech Connection: Buzz Word Bingo. Challenging Todays's Buzzwords In Tech

Society1, Coworking Space, Preston, PR1 3LT

18:00 - 19:30

Preston Freelancer Meet-Up: July
Longer July Banner.jpg.jpg
LBV Hub Networking
31 Jul 2025 - 31 Jul 2025

Preston Freelancer Meet-Up: July

Society1, Coworking Space, Preston, PR1 3LT

10:00 - 11:30

Advantage: AI – A Workshop for Business Leaders – August 14th
Navy--Event Tile.png.png
LBV Hub Seminars
14 Aug 2025 - 14 Aug 2025

Advantage: AI – A Workshop for Business Leaders – August 14th

Door4 Office, Burnley Wharf, Burnley, BB11 1JG

09:00 - 12:00

The Business Network Central & East Lancashire
LBV Header (24).png.png
LBV Hub Networking
21 Aug 2025 - 21 Aug 2025

The Business Network Central & East Lancashire

Mytton Fold, Langho, BB6 8AB

11:30 - 14:15

Morecambe Bay Walk
Walk.jpg.jpg
LBV Hub Awards
30 Aug 2025 - 30 Aug 2025

Morecambe Bay Walk

Arnside, CARNFORTH, LA5

09:30 - 14:00

Longridge Soap Box Derby
Screenshot 2025-06-10 090035.png.png
LBV Hub Fundraisers
14 Sep 2025 - 14 Sep 2025

Longridge Soap Box Derby

Berry Lane, Longridge, PR3 3WH

10:00 - 16:30

Cumbria Business Expo 2025
https---cdn.evbuc.com-images-880461633-4862066883-1-original.20241022-110415.jpeg.jpg
LBV Hub Exhibitions
19 Sep 2025 - 19 Sep 2025

Cumbria Business Expo 2025

Carlisle Racecourse, Carlisle, CA2 4TS

09:00 - 15:00

Help to grow management course
Help to grow - barriers to growth.png.png
LBV Hub Seminars
26 Sep 2025 - 16 Dec 2025

Help to grow management course

Preston Campus , Preston , PR1 2HE

09:00 - 15:00

RISE - Lancashire's unique leadership programme for women
thumbnail_Emma Weston Illustration WENDY BOWERS RISE Illustrstion.jpg.jpg
LBV Hub Seminars
22 Oct 2025 - 18 Mar 2026

RISE - Lancashire's unique leadership programme for women

East Lancashire Chamber of Commerce, Clayton le Moors, BB5 5JR

09:30 - 15:30

Advertise with us

Reaching 50,000 members, our print, digital and event platforms offer a fantastic way to raise your business profile and help you grow.

Find out more LBV 123 Online Graphic
Subscribe now

Weekly news bulletin