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.
 
Multithreaded_Calculations

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:
 
Chart_Markers_Images1
 
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:
 
Chart_Markers_Images2
 
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.

Evaluate_Formula1

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.

Evaluate_Formula2

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.

Evaluate_Formula3

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.

Evaluate_Formula4

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

Evaluate_Formula5

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:

First_Last_Word

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.
 
Alternative_Comments0 
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.
 
Alternative_Comments1 
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.
 Alternative_Comments2 
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

Excel2010_In_DepthThe 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:

Duplicates1
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:

Duplicates2
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.

Duplicates3
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.

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

GoTo2

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.

change_comment_shape1

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.

change-cell-comment-shape2003
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.

CAMERA3

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:

change_comment_shape3
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:

change_comment_shape4
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.