• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Archives for Formulas

Display Negative Time in Excel

Excel does not display negative time. So if you are working with negative times, there is a problem. Fortunately, there are ways to work around this issue.

This tutorial will show two ways to display a negative time in Excel.

For this example, we have the fastest times for the last six London Marathons for the men’s category. And we want to see the time difference compared to the previous year.

If the time is faster than the previous year, the result will be a negative time. A slower time would return a positive time.

[Read more…] about Display Negative Time in Excel

Lookup Multiple Values in Excel

In this tutorial, we will use Excel formulas to lookup multiple values and combine the results into a single cell, or row. We will also order the returned values in two different ways.

The formulas used in this tutorial are only available in Excel 365 and Excel Online. They take advantage of the array engine of modern Excel. This video show you how to lookup multiple values in all Excel versions.

Watch the video below, or read on. This tutorial covers three examples of a lookup formula to return multiple values.

Lots of information, so let’s dive into the action.

[Read more…] about Lookup Multiple Values in Excel

How to Make Table Column References Absolute

This tutorial covers how to make table column references absolute. It will cover both table column and cell references.

Download the Excel workbook to follow along.

[Read more…] about How to Make Table Column References Absolute

Create a Picture Lookup in Excel

Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup in Excel to return a picture dependent upon the contents of a cell.

This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.

In this blog post, we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.

Spreadsheet Setup

[Read more…] about Create a Picture Lookup in Excel

Count the Number of Words in a Cell

Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.

To analyse this data they wanted to count how many words were in each cell containing keyword searches. Excel provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1

This formula subtracts a version of the cell without spaces between each word from a version still with spaces. This finds out how many spaces there are. There will be one less space than there are words so the resulting answer has a 1 added to it to find how many words there are.

The LEN function is used to find how many text characters are in the cell.
The SUBSTITUTE function is used to replace the spaces between each word with nothing making one long word.

The TRIM function is used to remove any spaces at the beginning or end of the cell content. This cleanses the cell of any erroneous spaces put in by user error, or error on import.

Count the number of words in a cell
[Read more…] about Count the Number of Words in a Cell

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Interim pages omitted …
  • Go to page 18
  • Go to Next Page »

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·