Excel Tips & Tricks

The tips below in orange are included in the free membership (remaining tips are part of our Premium membership).

Plus with our free membership you get access to over 6 hours of free Microsoft Office Online Training.

Try it for FREE

 

Category Topic Description
Charts Charts – Columns on Two Axes (0:03:57) This video shows you how to create a chart with two y-axes in Excel 2007, enabling clearer interpretation of the chart.
Charts Clustered Stacked Chart (0:03:26) This video shows you how to expand the stacked column chart to compare the data over monthly, annual or any other time period in Excel 2007.
Charts Data from Different Sheets (0:03:03) Do you find you have monthly data on separate sheets but you’d like to plot it on one chart? This video shows you how to chart data from different sheets in Excel 2007.
Charts Floating Column (0:03:43) Excel doesn’t provide a floating column chart, but this video shows you a work-around to create one.
Charts Adjust axis tick marks and labels (0:02:59) This video shows you how to customise the axis tick marks and labels on your charts.
Charts Stock Charts (0:02:49) Stock charts can be used to show fluctuations of stock prices, but they can also be used to illustrate fluctuations in other data like temperatures etc.
Comments Copy and Paste Text into Comment (0:01:07) This video shows you how you can move notes that have been entered directly into cells into Comments in Excel 2007. It’s not as simple as copy and paste, but it’s quicker than typing the whole note or comment out again.
Comments Change Background Colour (0:02:05) If you change the background colour of your Comments in Excel 2007 you will know you have to do this manually for each comment, which is time consuming. This video shows you how you can change the default colour settings in Windows so that each time you insert a comment the colour is the custom default of your choice.
Comments Change Comment Shape (0:02:15) Bring attention to your comments by changing their shape to something more attention grabbing. You can choose from over 156 different shapes, everything from an arrow to a star.
Comments Print Comments (0:02:06) Inserting comments into your Excel 2007 spreadsheets is useful when viewing the data on screen, but what if you want to print them? This video shows you the different options for printing your comments.
Conditional Formatting Hide Cells to Print (0:02:26) Often when working in Excel 2007 we will have supporting data required to perform calculations that we don’t necessarily want to print. This video shows you a clever trick to hide some cells when printing without having to hide the whole column or row.
Conditional Formatting Hide Duplicates (0:01:25) To enable sorting and filtering you must have a value in each cell, but this can make the data difficult to read once sorted as all names are then grouped together and it can be difficult to see where one group ends and the next starts. This video shows you how you can use conditional formatting in Excel 2007 to hide the duplicate values.
Conditional Formatting Hide Errors by changing font colour to match cell colour (0:01:22) Sometimes formulas return an error for example #DIV/0!, however it doesn’t necessarily mean it’s a problem you need to fix, but it looks ugly when printed. This video shows you how to use conditional formatting in Excel 2007 to easily hide these errors.
Conditional Formatting Referring function to range on another Sheet (0:02:19) In Excel 2007 Conditional Formatting can be applied based on data that is on different sheet to where you want the formatting to occur. This video shows you how to use a conditional format function to achieve this.
Conditional Formatting Highlight Duplicates (0:01:16) A quick and easy way to identify duplicates in your data is to use Conditional Formatting. This video shows you how to use the built in Highlight Duplicates conditional format in Excel 2007.
Conditional Formatting Shade Alternate Rows (0:02:02) If you want to shade alternate rows to make your data easier to read you can insert a table, but tables also impose constraints on how you enter data and formulas. This video shows you an easy flexible alternative using conditional formatting.
Conditional Formatting Shade Bands (0:01:46) This video teaches you a clever formula that will shade multiple rows in alternating bands using conditional formatting in Excel 2007 to make your data easier to read.
Conditional Formatting Filtered Bands (0:02:06) Using shading on alternate rows improves readability of data. This video shows you how to use conditional formatting in Excel 2007 to shade alternating rows in a filtered list.
Conditional Formatting Icon Sets (0:02:31) This video shows you how to use the Icon Sets Conditional Formatting in Excel 2007 to insert coloured shapes, like traffic lights beside your data, giving you at a glance information about performance.
Data Validation Use Lists on Another Sheet (0:03:54) Data Validation is a great way to control the data that is entered in forms. This video shows you how to use data validation to limit the choices to those in a specific list stored in another sheet in an Excel 2007 file.
Data Validation Restrict numbers outside a specified range (0:03:20) When setting up spreadsheets in Excel 2007 where other people will input data, for example budgeting, you can use Data Validation to restrict data entered. This video shows an example of using data validation to restrict the entry to numbers within a certain range.
Data Validation Prevent Duplicates (0:02:52) This video shows you how to use Data Validation in Excel 2007 to prevent duplicates being entered into a list.
Data Validation List from Other Workbook (0:03:15) This video shows you how to use a list in a different workbook in a Data Validation List in Excel 2007. There are some limitations to this, like users will have to have both workbooks open for it to work.
Data Validation Quickly Create Name Ranges from a Selection (0:01:37) If you use Range Names in Excel 2007 you’ll be amazed at how quickly you can create them from a table, which can then be used to speed up the creation of other formulas.
Data Validation Naming Dynamic Ranges (0:04:17) Named Ranges in Excel 2007 are a great time saver for use in validation lists and formulas to name two, but their limitation is that by default they don’t dynamically update when new items are added thus increasing the area of the range. This video shows you how to use a formula to automatically update a named range as new items are added.
Data Validation Use a function To Limit Total Amount (0:01:59) Use Data Validation in Excel 2007 to restrict the total amount entered into multiple cells. For example you might have a budget spreadsheet and you don’t want the total amount of the budget to exceed $5000. This video shows you how you can use data validation to restrict the total amount entered.
Data Validation Custom Criteria – No Leading or Trailing Spaces (0:01:48) Controlling how data is entered into your spreadsheets can be imperative to making sure formulas calculate correctly among other things. This video shows you how to prevent users adding spaces before or after the data entered with Data Validation in Excel 2007.
Data Validation Custom Criteria – Prohibit Weekend Dates (0:02:11) If you use Excel to plan events around dates, you can use data validation to limit the dates entered to only week days, only weekends, or any day or range of days. This video shows you how to set up data validation for only weekdays in Excel 2007.
Editing Transpose data (0:01:16) Sometimes you start a spreadsheet entering the data in rows but then realise that it would be better if it were in columns. This video shows you how to use the Transpose tool to easily switch the data from rows to columns, and vice versa in Excel 2007.
Editing Increase Numbers by Set Amount (0:01:49) Do you ever need to increase many figures by a set amount? You might take last year’s budget and increase it by 10% as a starting point for this year. This video shows you an easy way to increase amounts without the need for formulas in Excel 2007.
Editing Create a List of Dates for Weekdays (0:01:28) Excel 2007′s AutoFill with the left mouse button is great for the pre-defined lists or if you enter a custom list, but using AutoFill with the right mouse button reveals even more options. This video shows you how to use AutoFill with the right mouse button to create a list of dates that are week days only.
Editing Fill Data using custom fill series (0:03:08) The built in AutoFill lists in Excel 2007 are great time savers, but if you find you often type out a custom list of names, products, or any other list over and over, you can set up a custom list and use the AutoFill to complete it for you in an instant. This video steps you through the few simple steps for creating your own custom lists.
Editing Quickly Copy A Formula Down A Column (0:02:12) If you find yourself scrolling up and down your worksheet with your mouse when trying to paste a formula down multiple pages, then this shortcut will save you not just loads of time, but reduce your frustration when working with Excel 2007.
Editing Copy to Adjacent Cells – Options using Mouse (0:01:41) The Fill Handle in Excel has many more features other than just filling a series. This video shows you how to use it to copy data, copy formatting, or copy data without formatting in Excel 2007.
Editing Convert Text to Numbers (0:02:47) Sometimes when you import data from another source the numbers are formatted as text, which means you can’t use the sum formulas to total the amounts. This video shows you how to convert numbers formatted as text, to numbers.
Editing Force a carriage return when typing text in a cell instead of using Wrap Text (0:01:04) Do you often find yourself adding spaces in headings to force text to wrap to the next line. Then if you re-size the column you have to fiddle with the spaces again to get the formatting right. This video will show you how easy it is to force a carriage return in Excel 2007, so you no longer have to fiddle about with extra spaces to wrap your headings.
Editing Find text stored in cell comments (0:01:35) When you do a Find in Excel 2007 the default is to find text in cells, but what if you want to find text stored in comments? This video shows you how to search comment field text.
Editing Find text/values stored in formulas (0:01:28) When you do a Find in Excel the default is to find text in cells by looking in formulas. This means if you have a cell that has a formula that is =1+1, and you search for ’2′ it won’t find a 2, as it can only see the formula =1+1. This tip shows you how to use the Options in the Find dialog box to tell Excel to search for values whereby finding the ’2′.
Editing Data Entry Tips – Shortcut Keys (0:02:22) This videos shows a few shortcut keys that most people can use everyday to increase productivity in Excel 2007.
Editing Dragging Content for Cut and Copy (0:01:36) This is probably one of the best time-saving tips there is. Anyone who uses Excel 2007 will find this useful.
Editing TRIM function to get rid of blank spaces at the end of data in a cell (0:01:26) If you sometimes import data from another source it may bring with it additional spaces at the beginning or end of the text. This video shows you how you can quickly get rid of these unwanted spaces with the TRIM formula in Excel 2007.
Filter AutoFilter – Filter Text in Long String (0:03:13) The Custom Option filter allows you to filter cells that contain specific text, but if your cell has more than 255 characters it won’t be found. You’ll also notice that cells that contain more than 255 characters simply don’t appear in the filter dropdown list. This video shows you a workaround, by placing the specific text you want to search in a cell on the worksheet and use a formula to check for the text.
Filter Advanced Filter – Unique Items/Remove Duplicates (0:01:55) This video shows you how to use Excel 2007′s Advanced Filter to extract a list of unique items from a list. For example you might want to compile a list of clients from a list that has the client name appearing more than once.
Filter Advanced Filter – AND vs OR (0:04:47) You can use Excel’s Advanced Filters to meet more than one criteria. For example you might have a list of employees that you want to filter as part of your bonus calculation process. First you want to filter by employees that have been with your company for more than 3 years and the next filter is for those in a management position. This video shows you how to use the AND, OR formulas in Advanced Filters in Excel 2007.
Filter AutoFilter – Sum a Filtered List (0:02:05) If you insert a SUM to total a filtered list it will SUM all the data in the column, not just the items that are visible. This video shows you how to sum the visible numbers in a filtered list in Excel 2007.
Filter Advanced Filter Criteria – Create Two or More Sets of Conditions (0:01:13) This video shows how easily you can use Excel 2007′s Advanced Filter using the OR statement to filter data that meets two or more sets of conditions.
Filter Advanced Filter – Using Wildcards in Criteria (0:04:14) This video shows you how you can use Wildcards in a filter to search for a specific word within a text sting, use the ? Wildcard to find text that shares some characters, but not others, and ignore wildcards that are used as part of the text.
Formatting Apply different formats within one cell (0:01:02) Typically when you format text you apply the format to all of the text in the cell, but what if you just want to make some of the text in a single cell red, and leave the rest black. This video shows you how you can apply different formatting to text within a cell.
Formatting Display only the last four digits of a credit card number (0:01:39) If you enter credit card numbers into Excel 2007, for data privacy you might prefer to only display the last four digits and keep the full card number in a hidden column. This video shows you a simple formula to create the ****-****-****-1234 format you often see when credit card numbers are printed on receipts etc.
Formatting Format Painter – Tips and Tricks (0:01:54) Format Painter is a new tool in Excel 2007 and on first impression it seems fairly limited. This video shows you one of the lesser known features of the Format Painter which is sure to save you time.
Formatting Convert Trailing Minus Signs (0:01:35) Sometimes when you import data into Excel 2007 from another source negative numbers have the minus sign to the right, known as a trailing minus sign. This video shows you how to quickly convert these numbers so they are formatted correctly with the minus sign to the left.
Formatting Format Sheet Tab Colours (0:00:47) Excel allows you to shade cells to draw attention, or code areas of the spreadsheet. It also lets you colour code the sheet tabs, allowing you to categorise sheets and make navigation quicker.
Formatting Create a new default worksheet (0:04:24) If you find yourself automatically reformatting each new Excel 2007 file you open, with your preferred font, font size, font colours, column and row widths etc. then this video will save you loads of time.
Formatting Set multiple non-contiguous print areas (0:01:12) A new feature in Excel 2007 is to set multiple non-contiguous print areas. This is great if you have reference data interspersed between data you want to print. Watch this video to see how easy it is to set multiple print areas in the one worksheet.
Formatting Display numbers as phone numbers (0:01:58) When entering phone numbers into your spreadsheets do you manually enter spaces to display the phone number so it’s easy to read? This video shows you how you can format the cell in Excel 2007 to display the number with spaces automatically, saving you time when entering data.
Functions/ Formulas Combine Text from Multiple Cells (0:01:08) This video shows you how you can combine text from two or more cells with a simple formula.
Functions/ Formulas Count Blank Cells – COUNTBLANK (0:01:01) One of the lesser knows formulas in Excel 2007 is to be able to count the number of blank cells in a range. This is useful when you’re working with a large document and you need to ensure all, or a specific number of cells are completed.
Functions/ Formulas COUNTIF – Match criteria exactly (0:01:19) This video shows you how to use the COUNTIF formula in Excel 2007 to count the number of times your criteria is matched exactly in a specific range.
Functions/ Formulas COUNTIF – Match criteria in a string (0:01:39) This video shows you how you can use the asterisk wildcards with the COUNTIF formula in Excel 2007 to count the number of times any variation of your criteria appears in a specific range.
Functions/ Formulas COUNTIF – Match criteria using operator (0:01:16) This video shows you how you can use the COUNTIF formula in Excel 2007 to count the number of instances that meet a specific criteria using a formula e.g. Count how many sales were > $500.
Functions/ Formulas Count Cells using – COUNT & COUNTA (0:01:31) This video explores the uses for the COUNT and COUNTA formulas in Excel 2007 to count different types of data in a range.
Functions/ Formulas Count rows in a Filtered List (0:02:28) One of the limitations of the COUNT formulas is they don’t work with filtered lists. This video shows you how to use the SUBTOTAL formula in Excel 2007 to count visible cells in a filtered list.
Functions/ Formulas Combine cells with text and a number (0:01:48) This video shows you how you can combine cells to join a text string with a number from a cell in Excel 2007. This can save you time by automatically joining information from your spreadsheet with text to form comments or notes.
Functions/ Formulas Combine cells with text and a formatted date (0:01:44) When you combine text with a date, you can format it by using the Text formula. Without formatting, the date will appear as a number.
Functions/ Formulas Find the number of workdays between two dates excluding holidays (0:02:29) If you do project planning or other analysis based on dates then the NETWORKDAYS formula is likely to save considerable time. This video shows you how to apply the formula in Excel 2007 to calculate the number of whole working days between a start date and an end date and exclude weekends and any dates identified as holidays.
Functions/ Formulas Find the date after a number of workdays WORKDAY (0:03:03) This videos shows you how to use the WORKDAY formula to calculate the number of working days before or after the starting date. It also excludes weekends, and can exclude dates identified as holidays. It’s useful for calculating delivery dates, number of days worked, and invoice due dates to name a few.
Functions/ Formulas ROUND, ROUNDUP, ROUNDDOWN (0:04:01) If you work with numbers that you want set to a fixed number of decimal places you might find that using the formatting to display, say to 2 decimal places, sometimes still results in a rounding error in any totals. This is because the underlying number is still stored un-rounded. This video shows you how to use the ROUND, ROUNDUP and ROUNDDOWN formulas in Excel 2007 to round the underlying value of the number which will eliminate rounding errors in totals.
Functions/ Formulas LARGE function to sum the x largest numbers (0:03:07) Sometimes you only want to add up say the top 3 numbers in a range that isn’t sorted. This video shows you how to use the LARGE formula in Excel 2007 to sum the top ‘x’ numbers in an un-sorted range.
Functions/ Formulas Sum visible cells only using SUBTOTAL (0:02:59) If you’ve hidden rows in your Excel 2007 spreadsheet and you don’t want them to form part of the total, this video shows you how to use the SUBTOTAL formula to only sum visible rows.
Functions/ Formulas Make functions visible on Worksheet (0:00:45) Sometimes you’ve got so many formulas on your spreadsheet that it’s difficult to follow them. One option is to use the Trace Precedents or Trace Dependents, but another option is to have Excel display the formulas in the cell rather than the result.
Functions/ Formulas Combine IFERROR and VLOOKUP (0:03:09) A new feature in Excel 2007 is the IFERROR formula. You can combine this with a VLOOKUP to extend the search capabilities of the VLOOKUP to search multiple tables of data one after the other until the result is found, or if it isn’t found in any table, return a message to say ‘Not Found’ or any message you want.
Functions/ Formulas Sum a range of cells using OFFSET (0:02:10) How many times do you enter a total at the bottom of a column, only to insert a new row just above the SUM formula and then have to update the SUM range to incorporate the new row. This video shows you how to use the OFFSET formula in Excel 2007 so that when you insert a new row the SUM range automatically updates, without the need for a spare row at the bottom of the list.
Functions/ Formulas Grand Total a range of cells using AutoSum button (0:00:50) This video shows you how to quickly add totals for both the rows and columns of a table in just two steps.
Functions/ Formulas Excel Time Calculations (0:05:17) This video tackles how to use times in formulas in Excel 2007 including, adding, subtracting, sum a range of times, multiply time by a $ value to get a cost for the time that could be used to calculate employee’s pay.
Functions/ Formulas Date Calculations (0:02:20) This video shows you how to use dates in Excel 2007 to calculate the date a number of years from now, and the date a number of days, weeks and years from a specific date. On the surface this sounds like a straight forward calculation, but there’s a specific formula you need to use for this to work.
Functions/ Formulas Display File Path In A Cell & File Name In A Cell (0:03:01) This video shows you how to display the Excel 2007 sheet name in a cell in the spreadsheet, which will dynamically update in the event of a change. This is useful if you want to print this information somewhere other than the header or footer of the spreadsheet.
Go To Fill Blank Cells (0:02:17) If you sometimes use data from another program you may find that in order to sort, filter or pivot the data you need to fill in any blanks left as a result of the import. Sure you can copy and paste, but this is quite laborious. This video shows you how to use Go To Special to quickly select the blank cells and then enter a simple formula to fill the blanks with the result in the cell above.
Go To Delete Blank Cells (0:01:11) If you find that when you import data from another program into Excel 2007 it ends up peppered with blank rows. This videos shows you how you can use the Go To Special command to quickly select all blank rows and delete them in just a few clicks of your mouse.
Go To Copy & Paste Visible Cells Only (0:01:32) Have you ever spent time hiding rows or columns only to then want to copy and paste only what is visible? This video shows you how to use the Go To Special command in Excel 2007 to select only visible cells so you can then copy and paste them.
Pivot Tables Group dates by month (0:01:40) Pivot tables are powerful, but if your data has date information in it, then being able to group dates into weeks, months, quarters etc. can make all the difference as to whether your Pivot Table is really useful. This video shows you how to use the Grouping feature, and how to trouble shoot when it gives you a common error.
Pivot Tables Preserve formatting when refreshing (0:01:26) Have you ever spent time formatting your pivot table, only to have to refresh it or change its layout and lose the formatting. This video shows you how to preserve your formatting when you refresh or change the layout.
Pivot Tables Turn off GetPivotData function (0:02:23) When you reference a formula to a cell in a Pivot Table Excel 2007 automatically generates a GETPIVOTDATA formula instead. If you find this annoying, watch this video to learn how to disable it.
Pivot Tables Rename Data Fields (0:00:59) When you generate a pivot table the data fields are given headings e.g. Sum of Sales, but you might prefer it just to read ‘Sales’. This video shows you how you can rename the data fields to something more elegant.
Pivot Tables Multiple Consolidation Ranges (0:02:40) Typically when you insert a Pivot Table you will be referring to one range of data, however it is possible to bring multiple ranges into one Pivot Table. This video shows you how you can use the Pivot Table Wizard in Excel 2007 to create a pivot table from more than one range of data.
Pivot Tables Remove the ‘Row Labels’ and ‘Values’ labels (0:01:19) When you insert a Pivot Table there are two labels at the top; Row Labels and Values. Whilst these serve a purpose, they’re unsightly if you want to print the Pivot Table and use it as a report. This video shows you how to turn these labels on and off.
Pivot Tables Apply Label Filters (0:01:13) New to Excel 2007 is the ability to filter fields in the row area of Pivot Tables. This video shows the simple steps for filtering on row labels.
Pivot Tables Insert a Calculated Field (0:01:30) This video shows you how you can insert a calculated field in a Pivot Table in Excel 2007. For example you might have the units sold and the unit selling price in your pivot table, but you don’t have the Total Sale value. You can insert a formula field in a new column that forms part of the pivot table. With this method any updates to the pivot table automatically include this calculated field, which saves you having to constantly update a manual formula external to the pivot table when the length of the table changes.
Pivot Tables Manually Hide or Show Subtotals (0:01:09) By default Excel 2007 Pivot Tables insert subtotals. This video shows you how you can turn them off and prevent them from reappearing when refreshed or layout changes.
Pivot Tables Show Top Items Only (0:01:02) Excel 2007 Pivot Tables allow you to filter the results in much the same way as standard Filters. This video shows you how you can filter to show the top 10 results in your pivot table.
Tables Create a calculated column in a Table (0:01:54) Excel 2007 Tables allow you to quickly set up a formatted table that dynamically updates as the table grows and changes. This video shows you how you can add a calculated column to a table.
Tables Convert a table to a range of data (0:01:11) Although Excel 2007 Tables have some great features, they also have limitations. Watch this video to learn how to turn your table into a regular range of data while retaining the table style formatting.
Excel Tips & Tricks E-Book






Get Weekly Excel Tips FREE

We respect your email privacy