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: