
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 articles writen for the common user to help you improve your skills...
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:
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.
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.
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.
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 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
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 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.
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.
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"))
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.
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 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:
This tool is very useful if you want to display tables from different sheets without having to adjust the formatting of the target 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
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
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
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:
Method 2:
Go to Tools, Options and on the View tab, under Window option, uncheck the Zero values options, like this: