PDF to Excel

I’m using a new application called PDF2XL from Cogniview to convert data from a pdf file to an Excel file. The PDF2XL OCR version that I’m using can also use scanned PDF files as well as native ones. You can convert the data from the pdf file into Excel or even into Word or Powerpoint.

The software is very easy to install and to use. When you open the program, to convert a part of your pdf file, you just need to open your pdf file inside the application, select the area that you want to convert and hit the Convert option on your menu bar. In seconds your data will open inside Excel. That’s it! With just a few mouse clicks your data will be converted into the application that you’ve chosen. Nothing is easier that this!

There are 3 versions of the software to choose from:

With prices starting at $97 USD for a single license I think that this is the best and most popular software on the market to convert data from pdf files into Excel.

Excel 2010 Multithreaded Calculation

Since Excel 2007 version that Excel is prepared to take advantage of dual-core (or more) machines. With Multithreaded Calculation, Excel finds formulas that can be calculated simultaneous and calculates them on multiple processors at the same time. 
To activate this function, you need to go to File-Tools and on the Advanced category scroll down until you find the Formulas section. Check the “Enable multi-threaded calculation” and select the “Use all processors on this computer”, as shown in this picture.

 

Excel Charts – Use an Image as Line Chart Markers

Excel line charts can have some different line markers but you can also use images to personalize your charts and give them a different look. When you create a line chart, it will look something like this simple example:

 

 

Here we have some diamond type line markers but we want to change this for a dollar sign marker. To do this we start by adding an image to our worksheet by going to Insert tab on our menu, then on the Illustrations group we can choose to add a Shape, Clipart or a Picture. Then format the image to a size that is suitable to the chart where you want to place it. Select the image and press Ctrl+C (Copy). On your chart, click on the line series to activate it. The line markers on your chart will be selected. Press Ctrl+V (Paste) to copy the image to your line markers and that’s it. Your chart will look something like this:

 

 

You can also have different images for each line marker. For instance, I could put green dollar signs on the months where the sales values increased and red dollar signs where the sales values decreased. When you Paste your image to your line markers, you can choose only the ones that you want to change the images instead of all the line markers.

Evaluate Formulas in Excel 2010

Formulas in Excel calculate in a blink of an eye. Sometimes you have the needs to see what is happening in your formula calculation. For that you can use the Formula Evaluate function in Excel 2010. On the following example we have a simple formula on cell C1, has you can see on the formula bar.

If you select the cell where the formula is and go to the Formulas tab on your menu bar, go to the Formula Audition group and select the Evaluate Formula option, has shown on the image below. This will open a Evaluate Formula dialog box where you can evaluate your formula.

You have an Evaluate button on the bottom that you can click to calculate the underlined portion of your formula (in this case A1). You can use the Step In and Step Out button to go in detail of the underlined portion of your formula.

You can also evaluate just a portion of your formula instead of the entire formula. For that, on the formula bar, use the mouse to select the portion of the formula that you want to evaluate. In this example, we just want to evaluate the A1+A2 portion of our simple formula.

Then press F9 and Excel will just calculate the portion of the formula that you’ve selected, as shown on the next image.

This will display 257 on the formula bar that corresponds to the sum of A1+A2 cell values. Don’t forget to press ESC to exit the formula after you use this method because if you press Enter, that portion of the formula will stay as the calculated value, in this case, it will keep the 257 value instead of the A1+A2 formula.

Get First and Last Word from a String

Maybe you already had the needs to extract the first and last name from a string containing the complete name of a customer. I know I did! This is a good example of the application of this article.
To get the first word from a string, we need to find the first space on the string. For that we can use the FIND() function. This function works from left to right so is perfect for finding the first space on the string. Then we want to retrieve the word to the left of the fist space. For that we will use the LEFT() function. Here’s how we can build our formula:

=LEFT(A2,FIND(" ",A2)-1)

Because we can be dealing with different strings and some may not have a space, we need to check for errors on our formula so we should use the IFERROR() function on Excel 2007 and 2010 like this:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),"")

This way, if we find any error on our formula, instead of showing #VALUE on the cell, we just leave it blank when this happens. In previous versions of Excel the IFERROR() function doesn’t exists so we need to use the ISERR() function that is compatible with all Excel versions. Our formula needs to change to this:

=IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

To get the last word from a string is not so easy because what I’ve mentioned earlier, the FIND() function works from left to right so we need to find the last space on the string and get the text to the right of it. Here’s the formula:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

As in the previous formula, we need to check for errors on our formula to avoid the #VALUE error message, so our formula turns to this:

=IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)

To have a formula that is compatible to all Excel versions, we need to change our formula to this:

=IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

This will produce something like this example:

An alternative to Cell Comments

You can put a comment in any Excel cell by right clicking on the cell and selecting the Insert Comment option or by going to the Review menu tab and on the Comments group, select the New Comment.

 

 
Here you can also choose Show All Comments to show or hide the comments on your sheet. On the File-Options-Advanced under the Display group, you have also some options to change the way your comments appear (or not) on your sheet. As we saw on the Change the Shape of a Cell Comment article, comments can have many different looks.

Excel provides an alternative to cell comments. You can use Excel’s Data Validation feature to display a pop-up message when a cell is selected. This way your message will always show up to the user even if the cell comments are disabled.
Do put a message on a cell using Data Validation, just select the cell where you want to put it and go to the Data menu tab, then Data Validation and select Data Validation option.

 

 
This will open the Data Validation dialog box. Under the Input Message tab you can fill the Title for your pop-up message (optional) and fill the Input Message field. This is the message that will appear when you select the cell. Don’t forget to leave the “Show input message when cell is selected” check box selected.
  
As you can see on the image above, the result will be a pop-up message when the cell (in this case cell A1) is selected. The pop-up message can be dragged to a different location.

Recommended Book - Excel 2010 in Depth

The book that I'm actually reading is Microsoft Excel 2010 In Depth from Bill Jelen. It's a +1,100 pages book that is well written and covers all of the main features of the new Excel 2010 version. For a big book I found it very easy to read because the book is visually attractive, with good and simple examples.
The book is divided under 5 main categories:

• Changes in User Interface
• Calculating with Excel
• Business Intelligence
• Visual Presentation of Data
• Sharing

I definitely recommend that you read this book to get to know Excel 2010 in depth because there are so many new things to discover that you will need a big book like this one to discover them all.

Excel 2010 – Introduction to PowerPivot

PowerPivot is a new free add-in for Excel 2010. It’s probably the most expected feature in the new version of Excel because it turns it into a powerful Business Intelligence tool. These are some of the major benefits of PowerPivot:

• While Excel grid goes until row 1,048,576, has we’ve seen on the Excel 2010 Grid post, with PowerPivot you can handle millions of rows of data. You can sort, filter, scroll and pivot that amount of data using PowerPivot.
• You can create pivot tables with data from multiple tables.
• Import data from different sources like Access, RSS, SQL Server and show the data into a single pivot table.
• DAX – Data Analysis Expressions is the new formula language in PowerPivot. DAX has 117 functions for two types of calculations: 81 normal Excel functions and 54 new functions mainly for data analysis.
• Excel workbooks with PowerPivot are smaller than the ones that use traditional pivot tables.

There are also some negative aspects on using PowerPivot on your workbook:

• You can’t Group on pivot tables.
• You can’t use VBA on PowerPivot as you could with pivot tables.
• PowerPivot can’t be used with other versions of Excel, only the 2010 version.

Excel 2010 – Handling Duplicates

In previous versions of Excel, removing or highlighting duplicates was not a natural thing of Excel, you would need some tricks to do it. Excel 2010 now provides a tool to handle this easily. Take a look at this sample data:


As you can see, there are some Customers and Products duplicated on our table. We want to get a list of unique customers. For that we need to copy the data from column A to another section of our worksheet (in this case the range A1:A7). Then select a single cell within the data set. Go to your menu bar and on the Data tab click on the Remove Duplicates button. A Remove Duplicates dialog box will open like this:


Excel will assume that you have headers on your data and will check the “My data has headers” check box and the name of your columns will appear on the list below. Here is where you will select the fields that you want to make unique records. In this case it’s just the Customer field. Click OK to close the dialog box and you will get a message with the number of duplicates found and remove and the number of unique records that remained.


In this example we used just a single column to check for duplicates but we could check for duplicates using more fields. We could check for unique combination of Customers and Product. For that, on the Remove Duplicates dialog box, just select both columns from the list.
Instead of removing your duplicate records, you may want to just highlight them on your sheet so that you can see witch are duplicated. You can do that by using Conditional Formatting option under Home tab. Please check the Highlight Duplicates article for that.

Excel 2010 Grid

In Excel 2003 we had only 65,536 rows and 256 columns. This would gives us 16.7 million cells to work.
In Excel 2010 we now have 1,048,576 rows and 16,384 columns. This more or less 17.1 billion cells on each worksheet witch represents a 102% increase over the old version.
Columns in Excel 2003 where labeled from A to IV. In Excel 2010 they are labeled from A to XFD. This change means that some old range names such as YTD2005 or TAX2006 cannot be used anymore because now they are valid cell addresses. Excel automatically changes this name ranges during conversion. For example, a range name such as YTD2005 will be changed to _YTD2005.

Delete all pictures or charts on Worksheet

This is a tip on how you can delete all pictures and/or charts on a worksheet without having to manually select each one and delete them.
So, you can use the F5 shortcut key on Excel to open the Go To dialog box.

Then click on the Special button on the bottom of the dialog box to open the Go To Special dialog box.

Select the Objects option and click the OK button to close the dialog box. Now you have all of the objects on your worksheet selected. Check if you don’t have any more objects on your sheet that you don’t want to delete, like a company logo or something, before you press Delete key to delete all of them at the same time.

Excel Shortcut Keys – Part I

Excel has very useful shortcut keys that helps you save time while inserting data on your worksheet. This is the first of a series of posts about Excel Shortcut Keys.

Ctrl + Shift + ;  Enter the current date on a cell
Ctrl + B  Bolds highlighted selection on sheet
Ctrl + I  Italics highlighted selection on sheet
Ctrl + U  Underlines highlighted selection on sheet
Ctrl + Z  Undo last action
Alt + =  Create a formula to sum all of the above cells
Ctrl + Shift + $  Formats the selected cells as currency format
Ctrl + Shift + #  Formats the selected cells as date format
Ctrl + Shift + %  Formats the selected cells as percentage format
Ctrl + Space  Selects the entire column where the cursor is placed
Shift + Space  Selects the entire row where the cursor is placed

Change the Shape of a Cell Comment

Cell comments is used very often on worksheets but they look always the same way because most users don’t know that they can change the shape of the comment. Instead of being this normal yellow rectangle, it can have lots shapes and colors.

To change the comment shape, make sure that the comment is visible by right-clicking the cell where the comment is placed and choosing Show/Hide Comments options. Then just select the comment border and, in Excel 2003, go to the Draw menu and choose the Change AutoShape option and select the shape you for your comment.


For Excel 2010, you have to add the Change Shape button to your Quick Access Toolbar (QAT). Click on the right arrow on your QAT to open the Customize Quick Access Toolbar, like shown on the picture below. Click on the More Commands option to open the Excel Options dialog window.

On the “Choose commands from” dropdown, select the Drawing Tools Format Tab. On the bottom list, select the Change Shape button and click the Add button. Click OK to close the Excel Options dialog box. Your Change Shape button will appear on your QAT like this:


Now that you have your button, you can select your comment border, click on the Change Shape button and select the shape that you want for your comment. the result can be something like this:


Don’t forget that you can also change the fill color of your comments, the color and weight of your comments border, as well as many other customizations that you can do to your comments for them to stand out on your worksheet.

Excel 2010 – Sparkline Charts

Excel 2010 has a new feature called Sparkline charts that allows you to place a mini-chart inside a cell on your sheet. There are 3 type of sparkline charts: line, column and win/loss. The line and bar charts are very useful to show you a trend about your information while win/loss charts show you positive or negative numbers across a horizontal axis, represented by squares that show positive values if they are above the horizontal axis or negative if they appear below.
To create a sparkline chart, just click on the cell where you want your chart to appear. Click on the Insert tab on your menu bar and click on the type of sparkline chart that you want. A “Create Sparklines” dialog window will appear. The cell that you’ve selected on your sheet will already appear on the field “Location Range” of the dialog window. Click on the “Data Range” field and select on your sheet the range that you want to use on your chart. The range will appear on the “Data Range” field. Click OK and you’re done.
On this picture, you can see on cell F3 a line sparkline chart and on cell G3 a bar sparkline chart.


On the next picture, you can see on cell F3 an example of the win/loss sparkline chart.

Create a Self-Expanding Chart

This happens very often with most of the Excel users. You have a set of data that you use to create a chart. If you latter want to add more data to the chart, you need to redefine your chart data series, unless if you didn’t add rows or columns in the middle of your original data where in this case, Excel will assume them on your chart.
To avoid this, just create your chart as usual. Then select any cell inside the range of data that you used to create the chart. Go to Insert-Tables to convert the range of data to a table. This is all you need to do for Excel to recognize the data as a table and the data added to the table can be reflected on the chart without the need to redefine your source.

REPT() - Repeat Function

The REPT() function is unknown for most of Excel users. This function repeats the given text of characters a given number of times. The syntax is like this:

REPT(text,number_times)

text is the text string that you want to repeat. number_times is the number of times you want to repeat the text on the cell.

Example:

=REPT("*",3) repeats the "*" character 3 times on the cell. The result will be "***".

A good use of this function is to make cell "bar" charts that will illustrate the dimension of the number on an adjacent value. Here's how it would look:

On this example, I use the character "■" from the Character Map and used the function like this:
=REPT("■",C3/1000)

I divided the value by 1000 so that the size of the "bar" stays at a reasonable size.

INDIRECT() Function

The INDIRECT() function returns the value of the cell referenced by a text string. The syntax for this function is:

INDIRECT(ref_text, A1)

ref_text is a reference to a cell in an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If it's not a valid cell reference, it will return #REF!.

A1 is a logical value that indicates what type of reference is on cell ref_text. If A1 is true or omitted, ref_text will be interpreted as an A1-style reference; if A1 is FALSE, ref_text will be interpreted as an R1C1-style reference.

Let's see a simple example of the use of this function. Let's say that we have this workbook that has a sheet for each month of sales.

On cell B3 we want to sum the sales values of the range A1:A10 for the month that we specify on cell B1, in this case "Feb" that corresponds to the Feb sheet on our workbook. For this we can put a formula using INDIRECT() function on cell B3 like this:

=SUM(INDIRECT(B1&"!A1:A10"))

Hide Cell content

Excel doesn’t have any options to hide the content of a cell, not even the 2007 version, without hiding the row or column where that cell is. There are several ways to bypass this limitation:

• Use a special custom number format by right-clicking the cell that you want to hide and open the Format Cells dialog box and on the Number tab select Custom on the Category list. Then on the Type field enter ;;; (three semicolons). Click OK.
• Change the font of the cell to the same as the background color.
• Add a shape that has the same color as the background on top of the cell that you want to hide the content.

This methods don’t really hide the content of the cell because if you click on top of the cell you can still see the content on the formula bar. You can always hide the formula bar also to avoid this or you can do it like this:

• Select the cells that you want to hide the content
• Right-click to go to Format Cells or press Ctrl+1 to open the dialog box. Click on the Protection tab
• Select Hidden check box and click OK
• In the menu bar, go to Review-Protect Sheet
• Add a password or press only the OK button

This way the sheet will be protected and the cells are locked. You can’t make changes to the cells. By default, Excel makes all cells “locked”.

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.

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:

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.

Your Camera button will appear now near the drop down button on your Excel Quick Access Toolbar:
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:

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:

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

VBA - Put borders around all used cells on a sheet

This code is used to put borders around every used cell on a sheet.

Sub DrawBorders()
   With Cells.SpecialCells(xlCellTypeConstants, 23)
       .BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
        On Error Resume Next 'used in case there are no inside borders
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

VBA - How to Autofilter all of your sheets

If you want to put Autofilter on all of the sheets on a workbook, you can't do it by select all of the sheets on the sheets tabs. Instead you can use this simple VBA code:

Sub Autofilterall()

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Range("A1").Activate
        Selection.AutoFilter
    Next ws
    Application.ScreenUpdating = True

End Sub

VBA - Get Sheets names and Rows count

This article is based on a specific question that I was asked. "How to get all of the sheets names and number of filled rows in column A from a workbook in a new sheet?". This is the code I came up to solve the problem:

Sub GetSheetNames_andRowCounts()

    Dim i As Integer
    Dim j As Integer
    Dim SheetCnt As Integer
    Dim lstRow As Long
    Dim ws1 As Worksheet
    Dim SheetName As String
    With Application
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    On Error Resume Next
    'Delete the Target Sheet on the document (in case it exists)
    Sheets("Target").Delete
    SheetCnt = Worksheets.Count
    'Add the Target Sheet
    Sheets.Add after:=Worksheets(SheetCnt)
    ActiveSheet.Name = "Target"
    Set ws1 = Worksheets("Target")
    j = 1
    'Combine the sheets
    For i = 1 To SheetCnt
        Worksheets(i).Select
        'check what is the last row with data using column A as a reference
        lstRow = ActiveSheet.Cells(65536, "A").End(xlUp).Row
        'get the name of the sheet
        SheetName = Worksheets(i).Name
        'assign the values to the Target sheet
        ws1.Cells(j, 1).Value = SheetName
        ws1.Cells(j, 2).Value = lstRow1
        j = j + 1
    Next
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Sheets("Target").Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select

End Sub

VBA - Add photos to cells inside comments

I have a sheet with employees numbers and names and I wanted to see the corresponding photo of each employee. I have a folder with the photos of each employee. Each filename is the employee's number. To be able to see the photos, I decided to put each photo inside a comment on each employee number cell. This way, when I pass over the employee number, a comment window will show up with the employee's photo. It will look like this:

For this, I have the following code:

Sub InsertPictures()
    Dim cll As Range
    Dim rng As Range
    Dim strPath As String
    strPath = "D:\Photo Folder"
    With Sheets("Sheet1")
        Set rng = Range("A2:A416")
    End With
    For Each cll In rng
        If Dir$(strPath & "\" & cll.Value & ".jpg") <> "" Then
            With cll
                .ClearComments
                .AddComment ("")
                .Comment.Shape.Fill.UserPicture (strPath & "\" & cll.Value & ".jpg")
                .Comment.Shape.Height = 160
                .Comment.Shape.Width = 120
                .Comment.Shape.LockAspectRatio = msoTrue
                End With
        End If
    Next cll
End Sub
You can adjust the size of your photos by changing the Height (160) and Width (120).

Excel Charts - Hide Zeros

If you have a series of data to display on an Excel Chart, like this one:

It will look something like this:

As you can see, the chart will display the zero values. If you want to hide the zeros, you have two ways of doing:

Method 1:
1) Select the range of cells where you have the values for your chart
2) Right click and select Format Cells
3) On the Number tab, on Category, select Custom
4) In the "Type" box you will see the current format for the range you selected
5) Put the cursor on the end of the content of the Type box and enter 0,0;;; it it will look something like this picture:

6) Your zero values will be hiden now

Method 2:
Go to Tools, Options and on the View tab, under Window option, uncheck the Zero values options, like this: