Microsoft Excel Help and Tips

Almost everyone knows a little bit about Excel, but there is a wealth of features hidden away that can save you copious time, money and stress. Here are a few of what we have found to be the most useful Excel tricks. You can find downloadable spreadsheets with examples from our Excel tips videos at the end of the Microsoft Excel Videos section.

Top 5 Excel Features
Software-Matters have a YouTube channel which includes short Excel tutorial videos that get straight to what you need to know to use the best bits of Excel. We are adding videos every few weeks, so if you go to the channel using the link above and then click the red Subscribe button, YouTube will automatically let you know when we publish a new video.
The first set of videos is our 'Top 5 Excel features'

Microsoft Excel Shortcut Keys

These are some useful shortcut keys that you can use in Excel to reduce the need to switch back and forth between the keyboard and mouse and so save you time. In one of our Smatterings Time Savers sections we wrote about shortcut keys that you can use in Microsoft Word. Many of these work for Excel and other programs. Most of the basic key commands were covered there and you can follow the link to see them.

Go to Word shortcuts that also work in Excel

Ctrl + C: Copy
Ctrl + X: Cut
Ctrl + V: Paste
Ctrl + B: Bold Text
Ctrl + I: Italic text
Ctrl + U: Underline text
Ctrl + S: Save
Ctrl + Z: Undo
Ctrl + Y: Repeat last action / redo. If you have just undone something it will replace it otherwise it will repeat the last action you did. 
Ctrl + F: Find
Ctrl + +: Insert row/column/cell
Ctrl + -: Delete row/column/cell
Ctrl + H: Replace
Ctrl + A: Select All
F2: Edit a formula or content of a cell.
F4: Repeat command.
F5: Go to a particular cell or named range.
F10: Select the active ribbon. You can then use the arrow keys to navigate through the ribbons.
Alt: Pressing alt will overlay the ribbon with numbers and letter which you can use to navigate the different tabs and sub menus.
Alt + =: Sum of cells above.
Alt Gr: This stands for Alternative Graphic and most keyboards have an Alt Gr button that can be used to give access to a series of symbols and accented letters without having to go through a menu to get them.
Alt GR + $: €
Ctrl + Shift + Home: Extends the selection of cells to the beginning of the worksheet.
Page Down: Moves one screen down in a worksheet.
Page Up: Moves one screen up in a worksheet.
Alt + Page Down: Moves one screen to the right in a worksheet.
Alt + Page Up: Moves one screen to the left in a worksheet.
Ctrl + Page Down: Moves to the next sheet in a workbook.
Ctrl + Page Up: Moves to the previous sheet in a workbook.
Ctrl + Shift + Page Down: Selects the current and next sheet in a workbook.
Ctrl + Shift + Page Up: Selects the current and previous sheet in a workbook.
Spacebar: In a dialog box, performs the action for the selected button, or selects or clears a check box.
Ctrl + Spacebar: Selects an entire column in a worksheet.
Shift + Spacebar: Selects an entire row in a worksheet.
Ctrl + Shift + Spacebar: Selects the entire worksheet.

Protecting Against Errors with IF functions (Excel 2007 and later)
Let's say you want column C to be column A * column B in your worksheet. If whatever populates columns A and B occasionally causes them to become errors (e.g. #VALUE) then the related column C entry will be forced to become an error as well. But it is likely that you want to do something with your set of numbers in column C, and having an error among it may ruin any further functions.

In this case you could change your formula for column C to be like this: =IFERROR(A1*B1,0). This replaces any error that occurs with the value 0.

In Excel 2003 and earlier, you would need to write =IF(ISERROR(A1*B1),0,A1*B1) instead, as IFERROR was not a library function in those versions.

Making the same edit to multiple sheets at once
If you want to make an edit to more than worksheet at the same time, you can use 'Group Mode'. To do so, simply hold down Ctrl and click on all the sheets you wish to simultaneously edit. You should see '[Group]' appear in the title bar of Excel. The sheet you were on when you started doing this will be part of your selection.

Now any change you make to your current sheet will be done to all other selected sheets. For example, if you wrote 'Hello World!' in cell A1 on your current sheet, it would also be there in that cell on all other selected sheets, overwriting anything that was there before.

To exit Group Mode, click normally on a single sheet that isn't in the current selection. Be careful, as forgetting to exit Group Mode can lead to you overwriting data on other sheets accidentally!

Microsoft Excel Videos

In this short series we'll be bringing you our top 5 Excel features, both for ease of use and power.

To play a video click on the play symbol in the centre of the video window. You can watch the videos on this page but if you would prefer to view a video full screen you will need to watch the video on the Software-Matters YouTube channel. Below each video there is a link that will take you to the video on YouTube. To watch a video full screen on YouTube click on the full screen symbol in the lower right corner of the video .
NB: You will need sound when you watch the video.

No.1 - VLOOKUP
VLOOKUP and HLOOKUP can turn Excel from a spreadsheet into a database, allowing your sheets to retrieve information relevant to what you are entering from data tables. Watch the video to see how they work and some examples of where they can be very useful.

Download VLOOKUP example spreadsheet as used in the video.

No.2 - SUMIF
When you want to do a conditional sum, that is a sum where you only add entries for which a certain criteria is true, you need to use the SUMIF function. Combined with its sister function SUMIFS, which lets you have many conditions, you can extract very specific information from a dataset of any size. In this video we'll give you a quick tutorial on how to use it and why it's worthy of the number 2 place in our top 5 best excel features list.

Download SUMIF example spreadsheet as used in the video.

No.3 - Tables and Filters
Excel makes managing big blocks of data easy through two key features: Tables and Filters. In this video we'll demonstrate how to set both up and show some examples of how to use them, hopefully convincing you that they deserve this number 3 spot on our top 5 list.

Download Tables and Filters example spreadsheet as used in the video.

No.4 - Sparklines
Number 4 on our list is Sparklines, a new feature added in Excel 2010 used for spotting and tracking trends in data without resorting to large / complicated charts.

Download Sparklines example spreadsheet as used in the video.

No.5 - Left, Right, Mid and Trim
Left, Right, Mid and Trim are basic functions for cutting up and reserving data.

Download Left Right Mid Trim example spreadsheet as used in the video.

Download Files as used in other Videos

VBA

Setting Error Traps
If some VBA code encounters an error, the user will be presented with the option to debug the code. If you do not wish to have your end users tinkering in your code then you can set it to have some other behaviour upon encountering an error. We do this with an 'error trap'.

Public Sub CopyTable()
            On Error GoTo Error_Trap
            Range("A1:C3").Value = Range("A4:C7).Value
            
            
            Exit_Error_Trap:
               Exit Sub
            
            
            Error_Trap:
               MsgBox Err.Description
               Resume Exit_Error_Trap
            
            
            End Sub
            

We have told the code to go to the section named 'Error_Trap' when errors occur (such as our sheet being Protected when trying to write values into cells), and have created this section by writing this title followed by a colon. This is usually placed at the end of the code. However, since this trap is part of the code, we need something before it to stop the code reaching it when there is not an error. Thus we have also inserted the 'Exit_Error_Trap:' section which simply ends the process as expected.

What our error trap actually does is give us a message box describing the error, to which the user can only press okay, then uses the 'Resume' command to tell it where to go next in the event of an error. Here we have just told it to go to our little exit routine; however there may be cases where you want some special behaviour to trigger upon an error, or to just carry on ignoring the error (in which case we would write 'Resume Next' in our error trap instead).

Protecting all your sheets at once
It can be a pain to manually set the protection on a large number of sheets. With this small piece of code, you can make a macro that protects or unprotects all the sheets in a workbook instantly.

            For Each ws In Worksheets ' protects all the worksheets
                ws.Protect
            Next ws
            

Simplifying with Public Subs and Functions
If when coding in VBA you find yourself having lines that repeat themselves in multiple places in your database, you can make your code both shorter and easier to edit by instead making a Public Sub (if the lines carry out a process) or Public Function (if the lines return something) with those lines as its content. Not only will this save you the trouble of writing out those lines every time you need them, but it will make it easier to edit what they do as you need only change them in one location, rather than having to go around looking for each place you used them and making an edit.

If your set of lines occasionally needs to output something different, you might consider adding an extra argument to your Sub or Function that causes it to behave differently. This allows you to have lots of very similar processes all in one place for ease of editing.

            Public Function StringMaker(Mode As Integer) As String
            ' A function to return a certain string, based on the interger input
            If Mode = 1 Then
            	StringMaker = "Hello World!"
            ElseIf Mode = 2 Then
            	StringMaker = "Good Day Planet Earth!"
            ElseIf Mode = 3 Then
            	StringMaker = "Saluations, children of Terra!"
            End If
            End Function
            

You would use this function using a line of code like this: MyMessage=StringMaker(2).

Using Worksheet Functions in VBA
The worksheet functions that you use in formulae can also be used in VBA. In the below example we show how to use a VLOOKUP to get a value in code from a table in our spreadsheet that we have assigned the name 'MyTable', and has values A,B,C in column 1, and 1,2,3 in column 2.

            Dim n as Integer
            n=Application.WorksheetFunction.Vlookup("B",Range("MyTable"),2,False)
            ' We use the Range object to call the table into VBA from the spreadsheet.
            MsgBox "The number is: " & n
            

VBA References
A VBA Reference points the program the in direction of the libraries that store its built-in methods and properties for all the predefined objects e.g. Strings. If multiple users are sharing a database that uses VBA References to Excel/Word/Outlook, it is best for all users to have the same version of Office installed (e.g. all 2010). If they have different versions of Office, they will need their own copies of the Front end, with the References set appropriate to their version of Office.

We also have a Microsoft Access help and tips page.