Convert Formulas to Values

One of the most common tasks in Excel is converting formulas to values. In Excel 2003, you could do a Copy, Paste Special-Values. In 2007 version, this operation is easier than ever. You just need to Ctrl+C to copy the range you want to paste the values and, without opening the Paste Special dialog box, you can go to Home, Clipboard, Paste-Paste Values. Then just press ESC to cancel the Copy mode. This way you will be replacing the formulas on your range by the current value that they return.
Instead of copying the values on top of your formulas, you can copy them to another location, leaving the formulas intact.

VBA - Create folders based on names on a column

This is a simple thing that sometimes we need to use. We have an Excel sheet that has a list of, for example, names in column A and we want to create folders for each of them on a disk. For that we can make a small VBA code to go through each of the rows on column A and create a folder with that name on the path that we specify on our code. To do that, I came up with this code:

Sub MakeFolders()
    Dim xdir As String
    Dim fso
    Dim lstrow As Long
    Dim i As Long
    Set fso = CreateObject("Scripting.FileSystemObject")
    lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lstrow
        'change the path on the next line where you want to create the folders
        xdir = "C:\" & Range("A" & i).Value
        If Not fso.FolderExists(xdir) Then
            fso.CreateFolder (xdir)
        End If
    Next
    Application.ScreenUpdating = True
End Sub

On this example, I will create the folders under C:\ If you want a different place, just change it on the code.

Excel Camera Tool

Excel has a hidden feature that most of the users don’t know. It’s the Camera Tool. For Excel 2003, you can get the button for the Camera Tool by going to Tools-Customize, on the Commands tab, find the category Tools. Scroll down until you find the Camera button. Drag and drop the button into one of your toolbars. Close the customize dialog window.

imagem2

For Excel 2010, you need to make the Camera button appear on the Excel Quick Access Toolbar. Go to Quick Access Toolbar, click on the drop down button and select More Commands, like on the picture below:

CAMERA3
This will open Excel Options dialog box. On the “Choose commands from” drop down select “All Commands” to see all Excel commands. Scroll down the list until you see the Camera command. Select it from the list and click on the Add button on the right. Once it appears on the right box, click OK button from the dialog box.

CAMERA4
Your Camera button will appear now near the drop down button on your Excel Quick Access Toolbar:CAMERA5
Now that you have the Camera Tool button available, let’s see what you can do with it. This tool can be very useful if you want to display a part of your sheet on another location, without affecting the formatting of that area. Let’s see a practical example. Suppose I have the following table:

CAMERA1
I want to show this table on a chart. For that, I will “take a picture” of the table from one of the sheets and place the “picture” on top of the chart that I have on another sheet. Just select the table cells and click on the Camera tool button on your toolbar. Then on the sheet that has the chart just click on one cell and the “picture” of your table will be displayed like if you have inserted a picture on your sheet. You can now work this “picture” the way you want it to look and place it on top of your chart. The final result will be something like this:

CAMERA2
This tool is very useful if you want to display tables from different sheets without having to adjust the formatting of the target sheet.