Awaiting New Year - 2011

I'm already stressed out this year. Already awaiting new year and started to count down. For all who are already exhausted like me, I created a file that counts down days, hours minutes and seconds to step into new year.

Just a preview of the file I created.

What are you waiting for? Go ahead click here and download the New Year Count Down file. I created this in Excel 2007. So, you can open thisin Excel 2007 and above. But if you are in Excel 2003 and you have a translator you can still use this.

Wishing you a very happy and prosperous new year in well advance.









Few techniques I used to create new year count down file are:

Go To Special series. Select visible cells only

Welcome to the nineth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are visible cells?

How often you filter the data in your spreadsheet? How often you hide some rows or columns from your data? I know your answer. Quite a number of times...Correct? If your answer is yes then read on and answer some more questions patiently.

How often you want to select and copy only the data you filtered and how often you want to select and copy only the rows / columns that are visible (excluding the hidden rows and columns)? This option is for you if you have failed copying this data and ended up pasting the whole set of data instead of only the filtered / visible data.

Go To Special series. Select Last Cell

Welcome to the eighth post of this series. Hope you are enjoying this series. Click here to read to navigate to go to special.

Select Last cell. Cell that has data and / or formatting. This is one of those most used excel option people often use.

Most often we need to go to the last cell in the worksheet we are working.

Go To Special Series. Select Precedents and Dependents

Welcome to the seventh post of this series – Select Precedents and Dependents. Hope you are enjoying this series. Click here to read to navigate to go to special.

What are Precedents and Dependents?

As we all know that a result of a formula or a function is derived using a single or a set of variables and / or constants. In short, result of a formula is an outcome of one or more constants or variables. Precedents are the inputs used to get the result. Dependent is the result itself. What does it mean? The active cell must be a formula. Yeah, I know, that’s obvious. But I wanted to mention..Just in case…

Say for example, A = 1, B = 2 and C = A+B = 1+2 = 3.

In this example, A and B are the Precedents. The value of C is dependent on A and B. Hence C is dependent.

Isn’t it quite possible that A and B are also dependent on some other variables and or constants? Yes, you got that right! It is quite possible. That is why Excel gave us an option of selecting Precedents and Dependents that are Direct only and All Levels.

First things first

When you have to find the precedents / dependents you need to first select the cell to which you need to select precedents or dependents.

Example

Go To Special series. Select Row and Column Differences

Welcome to the sixth post of this series – Select row and column differences. Hope you are enjoying this series. Click here to read to navigate to go to special.

I’m sure this is one technique that will be useful for auditors, reviewers, quality checkers (all these are one job but different tags called in different companies. There might be some more which I’m not aware!). In the companies, I worked before there used to be one preparer and one reviewer (for a four-eye check or a quick sense check before the report is sent to the stakeholders. Reviewers job is to make sure the values are correct as per the inputs received and all formulas are correct and end result makes sense. This is one technique I adopted when I used to review the data.

Row differences:

Row differences select cells that are different to active cell from all the cells you selected in a row. By different, I mean cell values if they are constants or the formula differences if the cells contain formulae. When one selects a range of cells from a row one need to be careful in selecting the active cell. By default, excel selects the first cell as active cell. To change the active cell in the selection press Enter or Tab. Ensure you have the correct cell activated after which you wanted to find the differences in a row.

If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell.

Keyboard Shortcut: Press F5, then Alt+S+W

Below is a random data I picked to illustrate the same.

This is a gross profit calculation for four fruits sold in a market per week (I’ve shown data for two weeks). Gross profit is Sales minus COGS (Cost of goods sold). In this example, for week 1, gross profit for Apple should be C25-C26 (=50). Think as a reviewer! Check the formula in cell C27. It should be C25-C26 (Sales – COGS). If it is fine, select row 27 in this example from columns C: F. Press F5, then Alt+S+W. You will select the differences in row due to formula. The selected cells are wrongly calculated cells. (Now it’s your duty to mark your preparer an error! Well, if you do not want to screw him just let him know about the error. Am just kidding!!)

Check below the table and formulas for gross profit in table. If you rightly audit the formulae, you will observe gross profit for Grapes and Orange is not calculated using sales and COGS of Banana and Orange respectively (E27 shows D25 – D26 where it has to be E25 – E26, and F27 shows E25-E26 where it has to be F25 – F26). Same is the case with gross profit for week 2 for Banana (Cell D30). Formula was carried forwarded from apple gross profit calculation.

R-C diffs

	A	B	C	D	E	F
23
24			Apple	Banana	Grapes	Orange
25	Week 1	Sales	100	200	400	200
26		COGS	50	100	200	100
27		Gross Profit	50	100	100	200
28	Week 2	Sales	80	120	1000	120
29		COGS	40	70	600	70
30		Gross Profit	40	40	400	50
31

Excel 2007

Worksheet Formulas Cell Formula C27 =C25-C26 D27 =D25-D26 E27 =D25-D26 F27 =E25-E26 C30 =C28-C29 D30 =C28-C29 E30 =E28-E29 F30 =F28-F29

Copy the above data and formulas to your excel and play around.

Select range C27:F27 (Active cell is C27), Go to special and select row differences. You will see E27 and F27 are selected as they are different from the row (Formulas pointing to different column unlike the active cell in your selection). Below is a visual selecting the multiple rows and finding the differences.

Go To Special series. Select Objects

Go To Special series. Select Objects

Click here to read to navigate to go to special.

In my current job, I work a lot on SAP BW. When I export a BW report to Excel, I find quite a lot of objects. Annoyingly enough, Excel processing slows down (Almost dead!) because of these numerous objects in the file. So, first thing I do after I export a report to excel from BW is select all the objects in the file and delete.

What are Objects in Excel?

Below picture I made to define objects in Excel.

Go To Special Series. Select Current Region and Current Array

Go To Special series. Select Current Region and Current Array

Happy "Dusserah" to all the readers. This Dusserah, may you be blessed with good fortune, Wealth and prosperity, Happiness. Wish you and your family a fun filled Dusserah!

Welcome to the fourth post of this series – Select current region and current array. Click here to read to navigate to go to special.

Define Current Region

The range of cells filled in with data and including the current selected cell or cells. The region extends in all directions to the first empty row or column. Selecting a current region is selecting an entire list of data.

Keyboard Shortcut: Press F5, then Alt+S+R or simply Ctrl+A or SHIFT + CTRL + SPACE bar

Go To Special Series. Select Blanks

Select Blanks:

Welcome to the third post of this series – Select blanks. Click here to read to navigate to go to special and to select comments or Click here to read to select constants and formulas.

Why Select Blanks?

I often use this option in Excel and I sincerely thank Microsoft for having this option in Excel. I must admit I hate to see blank cells in my data. It is quite common that we see blank cells in a large set of data, especially for people who download data from web and people who get a pivot table paste as values as his input for further processing. One might want to get rid of blank cells or put a constant (Sometimes, I make blank cells as zero) or fill the blank cells with a common formula etc.

Bear in mind to select a range of cells while selecting blanks or else by default excel selects all blanks in the used region. I mean by used region, from A1 to the last used cell.

Navigate to Go To Special - Blanks

Click here to learn navigation.

Keyboard Shortcut: Press F5, then Alt+S+K.

Go To Special series. Select Constants and Formulas

Select Constants and formulas:

Welcome to the second post of this series – Select constants and formulas. Click here to read to navigate to go to special and also to select comments.

What are constants and formulas?

Well, constants are values that are unchanging in nature. They are not dependent on any other constant or variable.

Formulas on the other hand are derived based on the value of referred cells. Formulae copied and pasted as values are constants as well for selectionJ. Having said that, let’s explore the options to select constants and formulas by using “Go to special”.

Data types of constants and formulas can be number, text, logical and error.

Number can be any numeric value

Text can be any text (string) value.

Logicals are TRUE and FALSE.

Errors are all the error types in excel. #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

Tip: If you select a range and then go to special and select your criteria, it selects the criteria only from your selected range.

Go To Special Series. Select Comments.

Welcome to "Go to special" series of posts. Let’s explore “Go To special” and select Comments option in excel today.

Years ago, when I was novice to excel, I filtered some data in excel and copied. But when I pasted the data into a different sheet for further analysis, I observed I pasted complete set of data instead of just the filtered data. It was some thousands of rows. I was disappointed thinking I have to copy each row of data one by one. My team manager observing me from a corner (May be he was writing some points for my appraisal looking at my behaviour on floor!!) walked up to me and understood my problem. He kept his hand on my shoulder, pressed F5 on keyboard and clicked on “Special…” button when he saw “Go To” dialog box. This is the first time when I saw “Go to Special” dialog box in Excel. Thanks to my boss for showing me this technique and Microsoft for giving this option in Excel. This option in excel we are exploring today.

On a day to day basis, we might come across various scenarios where we want to select a specific set of data in a given large set of data based on various criterions. Go to special gives us that option to select data based on various criterion.

“Go to special” gives us 15 criterion's to select data. I’ll be posting this as a series. In each series, I’ll be exploring one criterion.

Navigation/keyboard shortcut to get to “Go To Special”

Excel 2003 navigation: On toolbar, click on Edit and scroll down to click on “Go To”. See a dialog box like below.  Now click on “Special” button highlighted on yellow in the below snapshot to see the “Go To Special” dialog box.

Excel 2007 navigation: On the extreme right-hand side of the ribbon you see Find and Select option and click on it to see “Go To Special” like in the below snapshot. Now click on “Go To Special” to see the dialog box.

Keyboard shortcut in Excel 2003 & 2007: Ctrl+G Alt+S or Press F5 and Alt+S. You will see the dialog box like below. This is the starting point for our exploration.

Duplicates in data? Excel is a better option!!

Here’s my first post for my blog. I’m so excited.

I’ve chosen this topic “Duplicates” as my first topic. It is quite common that we work with data that has duplicates. It is not always common how we treat duplicates each time we come across. Thankfully Excel has inbuilt tools and functions to figure out duplicates. I want to share the ways I use to deal with them.

There are times when you want to:

            1.  Identify duplicates in data

            2.     Keep only unique data

Explore Excel

Hi All,

Welcome to the board!

"Explore Excel" is what I've decide my blog name is. This is the place where I want to share some of the excel tricks and techniques I use. This is the place where I want to learn tricks and techniques from you all power excel users.

This is the place where we together "explore excel".

Cheers,
VaraK
"Explore to Excel, Excel to Explore"