Show the value of the last cell with a formula in an excel report

ITIL Reporting

It already happened that I needed to know the value of the last cell for a column or for a row and this formula will give me the good answer.

When I use the formula ?

To find the value of the last cell, either in a column or in a row.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=LOOKUP()

For a column:

=LOOKUP(2,1/(A:A<>""),A:A)

Explanation:

In case if I want the value of the column B by looking into the column A, I just need to change A:A (the second one) by B:B.

For a row:

=LOOKUP(2,1/(2:2<>""),2:2)

It works as the column, here it is looking into the row 2.

Be successfully certified ITIL 4 Managing Professional

Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
Be successfully certified ITIL 4 Strategic Leader

With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
Hide visual and change background color based on selection

Some small tricks to customize the background colour of a text box...
Stacked and clustered column chart or double stacked column chart

In excel, I use a lot the combination of clustered and stacked chart...

Count and check empty cells of filtered columns using a macro in an excel report

I use this macro to check if there are blank cells after I filtered...
Delete rows out of date using a macro in an excel report

In most of the reports, when I am doing the monthly one, I just need to keep all data that are in the month...
Find a specific value then insert a row and more things using a macro in an excel report

This VBA allows me to look for a specific value, it can be...
Execute a macro based on the day or time in an excel report

In some excel files, I am using a macro to tell it in which moment to do the report. For instance, if I am...
List unique values then combine in one single cell all data using a macro in an excel report

In one of my reports, I have to list from a column the unique values...

Copy/paste a range of values after finding the current date in an excel report

This script is to check and compare each cell of a specific column to...
Insert a row after finding a specific value in an excel report

This script allows to search a particular value, once find it, a new row will be inserted above or below...
Copy data between 2 sheets on top or bottom using an office script in an excel report

This online script allows me to copy the full data of a table to another...
Use a script to copy, cut, paste, replace and delete in an excel report (part 2)

This is the second part of my tutorial and it will be focused on...
Autofill from the last row using an office script in an excel report

This script will look for the last row then it will copy and paste the data to a number of rows below...

Calculate a weighted average for a SLA and a conversation time with a formula in an excel report

In one of my experiences, I had a tool that gave me the weighted average...
Search in different sheets then display the wanted data with a formula in an excel report

vlookup and hlookup are formulas that allow to search a data in another...
Find the good data by matching 3 different criterias with a formula in an excel report

It is a combination of “index” and “match” formulas, much better...
Sum and count sales with a formula in an excel report

Extracting data from salesforce or qlikview may not give the information I needed, it already happened...

It is important to know how long the service has been impacted by...