Vinod Kumar (Blog home)

This is in continuation to tips around Excel. This particular tip has been around in Excel for ages. But just like any other feature in Excel this is yet another nice utility that is lesser known to many.

Let us assume you have the following data set. This is a simple data set and it has Name that has come from some external source. Now we want to split as Lastname and FirstName instead of the usual Name column as shown below.

Now to split the column, create a new column “C” by inserting a new column adjacent to our name column. Next select all the values from Name column. With this selected, goto Toolbar “Data” Tab –> “Text to Columns” under Data Tools section.

Selecting this invokes a wizard which is easy steps. Select “Delimited” and press “Next”.

This is the screen where we will select the delimiter. In our Dataset, the Name field has a space. So we will select “Space” in this screen. And select “Next”.

Now we will be presented with the final screen. This can be used for some Formatting options. In my data, I am going to select the defaults. Press “Next”.

Our final screen shows the split automatically done. The screen would typically look like below. Give the Column “C” as Lastname and we must be done.

This option works wonders when data values are consistent and has just two parts or three parts for ALL data values. Else we will get additional columns and if there is data, a popup appears telling us there are values and do we need to override. Be careful in such cases.

Hope you have used these in the past and I will try to bring in more such tips in our subsequent blog posts.

Let me bring this weeks Excel Tips and Tricks series with a simple tip. The scenario I am talking is most used – You want to Build a Sum of values for a table of values.

The most common requirement for any table of data is to build a SUM or Average along its rows and Columns. There are multiple approaches and let me outline some of the most common ways it is done today and I find each of these methods interesting and one method will always be efficient than the other. I am sure I have not outlined all the possible ways of doing this task.

Initial DataSet

This is a simple dataset of names and various Sales number. Now we need to have create totals Month-wise as well as employee-wise. So our dataset looks like below:

And we want to build SUM across and along as shown below:

Using SUM Formula

The basic old school of thought will be to Type “=SUM” as shown in figure below and we can give the range of C3-C6. Similarly we will do it for each of the cells.

Copy-Paste: A more efficient way to get the values across will be copy the Cell C7 and paste it across in D7, E8 and so on. This copies the formula of sum and gets the relative cells accordingly for each cell.

Using Shortcut

When the Cursor is at C8, Press “Alt+” (Alt and + key). This generally gets the adjacent cells and does the auto select with the SUM option. This is really powerful and your screen would look like this:

Now that we have got the C8, we can do Copy-Paste. Wait, now if you move your mouse to the C8 cell corner – you will be presented with a cross-arrow. Just drag this till F Column. It will magically fill the formula and make the SUM.

Now let me move to the Columns G3 and to get the SUM, let me use “Alt+” and press Enter. Voila, you have the Sum across columns done. Now we can use the same cross-arrow and drag – hold on.

Once you see the cross-arrow in columns, just double click. You will automatically fill till G7. Awesome right?

Using Toolbar

I am sure you have used any of these previous techniques in the past. The most efficient way I have figured out for Summing is using the Toolbar –> Home –> Editing Section –> AutoSum.

For this to work, select the region and just one click from toolbar. The values are filled.

It is just not only for single click SUM, you can also get AVERAGE, COUNT, MAX, MIN and many more functions as shown in figure below.

The best part of this method is, we have not used any Keyboard keys and I call this “Magic with mouse :)”.

Finally, I complete this weeks Excel Tips series and do let me know your feedbacks, I will be more than happy to write more on these lines and your comments are most welcome if you want me to write more of these in the future. I can surely write based on your feedbacks and enthusiasm for this topic. Office is an ocean and we can never be lost for tips to share. Thanks again for reading this far.

This blog post is an extension to our previous post on Excel Tip: Custom Conditional Formatting. I highly recommend you to read that post because we will be building on the same concept and take it to next level.

In this blog post the problem statement is simple:

1. Have user input for Sales or Spend.
2. If “Sales” is given, we want to highlight “Sales” values greater than 70.
3. If “Spend” value is given, we want to highlight all “Spends” values lesser than 30.

Let us see how to achieve this step-by-step.

Initial DataSet

This is the same dataset I used before for Excel Tip: Custom Conditional Formatting. Nothing magical about the dataset :).

Creating Filter Dropdown

We will try to create an Input element, in our example below I am creating a Label of Filter and adding a cell C1. Let us next add the dropdown.

With Cursor on C1, Select Data Tab –> “Data Validation”.

This brings the Data Validation Screen. In this screen, Select Allow –> List. Under Source, we will select our sources of values in C3 and D3. This will be for values Sales and Spend.

Now click “OK” on Data Validation screen. Now you will be presented with a Dropdown on C1 cell and selecting that gives us two values as below.

Starting Conditional Formatting

Now the process for conditional formatting is similar to the post from Excel Tip: Custom Conditional Formatting. Let us select the section C4-D9 region and select Home –> Conditional Formatting and “New Rule…”.

In our Formatting rule screen, let us add a simple rule to start with. Here we have entered a Formula of =IF($C$1=”Sales”, $C4>70). Also don’t forget to change the Format color before hitting OK.

This formula shows conditional format of Sales > 70 when our Dropdown value shows Sales. A sample output is shown below.

Interestingly, we have not yet written our rule for “Spend”<30. So Goto Conditional Formatting –> Manage Rule and Edit our Rule again. Here we have used the Syntax of IF(Condition True, then, Else(If (condition) true, then)).

The actual condition looks like =IF($C$1="Sales", $C4>70, IF($C$1="Spend",$D4<30)).

Click “OK” and get back to our C1 Dropdown. Select Spend and now you will see the formatting change.

We can extend this concept to a number of formatting options. I have found this really powerful use of Conditional Formatting and works like charm in almost most of the earlier versions of Excel.

Hope you are enjoying this series of Excel Tips. Keep locked in, we have many more coming. If you liked it, please drop a comment.

I hope you are enjoying the Excel Tips series I planned for this week. I got feedback from people to show some of the old tips I show them with Excel 2007 or Excel 2010 version. So here is a simple blog post which will show how to use custom Conditional Formatting with Excel.

So for this blog post, we will use a simple table. We have 3 columns and some values to play with.

Conditional Formatting

Highlight cells from C3-D4 and select out out-of-box conditional formatting options. In the below figure we have selected, Home Tab –> Conditional Formatting –> Data Bars. Hover over the various options to check how it looks in our table.

In below example, I have shown how it looks using “Orange Data Bar”.

When you want to have a gradient of colors, select the “Color Scales” option and choose one of the options. Below I have shown how data will look in a “Green-Yellow-Red” color scales. This is one of the easiest color coding where Red means Low values and Green mean high values.

Now these are basic out-of-box features. I hope you have used them in the past already.

Custom Conditional Formatting

With out-of-box options available, what if we wanted to color code our rows based on our requirement. Let me describe my requirement, I want to highlight rows who’s Sales value is greater than 60.

To achieve this, Select “Conditional Formatting” –> “New Rule …”.

This will bring up the rules dialog box. Here select “Use a Formula to determine which cells to format”.

As per the requirement we want the “Sales column” value to be greater than 60. So in our “Formula” bar, enter value of “=$C3>60”. Next Select the Format button.

This brings up the Format dialog as above. I have selected the “Fill” tab to fill the cells with Green color. Feel free to use your own color coding. Now the final – “New Formatting Rule” dialog looks like below.

Clicking “OK”, the table now highlights the rows with Sales value greater than 60.

How cool is that? Formatting can be quite powerful and we can add interesting extensions to this. Maybe I will write the same next. Do let me know if you learnt something new today. Thanks for reading this far.

Yesterday I wrote about Trace Precedents / Dependents and this blog post is an extension to what we have with Excel 2013. In this blog post we will see how to use “INQUIRE” tab and how it can be yet another powerful visualization to find our chain of dependencies inside Excel worksheets.

Enabling Inquire Tab

The first step is to enable the INQUIRE toolbar. This is not enabled by default. Open Excel 2013, in a blank document select “File” –> “Options”. We will be presented with the below screen.

Select “COM Add-Ins” and click “Go…”. We will get this mini dialog. Select the checkbox “Inquire” and click “OK”.

INQUIRE Tab

Once we have done the above step, you will find a new Tab added called as INQUIRE as you can see below.

In this blog post, we will show the following:

1. Cell Relationship
2. Worksheet Relationship
3. Workbook Relationship and
4. Workbook Analysis

We will use the same worksheet as used in Trace Precedents / Dependents.

Cell relationship diagram

This in a way is like our Precedent and Dependent diagram but is way more cool. This will show us the dependency like a fishbone diagram. With our cursor in “B7” cell, we are going to click on Inquire –> “Cell Relationship” button. We will be presented with the below screen. I generally like to keep the defaults and hit “OK”.

The output will look like below. We see this because we selected the “Initial number of expansion levels” as “Limited –> 1”.

We can expand it to the next level and we will be presented with a more detailed view. Here we are seeing B7 –> Dependent on B9, B70, D25, D15 and so on. How cool is this when compared to our Trace Precedents? We are yet not over. There is more.

Worksheet Relationship Diagram

Now this is new and an great extension. Here click on “Inquire” –> “Worksheet Relationship”. This will show us the dependencies of each of the Worksheets.

In our example above, it is showing the XLS sheet “Statement 2012-11.xlsm” has various sheets like Documentation, P&L Statement, CASH_FLOW etc. While it shows for example “Cost Cap” sheet is dependent on “IM Data”, “GL Data” etc which in this case are a different File and Sheet. Cool isnt it? We arent done yet !!!

Workbook Relationship Diagram

This is taking our dependency diagram to the next level. Especially for file like P&L data can come from a number of external sources. And this option shows how we are dependent on other external sources. Also it also shows if that file currently exists or not :). Cool right.

In our example, I selected “Inquire” –> “Workbook Relationship” to get a typical output like below.

In the above example we can see current XLS sheet is dependent on Northwind1.mdb (access DB), table.html (from web), and a number of other excel sheets. A “Red mark” indicates the object is not available now.

Workbook Analysis Report

This is a great summary of information that is available in our Workbook. It shows a number of information shown, I am listing some of them here.

1. Visible Sheets
2. Hidden Sheets
3. Formulas
4. Formulas With Errors
5. Formulas With Logical Values
6. Formulas With Numeric Values
7. Formulas With Date/Time Values
8. Formulas Without Cell References
9. Formulas Referencing Blank Cells
10. Formulas Referencing Hidden Cells
11. Formulas Referencing External Workbooks
12. Positive Formulas
13. Negative Formulas
14. Duplicate Formulas
15. Cells With Dependents
16. Cells With Comments
17. Cells With Validation Criteria
18. Cells With Conditional Formatting
19. Invisible Cells
20. Blank Cells
21. Hidden Rows and Columns
22. Named Items
23. Named Items With Errors
24. Warnings

And many more that I have not listed. The below diagram shows a snapshot of the same information for our Demo P&L Excel sheet.

Conclusion

Hope you found this blogpost interesting and you got to learn something new. This feature is part of Excel and worth a look any day. We will look at many more Office tips coming this week. Stay tuned on the series.

I am a big fan of Office products (Word, Excel, PowerPoint, Access and OneNote) and I always find it interesting to share my tips with customers almost every single day. I have found it tough to explain them over my blog till date except for one post (Excel Tip – Find invalid data). I am sure if you have played around with Excel 2013 there are loads of features to look out, I am not going to explain the common super cool features of PowerPivot, PowerView, GeoFlow etc. Those will be way in the future maybe :).

So stay tuned because this week will be “Excel 2013 Tips and Tricks” that I love to share with customers daily and now with you. Some of these features are already available in previous versions but they may look refreshing every single time. Incase you don’t find the option I am talking, it is an Excel 2013 feature.

Trace Precedents and Trace Dependents

In this blog post, we will look at the options available in “Formula’s” toolbar of Trace Precedents and Trace Dependents. We can will find these options:

Let us assume we have a P&L statement which is using tons of Excel references to different cells. In our below example, you can see that we have selected the cell “D13”.

This is a very big Excel sheet spanning multiple sheets and running close to 80-90 rows of cross references.

Trace Precedents

Now let assume you want to check which are the values that affect the value in cell “D13”. This is where “Trace Precedents” help. Click the same from “Formulas” toolbar.

You will now magically see an arrow (as above), this means the value in D13 is dependent on values coming from “B11” in our example. If there are other dependent values then we will get multiple arrows. Here is a typical example below:

Trace Dependents

Now the “Trace Dependents” on “Formulas” toolbar does exactly the opposite. This option shows which are the cells affected by values from this cell. In our example it is cell “D13”. Interesting part is, if we click the “Trace Dependent” again, it runs the dependency in recursion. It can give you an visual treat like below on values that can get affected because of “D13” (in recursion) in our example.

This is a powerful representation and can be quite useful too. To remove the arrows, use the “Remove Arrows” button from “Formulas” toolbar.

Conclusion

How cool is this? Did you find it useful? Have you used this feature in the past? Do let me know how you have used this feature in your daily life. More such fun coming your way this week :).