Archive by month August

Convert Text to Columns

With Excel there is always more than one way of completing a task.  Previously I have written a post about splitting date and time from being combined in one …

What are Variables in Excel VBA

Using Variables in Excel VBA A Variable is used to store information temporarily so that it can be used for execution within the Procedure, Module or the …

How many Cells in Worksheet in Excel 2003

Worksheets in Excel 2003 can contain 65536 rows and 256 columns.  So to work out the number of cells per worksheet, simply multiply 65536 x 256 = …

VBA Decimal Places in String Variable

In this post I hope to show you a work around for reducing a string to two decimal places. I was working on a project just recently for a reporting system for …

How many Cells in a Worksheet in Excel 2003?

In Excel 2003, there are 16,777,216 cells in a worksheet. There are 256 columns and 65,536 rows. Then doing the maths: 256 x 65536 = 16,777,216 cells. In Excel …

How many Columns are there in Excel 2003?

There are 256 columns available in each worksheets in Excel 2003. However, in Excel 2007 I believe there is no

How many Rows in Excel 2003?

Excel 2003 has a maximum of 65536 rows per worksheet. But did you know that Excel 2007 offers an almost unlimited number of rows per worksheet, over one …

How to Show Lowest Number in Range of Cells

A quick way to get and show the lowest number in a range of cells, instead of using the  =MIN function by hand, is to use the auto min button in excel.   All …

Convert a Row into a Column

Converting a row to a column, or column to a row, is very easy in Excel using the transpose feature. Often you find yourself presented with data in rows, but …

Count the Number of Cells in a Range based on Criteria

In a previous post I talked about how to use the COUNT function in Excel to count the number of cells containing data.  Well, you can also combine COUNT with …

Show Highest Number in a Range of Cells

A quick way to get and show the highest number in a range of cells, instead of using the  =MAX function, is to use the auto max button in excel.  All you do …

Get Average Number from Range of Cells in Excel

A quick way to get an average from a range of cells, instead of using =AVERAGE function, is to use the auto average button in excel.  All you do is click in …

Using AutoSum Feature

An even quicker way to sum a range of cells, instead of using =SUM function, is to use the auto sum button in excel.  All you do is click in the cell directly …

Quickly Select all Data in a Column

If you have a lot of data that you need to select in a column, and can’t do so by selecting the whole column, a very quick way is using a key board short …

Use Excel VBA to Get Username of Person Logged in to Computer

Using Excel VBA you can get the username of the person logged into the computer and print it to a cell. I have used this before to monitor which users on a …

Get Computer Name using Excel VBA

Did you know that using Excel VBA you can get the computer name and print it to a cell. I have used this before to monitor which computers on a network where …

SUMIF Function – Summing Cells using a Condition

In a previous post I talked about how to SUM cells using Excel’s SUM function.  This is a great way to sum cells quickly, but did you also know you can …

Summing Cells in Range using SUM Function

Before Excel 2007 the maximum number of rows per worksheet was 65000!  And if you had numbers in just a thousand of those, and needed to a total of all the …

Counting Cells in a Range using Count Function

When using Excel, it’s often beneficial to know how many cells that contain data there are in a range.  A real easy to work this out is using the COUNT …

Insert Current Date and Time in a Cell

Have you ever needed to show the current date and time combined into one cell?  Simple solution is to click into the cell you want it displayed and type …

Always Show the Current Date in an Excel Cell

Sometimes it is required to always have the current date displayed on a workskeet whenever you open it.  A very quick and easy method is to type =TODAY() in a …

Nested IF Function in Excel

Hopefully you have come to this post after reading my previous article about using IF functions in Excel.  This is a next step to using IF in Excel.  …

Writing IF Statements in Excel

The IF Statement in Excel is a good tool, but is quite often little understood by Excel users.  The IF statement tests a value and then says whether it is …

Add Command Button to an Excel Worksheet

This was a request from a reader who wanted to know how to add and use a command button that was controlled from a worksheet in Excel. Lets assume we will use …

Using MID Function to Extract Characters from Middle of String

The MID function in Excel allows you to extract a set number of characters from the middle of a string.  For example you may have in cell A1 …

Using LEFT Function to Select set Number of Characters from String

The LEFT function is excel allows you to extract a set number of characters from the left hand side of a string.   For example you may have in cell A1 …

Using RIGHT Function to Select set Number of Characters

The RIGHT function in excel allows you to extract a set number of characters from the right hand side of a string.   For example you may have in cell A1 …

How to Remove Blank Spaces from Cells

Sometimes in Cells you will have several blank spaces at the beginning and/or end of a string, example “SPACE    ” or ”   SPACE” …

How to Concatenate in Excel using LEFT, MID and RIGHT String Functions

I have worked with data downloads from companies where the date comes out of the database it is in an unsable format for date purposes.  Instead of displaying …

How to Hide Sheets using xlVeryHidden

Normally to view a hidden sheet a user could go to the menu and click Format > Sheet > Unhide.  Then the user could select the sheet that they wish to …

How to Seperate Date and Time in Excel

Sometimes when you receive data in an Excel file that has been downloaded from a database the date and time are in the same column together (as shown in column …

How to Use SumProduct in Excel

The SumProduct feature in Excel is, I think, a fantastic formula that is so often under used.  It allows you to multiply corresponding values in your chosen …