Insert a Picture into a Comment

Excel was created for the use of calculating and analysing huge amounts of data. Because of this it has never been great for storing large amounts of text or pictures. Inserting images into a cell would make the spreadsheet even larger and more awkward in its daily use.

Comments are fantastic for leaving notes for other users, but they can also be used to store pictures. These pictures can then be made visible when required rather than permanently displayed on screen within a cell.

Continue reading

Excel Badminton League Table

This Excel badminton league table can be used to track team or player positions real time as results are entered into the spreadsheet.

Download the Excel badminton league table.

This spreadsheet uses a few excellent Excel techniques for its functionality. These include;

To use the spreadsheet. Simply add the team or player names to the table on the Teams sheet and fixtures sheet (Find and Replace helps here), and then enter the results into the fixtures sheet.

Using SUMPRODUCT Function to Count Records using Multiple Conditions

There are many ways to count records using multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables or by simply filtering the data. However neither of them are as flexible as the SUMPRODUCT function.

The SUMPRODUCT function is used to multiply the values of multiple arrays and then sum the totals. However it is capable of so much more amazing feats. It truly is one of those hidden gems waiting to be discovered.

Continue reading

Macro in Excel – Loop Through all Files in a Folder using VBA

One of the main reasons to create a macro in Excel is to make a repetitive task easier and quicker. Such a task may be to loop through all files in a folder and perform an action on each one.

The code below will display the folder picker dialog box to allow the user to specify the folder (directory) that they want to use. It will then loop through all the files within that folder. It will open the workbook, perform an action and then close it saving the changes made.

Comments have been used to explain different areas of the code.

Continue reading

Compare Two Lists in Excel to Highlight Matched Items

In this post we will look at how you can compare two lists in Excel to highlight matched or unmatched items. We will first identify the items that appear in both lists, and then look at how to highlight the items that appear in the first list but are missing from the second list.

The Match function will be used to compare both lists and will return if a record is found or is missing. The Match function returns the relative position of an item in a list. If it cannot find the item it will return the #N/A error message (learn more about the Match function).

member_list

Continue reading

Learn How to Use Excel – 3 Special Excel Functions that will Amaze You

 

Excel contains 400+ functions and this list is constantly growing. There are functions to perform almost any task from financial, date and time, statistical etc.

This learn how to use Excel post looks at 3 little known special Excel functions that will take your skills to another level and make you the envy of your work colleagues.

Continue reading

Excel Gantt Chart Template for Tracking Project Tasks

A Gantt chart is used to plan and track the progress of a project. Although Excel does not contain a Gantt chart feature (maybe one day), its tabular structure and wealth of tools provide us with the means of creating one.

A Gantt chart can be created in many ways to match your requirements.

Using the Excel Gantt Chart Template

This Excel Gantt chart template uses fixed scheduling on its tasks and provides a timescale of 1 full year from the project start date. To use the template;

  1. Enter the project start date in cell E1.
  2. Enter the ID and name for the tasks of your project.
  3. Enter the task’s estimate start dates and durations.
  4. Enter the % completion to update the chart with the progress of the project.

Continue reading

Essential Conditional Formatting Tricks

Conditional Formatting is an amazing feature of Excel. It is sure to create a spark of interest and questions during training. People see the potential in their spreadsheets and how easy their team could visualise their data and create engaging forms and reports.

This tutorial looks at the two most requested Conditional Formatting tricks asked by Conditional Formatting enthusiasts.

Continue reading

Excel Training – Lookup your Data with the DGET Function

A key skill in Excel training is to be able to lookup and retrieve data from a range of records. The most popular way of achieving this is to use the Vlookup function. The Vlookup function on Excel is awesome and easy to use, but it has its drawbacks.

Cue the DGET function. A very powerful Excel function that will retrieve data from a record without the limitations of Vlookup. Advantages of using the DGET function include:

  • It can retrieve data from a column to the left of the column you searched within.
  • It can lookup data based on multiple conditions.
  • It can handle both AND and OR logic.

Continue reading