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:
Write multiplier to one cell and copy it to clipboard (Shortcuts ctrl + c in Windows and cmd + c in Mac)
Select cells you want to multiply
Open context menu and select Paste Special…
Check Multiply and press OK –> all selected cells are multiplied by the number you copied!
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):
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
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
Start Excel’s Visual Basic -editor
Add new module by opening context menu from VBAProject and selecting Insert –> Module
Write following code to Module1
returntxt = ""
For i = Len(txt) To 1 Step -1
returntxt = returntxt + Mid(txt, i, 1)
TransposeText = returntxt
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:
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:
There are many ways to adjust column width or row height in Excel worksheet. I personally use following methods:
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.
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.
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.
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:
Sport (e.g. running, volleyball, gym, walking, skiing, …)
Type (aerobic, strength training, ball games, …)
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 🙂