VBA Macro: How to add and rename worksheets

In following demonstration video I will implement Excel Visual Basic macro that reads worksheet names from the cells A1, A2, A3, ... in sheet "Sheet1" and creates new worksheets with correct names.

For simplicity there is no error handling etc and following simple code does the trick:


Sub createsheets()
    Dim newsheet As Worksheet
    Dim r As Integer
    r = 1
    Do While Sheets("Sheet1").Cells(r, 1).Value <> ""
        Set newsheet = Sheets.Add
        newsheet.Name = Sheets("Sheet1").Cells(r, 1).Value
        r = r + 1
    Loop
End Sub

Excel VBA Macro: How to name Worksheets

You can have several worksheets in one Excel workbook. Default names of worksheets are Sheet 1, Sheet 2, ... You can change names manually by double clicking the name of the sheet, but you can also automatise this.

In the following demonstration video I will implement a simple VBA macro that names all worksheets in Excel workbook according to the content of the each worksheet's cell A1. You can go through all worksheet objects by using For Each -loop:


Sub namesheets()
    Dim s As Worksheet
    For Each s In Worksheets
        s.Name = s.Cells(1, 1).Value
    Next
End Sub

How to use Excel's DATE, DATEVALUE, MONTH, YEAR and DAY functions

Following video demonstrates the usage of Excel's Date functions:

• DATE() : Takes year, month and day as parameters and returns Excel date value.
• DATEVALUE(): Takes date string as parameter and returns Excel date value.
• YEAR(): Takes Excel date value as parameter and returns corresponding year number (1900..9999).
• MONTH(): Takes Excel date value as parameter and returns corresponding month number (1..12).
• DAY(): Takes Excel date value as parameter and returns corresponding day number (1..31).

If you are unfamiliar with Excel's way to handle dates, please check Excel Date System basics.

Excel Date System Basics

Excel handles Date values as integer numbers. Number one means 1st of January 1900, two means 2nd of January 1900 and so on. Today (27th of February 2013) is day number 41332. So date number can be understood as the number of days since 31st of December 1899.

The first day that Excel supports is 1st of January 1900 and if you need earlier dates, Excel's build in date functions are not working.

Excel actually supports two different Date Systems: In addition two this 1900 Date System you can also use 1904 Date System where number one means 1st of January 1904. This can be selected on Calculation settings but I recommend to use 1900 Date System to maintain better compatibility to e.g. other spreadsheet programs.

Excel handles times as decimal numbers between 0 and 1 and so 41332,41667 means that the date is 27th of February 2013 and time of the day is 10AM.

Following video demonstrates this.

Difference between TODAY() and NOW() functions

Excel has two similar time functions TODAY() and NOW(). These functions are not the same. Difference is that TODAY() function returns only current date (time value is 12AM or 0:00) and NOW()-function returns current date and current time.

Following video demonstrates this:

How to use Excel's cell comments

You can add descriptive comments to Excel cells. Comment doesn't have any influence to content of the cell. Each comment can be always visible or visible only when mouse pointer is on cell. You can drag always visible comments to any location on the worksheet. Comment boxes are also resizable.

Following video demonstrates this:


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel VBA macro: How to implement running clock

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

• RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE. 
• StartClock: Sets global variable clockOn = TRUE and calls RunClock
• StopClock: Sets global variable clockOn = FALSE

Recursive call is made using Excel's function

Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule) 

In this case only first two parameters are needed:

• EarliestTime: Time when Procedure is been run. In this case Now + TimeValue("00:00:01")
• Procedure: The name of the procedure. In this case RunClock itself 

If you want to create buttons for starting and stopping a clock, please check how to add button

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel COUNT functions

Excel has several functions for counting the number of cells that meet specific criteria:

• COUNT(value1, value2,...): Counts number of cells (or arguments) containing numbers
• COUNTA(value1, value2,...): Counts number of non empty cells (or arguments)
• COUNTIF(range, criteria): Counts number of cells meeting the criteria
• COUNTIFS(range1, criteria1, range2,criteria2,...): Counts number of cells meeting all given criteria
• COUNTBLANK(range): Counts number of empty cells
• There are also functions DCOUNT and DCOUNTA, but I will handle those later

Following video demonstrates the usage of COUNTX-functions:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Remove Duplicates -feature

Very typical task with Excel is to clean tables by removing duplicates. Fortunately Excel has handy tool to do it automatically. There is the feature Remove Duplicates in Data tab of Ribbon bar.

Check the video below.


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Skip Blanks in Paste Special and what does the feature do

There is an option Skip blanks in Excel's Paste Special -dialog. You may have wondered what is the purpose of that feature.

After watching this video, you will know :-)


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

The big secret has now been revealed: Skip blanks pastes only non blank cells and leaves other cells untouched.

Excel VBA macro: How to loop through selection

In following video I will show an Excel VBA macro example about how to loop through each selected cells and write location information to cells.

We can use Excel's Selection object (Range type containing selected cells of the active worksheet) and cycle through selected cells by using For-Each-Next loop:

For Each c in Selection
    ...
    c.Value = ...
    ...
Next

c stands for single cell (Range type).

Following video clarifies this.
Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel's Switch Reference -tool

Following video demonstrates nice and easy way to quickly change cell reference type between absolute and relative reference:

1. Select Formulas tab in Ribbon bar
2. Select reference in formula
3. Click Switch Reference to change reference type A1 --> $A$1 --> A$1 --> $A1

See my earlier posting about Absolute and Relative References.

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel's Formula Audit -tools

Sometimes it is hard to understand the logic of complex Excel formulas - Especially when there are lots of references to different cells or ranges. Excel has (of course :-) ) great helper tools for this. There is set of Audit Formulas -tools in Ribbon bar's Formulas tab.

• Trace Precedents: Shows with arrows from where the selected formula takes it's parameters
• Trace Dependents: Shows with arrows the formulas that contains reference to the selected cell
• Check for Errors: Tool for analysing possible errors in formula
• Remove Arrows: Clears arrows from the screen

Following video demonstrates this:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel Filter: How to analyse data by filtering multiple columns

Following example video demonstrates how to analyse big data tables by setting filtering criteria to multiple columns simultaneously. In the example below I show only countries with population less than one million and population updated recently.


Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel Filter: How to filter data according to dates

Filter options

Today I will demonstrate Excel's powerful capabilities to filter date columns. You have many options for selecting which time period to show or not to show.

In following video I will show how to select time period

• by selecting start date and end date
• by selecting "Last Year" from the menu
• by selecting any year

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel Filter: How to filter data according to text content

Following demonstration video shows how to filter data according to text content. In this example case we have list of countries and their populations. The list contains independent countries and dependent territories with country it belongs to in parenthesis. We can show only independent countries by filtering "country name"-column with criteria "show only cells not containing parenthesis" and with the opposite logic show only dependent territories. The video will clarify this:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

Excel Filter: How to filter number columns efficiently

Following example video demonstrates how to use Excel's Filter-feature to analyse data of the large tables - in this case population of countries. You can easily filter e.g.

• Top 10 list
• Bottom 10 list
• Above average list
• Below average list
• Countries with population between 5 and 10 million people
• etc...

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to use Excel's Freeze Panes -feature

It is sometimes annoying when scrolling in big Excel table because you may not know what data the current row or column should contain. E.g. when each row represents one person's data, it is difficult to analyse data if you have scrolled so that you cannot see the name column and don't know to who current row belongs and what is the title of the current column.

Excel has (of course :-)) a solution for this. It is a feature called Freeze Panes and with it you can make selected topmost rows and leftmost columns always visible.

Following example video demonstrates the feature (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

Excel VBA macro example: How to count concurrent times

Here is an example VBA macro for solving a call log analysis problem:

We have call log

• Column A: Date of the call
• Column B: Time of the call
• Column C: Duration of the call

How many concurrent calls?

Following screen capture demonstrates the situation:

To solve the problem I implemented Visual Basic macro ConcurrentCalls() and helper functions max(), min() and isConcurrent(). Here is the code:

Function max(a, b)
  If a > b Then
    max = a
  Else
    max = b
  End If
End Function

Function min(a, b)
  If a > b Then
    min = b
  Else
    min = a
  End If
End Function

Function isConcurrent(start1, end1, start2, end2)
  If max(start1, start2) <= min(end1, end2) Then
    isConcurrent = True
  Els
    isConcurrent = False
  End If
End Function

Function ConcurrentCalls()
    maxconcurrent = 1
    r1 = 2
    Do
        concurrent = 1
        r2 = 2
        Do
            start1 = Cells(r1, 1).Value + Cells(r1, 2).Value
            end1 = start1 + Cells(r1, 3).Value
            start2 = Cells(r2, 1).Value + Cells(r2, 2).Value
            end2 = start2 + Cells(r2, 3).Value
            If r1 <> r2 And isConcurrent(start1, end1, start2, end2) Then
                concurrent = concurrent + 1
            End If
            r2 = r2 + 1
        Loop Until Cells(r2, 1) = ""
        maxconcurrent = max(maxconcurrent, concurrent)
        
    Loop Until Cells(r1, 1) <> ""
    ConcurrentCalls = maxconcurrent
End Function

Feel free to examine and let me know if you see a bug or can imagine a better solution!

Check also 101 Excel Secrets and Everyday Excel video course.

Excel Conditional Formatting: How to highlight cells based on age

Following Excel Conditional Formatting example demonstrates how to highlight cells with dates older than 1 year, 4 years, 8 years and 12 years with different colours (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

Excel VBA Macro: How to use InputBox

Following example video demonstrates how to use Input Box dialog from the Excel VBA Macro code.  In this example input type is Range-object. Example code asks user to select cells and then fills those cells with text "Foo".

Tip: If you have difficulties to see the video, try to maximise it from lower right corner.

How to customise Excel table's look and feel

There are numerous ways you can customise how your Excel table looks. Yo can e.g.

• Change the font color or cell's fill colour
• Make texts bold, italic, etc.
• Draw different kinds of borders around your cells
• Align cell's texts different ways
• Merge cells to make it easier to align texts the way you like
• Adjust column widths and cell heights
• Hide Excel's gridlines
• ...and much more...

A shortcut tip: You can open Format Cells -dialog by pressing

• Windows: ctrl + 1
• Mac: cmd + 1

Following demonstration video shows some of these techniques to format a table (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

How to use Excel's VLOOKUP-function

VLOOKUP-function is a great help when you need to find data from the large tables. With VLOOKUP you can search for a value in the first column of the table and return a value in the same row from the column you specify. VLOOKUP has three mandatory parameters and one optional parameter:

1. parameter: Value to search in the first column
2. parameter: Table to search from
3. parameter: Index of the column from which the matching value must be returned. The index of the first column is 1
4. optional parameter: If omitted or TRUE, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will only find an exact match.

The following video demonstrates a simple example how to use VLOOKUP:

Excel VBA macros: How to add button to worksheet

In my previous blog post I launched Excel VBA macro from Ribbon bar's Developer tab (Macros). More user friendly way is to add push button for macro directly to worksheet. Following video demonstrates how to do it (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

Excel VBA macros: How to record a VBA macro

It is a good idea to automatise time consuming routine tasks in Excel. You don't need to know how to implement VBA macros, because you can just record your tasks. In following example I record a macro that assumes that I have people's whole names listed in column A and want to split names (first name and last name) into columns A anB. This is good example when simple recorded macro can save you from doing this routine task all over again.

Here is a video that demonstrates the VBA macro recording and shows how to run the macro (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

How to import text and use Excel's Text to Columns -feature

Normally, when you paste text to Excel, the whole text will appear in one column. Luckily Excel has nice feature called Text to Columns. With that feature you can spread your text data to columns nice and easy. In my example, I have text where items are delimited with commas and colons.

Following video demonstrates how to import that text to Excel's columns easily (Tip: If you have difficulties to see the video, try to maximise it from lower right corner):

How to use Excel's Goal Seek analysis

Excel provides many ways to analyse data. One powerful tool is Goal Seek -tool. Following demo presents a simple example to find out body weight when target Body Mass Index (BMI) is known by using Goal Seek analysis. All we need to do is to know the formula of BMI and then define target BMI. We try to solve out the body weight that is needed to achieve our target BMI.

The formula for BMI is:

BMI = Weight[kg] / (Height[m] x Height[m])

The following video demonstrates this:

Excel: How to show number in custom format

You are able to format Excel's cell items as you like. You can e.g. add custom texts to be shown with numbers. Typical case is to show numbers with units:

1. Select the cell/cells you want to format:
   Context menu: Select Format Cells
   Shortcut: Windows ctrl + 1, Mac cmd + 1
2. Select Number and Custom from the Category list.
3. If you want to one decimal and unit, type following format code
   0.0" kg" or
   0.0" lb"

Following video demonstrates this in action:

How to create and use Excel's drop-down lists

Excel has a great feature to improve productivity by using drop-down list. If you have specific list of allowed entries to a cell, use drop-down list! In my example video below my list contains car brands. Creating list is easy:

1. Type down your list items to Excel worksheet. It might be a good idea to put list to different worksheet than your main data.
2. Activate cell where you want to use the list
3. Select Data-tab from the Excel's Ribbon menu.
4. Select Validate
5. From the Allow drop-down list, select List
6. Click the painting tool on the right hand sight of the Source box
7. Select list items you typed in the first phase
8. Press Enter
9. Now you have drop-down list for selecting items in the cell you activated

Following video demonstrates this:

How to use Excel's absolute reference ($) and why

The normal way to reference Excel's cell is like "A1". This is called relative reference. When copying cell containing formula with relative reference, the reference will change depending on where to paste it. If you for example paste formula containing "A1" reference to one cell above, the new reference will be "B1". If you want to fix column and/or row of the reference so that it won't change when pasting, you can use so called absolute references. All you need to do is to add $ to the reference:

• $A$1 means that both column and row are fixed
• $A1 means that column is fixed but row reference will change when pasting formula
• A$1 means that row is fixed but column reference will change when pasting formula

Following video demonstrates this:

Facebook & Twitter

Be aware about my new posts by following Excel4Everything on Facebook fan page and in Twitter. So Like and Follow and you won't miss any valuable tip or trick! -:)

How to use Auto Filter -feature

An easy way to analyse data of large tables is to use Excel's Auto Filter -feature. With this feature you can define filtering criteria for each column of the table to show only the information that is most relevant to you.

Following video demonstrates the Auto Filter -feature

How to use SUMIF()-function

Excel's SUMIF()-function is handy function for summing items matching specified criteria.
The syntax of the SUMIF is

SUMIF(range, criteria, [sum_range])

• range: range of the cells to apply the criteria against
• criteria: determines which cells to add
• sum_range: this is optional parameter specifying which cells to sum. If omitted, the range is used as the sum_range

Following video demonstrates the usage of SUMIF:

How to highlight dates that are older than 70 days using Excel's Conditional Formatting

Following video demonstrates how to use Excel's conditional formatting to highlight dates that are older than 70 days. The formula takes into account that empty cells won't be highlighted. Following formula in D1's Conditional Formatting does the trick:

=AND(D1<>"";DAYS360(D1;TODAY())>=70)

Copy the formula to all cells in column D.

Here is a video demonstrating this:

How to multiply large amount of numbers in Excel by using Paste Special

Sometimes you may have a need to multiply large amount of Excel worksheet's numbers by some specific number. E.g. you want to make all numbers negative or multiply them by 100. Instead of doing this routine task manually "by hand", you can use this easy trick and save lot of time:

1. Write multiplier to one cell and copy it to clipboard (Shortcuts ctrl + c in Windows and cmd + c in Mac)
2. Select cells you want to multiply
3. Open context menu and select Paste Special...
4. Check Multiply and press OK
   --> all selected cells are multiplied by the number you copied!

Following video demonstrates this in action:

How to colour Excel chart according to source data using VBA

I will demonstrate you the power of Excel VBA macros with a nice simple macro that goes through all your chart objects and set the colours of those according to their source cells' colour. If you are unfamiliar with Excel VBA, then I suggest that you first check my previous post Excel Tips and Tricks: How to write simple VBA function.

Here is the code that does the trick (I don't guarantee that it works in every cases, but it should give you the idea):

Sub ColorCharts()

    For Each ch In ActiveSheet.ChartObjects

        For Each ser In ch.Chart.SeriesCollection

            s = Split(ser.Formula, ",")

            For i = 1 To UBound(ser.Values)

                ser.Points(i).Interior.Color = Range(s(2)).Cells(i).Interior.Color

            Next i

        Next ser

    Next ch

End Sub

Following video demonstrates this:

How to write Excel VBA function

If there is no proper function for your purposes, you can always write one by yourself. All you need is a basic understanding of Basic-programming. It is not hard!

In this demonstration we implement macro function for reversing text (e.g. "help"-->"pleh"). Following steps are needed:

• Add Developer tab to Excel Ribbon bar:
  Excel 2011 for Mac: Select click gear in the Ribbon's right edge and select Ribbon Preferences...
  Excel 2010 for Windows: Right clickon the Ribbon and select Customize Ribbon...
  --> Make sure that the Developer-Tab is selected in the Tab list 
• Select Developer-Tab
• Start Excel's Visual Basic -editor
• Add new module by opening context menu from VBAProject and selecting Insert --> Module
• Write following code to Module1

Function TransposeText(txt)

    Dim returntxt

    returntxt = ""

    For i = Len(txt) To 1 Step -1

        returntxt = returntxt + Mid(txt, i, 1)

    Next i

    TransposeText = returntxt

End Function

• Now your function is ready to use
• Go back to the worksheet and test the new function

Here is the video demonstrating this using Excel 2011 for Mac:

How to transpose Excel table using Paste Special -feature

There is a handy way to transpose entire table:

1. Select the table you want to transpose
2. Select Copy
3. Select Paste Special (e.g. from the context menu)
4. Mark checkmark to Transpose
   --> And vóila, you have transposed table!

Following video demonstrates this:

How to use Excel Conditional Formatting to highlight past dates

Excel has very powerful feature called conditional formatting. In this post I will demonstrate how to use the feature to automatically highlight past dates in different way than future dates.

• I use TODAY()-function to get current date
• I set conditional formatting so that when the value of the cell is less than TODAY(), it will be formatted

Following video demonstrates this:

How to use Excel's SUMPRODUCT()-function

An easy way to sum products of corresponding numbers in several arrays is to use Excel's SUMPRODUCT()-function. It takes one or more arrays as parameters and calculates sum of products.
Following video demonstrates first the traditional way to do that and then same by using SUMPRODUCT()-function:

How to Adjust column width or row height in Excel

There are many ways to adjust column width or row height in Excel worksheet. I personally use following methods:

1. Manual adjusting: Move mouse pointer to column/row header between two columns/rows. Mouse pointer turns in to two headed arrow. Now you can adjust column width / row height by just dragging with mouse.
2. Automatic adjusting: Instead of dragging, you can double click between two columns/rows when mouse pointer is two headed arrow. That will result column/row automatic adjustment to the optimal width/height.
3. Adjusting several columns/rows at the same time. Actions above adjust all selected widths/heights. You can select some columns/rows or entire worksheet by clicking crossing of column and row headers on top left corner of the table.

Following video demonstrates this:

How do I use Excel, part #1: Training diary

I'm active sports enthusiast. I have been using Excel based training diary since year 2001. My every single sport activity is marked in Excel. Today I have 2806 activities recorded.

A single activity includes e.g. following information:

• Date
• Sport (e.g. running, volleyball, gym, walking, skiing, ...)
• Duration
• Type (aerobic, strength training, ball games, ...)
• Distance
• Heart rate
• Calorie consumption
• ...

Using this data I can analyse what ever information I ever need. It is interesting for example to check how my weekly sporting hours have changed during years. Or how my running pace is now much slower than ten years ago :-)

How to make text starting with =, - or + character in Excel

Sometimes you may want to write cell text that begins with =, - or + character. Excel, however interprets text beginning with those characters as formula and that causes an error. Trick is following:

• Type ' -character as a first character. This forces Excel to interpret the cell content as text.
  e.g. typing '--> displays as --> and causes no error

This video demonstrates this:

Excel Tips and Tricks: My favourite shortcut keys

For me Excel's most time saving shortcut keys have been ctrl + d and ctrl + ':

• ctrl + ' : Copies a formula from the cell above the active cell into the cell.
• ctrl + d : Uses Fill Down command to copy the contents and format of the topmost cell of a selected range in the cells below.

Here is the video demonstrating this:

How to generate series in Excel

Did yo know that you can generate many kinds of series just by

1. Selecting first two cells of the series
2. By dragging from the lower right corner of the second cell as long series as you need

Following video demonstrates few basic ways where this saves lot of time: