Functions

Got a spreadsheet problem that you just can’t figure out?

Wondering whether you’re going all round the houses to do something that could be solved with a few clicks?

Don’t fear… Traci’s Tips are right here!

Here are a few Functions that you may find useful.

Tip 1

Use ‘Custom Filter’ to select all items that ‘Begin With’ or ‘Contain’ particular character(s)

Tip 2

Use ‘Conditional Formatting’ to change the format of cells, depending on the data they contain i.e. Less than 0, etc

Tip 3

Use ‘Sort’ to change the order that data appears in i.e. Alphabetical Order, Date Order etc.

Tip 4

Use ‘Find’ function to locate specific data (limit search by highlighting specific row or column to search)

Tip 5

‘Use ‘Find & Replace’ function to amend data quickly i.e. Change 2010 to 2011

Tip 6

Use ‘Named Ranges’ for ranges of data that are commonly used, this reduces the need to update ranges several times

Tip 7

Use ‘Data Validation’ to create a ‘Pick-List’ in a cell, to avoid needing to type repetitive information into a cell

Tip 8

Use ‘Freeze Panes’ to keep key rows and columns visible as you scroll down or across the sheet

Tip 9

Use ‘Paste Special’ to copy just the Formatting, leaving the values in the cells as they were.

Tip 10

Use ‘Paste Special’ to copy just the Values, leaving the formatting in the cells as they were.

Tip 11

Use ‘Paste Special’ to copy just Comments, leaving the values & formatting in the cells as they were.

Tip 12

Right click mouse, insert comment – Include a note on a cell (Red triangle will appear in the top right corner of the cell)

Tip 13

Click on a Row Number to select the entire row, right click mouse, select ‘Delete’ to delete the entire row

Tip 14

Click on a Column Letter to select the entire column, right click mouse, select ‘Delete’ to delete the entire column

Tip 15

Use ‘Merge Cells’ in the ‘Format’ menu to merge one cell across various Columns / Rows. Ideal for headings.

Tip 16

Use ‘Paste Special’, ‘Transpose’ to copy a vertical list into a horizontal list

Tip 17

‘To convert positive numbers to negative, type -1 into an empty cell, Copy, highlight data to convert, Paste Special, Multiply

Tip 18

If a sheet has been filtered by more than one column, go to ‘Sort & Filter’ menu and select ‘Clear’ to unapply the filters.

Tip 19

In the ‘View’ tab, there is a ‘tick box’ for ‘Gridlines’ if you want to remove these from view

Tip 20

Use ‘Custom Format’s’ to apply dates formatted as you require, using the abbreviations: dd mm yy.

Tip 21

Right click on Tab, Move or Copy – choose the ‘Workbook’, ‘Position in the Workbook’, tick ‘Copy’ to create a copy

Tip 22

When creating a ‘Named Range’, spaces and characters cannot be used. You can use _ for spaces.

Tip 23

To ‘Password’ protect a file, go to ‘Save As’, select ‘Tools’, ‘General Options’ and then enter the password.

Tip 24

Group data in Pivot Tables to consolidate i.e. Group dates into Months & Years

Tip 25

Use ‘/’ when entering dates so that Excel will recognise this as a ‘Date’

Tip 26

Use ‘Data Validation’ to restrict entry into a cell

Tip 27

Use ‘Data Validation’ to provide a message to Users as to what should be entered

Tip 28

Use ‘Data Validation’ to provide a ‘warning’ or ‘error’ message if incorrect data is entered

Tip 29

Create a ‘Custom Format’ to make dates include the ‘day’ i.e. ddd dd mm yy – Sun 01 Jan 12

Tip 30

Create a ‘Named Range’ by highlighting the Range, and typing the ‘Name’ directly into the ‘Name Box’

Tip 31

Highlight a range of cells and Excel will automatically show ‘Sum’, ‘Count’ & ‘Average’ in bottom right toolbar (as appropriate)

Tip 32

Hide formula’s by ticking the ‘Hidden’ box in the ‘Protection’ tab in ‘Format Cells’, then protect sheet.

Tip 33

Create a Pivot Table to summarise data, and link this to a Pivot Chart to display visually (and automatically!)

Please don’t hesitate to get in touch if you have any spreadsheet related questions, and don’t forget you can access loads of my other free templates

Traci x