casinos

Posts Tagged ‘Office’

Excel Functions: ROMAN, ARABIC and FORMULATEXT August 16th, 2013

Vinod Kumar

If you a power user of Excel, then there is no way you could have lived without tons of awesome functions available with Excel. In this blog post, let me talk about three such awesome functions which are simple yet powerful. These are not the traditional function’s but are used for special purposes. The functions are as below:

ROMAN Function

How many have wanted in creating your kids homework to convert numbers into Roman numerals? Here is the magic function ROMAN. Below are the options available with ROMAN function. Do play around with them to know the differences.

In the below example, I have used the “0 – Classic” option and the output can be seen below.

ARABIC Function

This is an interesting function. This is the reverse of what we did before. Assume the situation where-in you want to convert from Roman numerals back to normal numeric? Now that is the function to take a look. It is ARABIC, pass it the Roman numeral and you will get the integer value.

Formulatext Function

Now this is not part of the ROMAN and ARABIC function. But when playing around with Excel, sometimes I need to show the formula that is behind the cell. Many a times I use F2, copy the formula and paste it with an apostrophe (‘). There is a even better way to do this – it is via the function FORMULATEXT. A typical use of FORMULATEXT is shown below.

I plan to write many more Excel formula in future. Do let me know if you want me to cover any of them in particular. Will line them up !!! Thanks for reading this far.

Continue reading...


 

Excel Tip: Shortcut with Tables / Regions August 12th, 2013

Vinod Kumar

Last week I wrote about Copy Values from Formula cells and a number of folks had expressed interest in knowing more shortcuts when working with Tables. This blog post I will show some of these commonly used shortcuts for Tables inside Excel. I am sure there are many more available with excel that you would like to share with our readers, please feel free to drop a line under comments section.

For this example, I am going to use a simple table as shown above. Now our cursor is in C4 cell and we will play with a number of shortcuts next.

Select Cells one-by-one

This is a basic shortcut, If you need to select cells adjacent to C4 one-by-one. Use SHIFT + Arrow. We can use the up arrow, down arrow or the side arrows to select in the directions we want to select cells.

Select Cells till the edge of region

Moving cell-by-cell can be time consuming. So if you want to select till the edge of the row. We can use SHIFT+CTRL+Arrow key. Below we used the right arrow key to select till the edge.

Select current Line

If above was a neat trick, then what will be the shortcut to select the complete row. That would be SHIFT+Spacebar. From C4 cell you can see we have selected the row edge-to-edge in the example below.

Select current column

Now to select the complete column edge-to-edge of values. the shortcut would be CTRL+Spacebar. From C4, the selection would look like below.

Select current REGION

Now for a strange reason you want to select the complete region. We can combine, the shortcut of row selection and then column to get the same effect. Even better combination is CTRL+A.

Hope these shortcuts will be useful when you play with Tables / regions inside Excel next time. Do let me know if you have alternate shortcuts for the same.

Continue reading...


 

Excel Tip: Copy Values from Formula cells August 7th, 2013

Vinod Kumar

I show a number of Excel Productivity to my customers every single day. One of the task includes getting some random number to show some charts or some formulae functions. I have used the RANDBETWEEN() function in the below example as an dataset. Assume we have a this range of numbers and now we want to copy ONLY the values from this range?

If you try to do anything with this Excel sheet, the random number is automagically generated. Not something we wanted :). Now the task is to copy ONLY the values into a different column.

First step is to Select the range of cells we want to copy. This can be any range in your workbook to start with. In our example we are using B2 to B6 as shown in figure below.

With the range selected. Move your mouse to the left side of the box. Do you see the “Cross Arrow” in the above figure? That is the trick.

Once you see the Cross Arrow, with your mouse in that over the box –> Right Click and Drag your mouse. You will see a range now where the values will land. In the below figure you see the D2:D6 for example. Now is the time to release the mouse.

Voila, now you will get an awesome popup inside Excel to play with. This gives us interesting options. Feel free to play with them as you wish. For our example, we wanted ONLY values, so I choose the 3rd option.

Now that lands the values, without the formulae into these cells. As you can see the values are copied, since we used the RANDBETWEEN() function – the values in our old cells are now regenerated. This is by design.

Our final output looks like above. You can use this technique in ANY range that contains range with formula. Do let me know if you used this trick anywhere in your Excel explorations.

Continue reading...


 

Excel Tip: Table’s Row-Column Shortcuts July 30th, 2013

Vinod Kumar

I work with tons of tables inside Excel and sometimes there are requirements to do some simple but repetitive activities on a table that I have always wanted to automate. These tasks include inserting a row, inserting a column, deleting a row, deleting a column, hiding a column or row etc. So this blog post will be all about these simple tasks. Our dataset looks like one below.

Inserting a Row and Column

This is one of the easiest shortcut to remember.

To insert a row at current location: ALT + I + R (like (I)nsert + (R)ow)

To insert a column at current location: ALT + I + C (like (I)nsert + (C)olumn)

Our excel will show an output like below.

Deleting a Row or Column

If adding was this easy. Now we might want to delete rows ad columns too right? There are two mode for me. Here is the lengthy route. If you press ALT+ E + D, you will get the following dialog.

Now, using the dialog box can be easy. We can still use some of the hotkeys on the dialog. Check the row and column. So the lengthy route to:

Delete row: Alt + E + D + R + Enter

Delete column: Alt + E + D + C + Enter

I have already calling this as lengthy approach. So there must be something simpler right. You guessed it correct. Use the below shortcuts.

Delete row: Shift + Spacebar + Ctrl + -

Delete column: Ctrl + Spacebar + -

That seems to be slightly lesser keystrokes right? Found it interesting? Let us move to the next shortcut.

Hiding Row or Column

What we want to achieve is to hide column D and Row 12.

So here are the shortcuts for the same.

Hide a Row: CRTL + 0

Hide a Column: CTRL + 9

The last part of this tips would be, to unhide the row or column – select the rows and right click to Unhide. Do you know of a shortcut to unhide columns or rows? Drop a line and let us learn from each other. Hope you enjoyed this tip today.

Continue reading...


 

Excel Tip: Compare two lists (Method 3) July 19th, 2013

Vinod Kumar

This is in continuation to our series where we want to compare two lists and find the difference. In previous posts we discussed the use of MATCH() and then VLOOKUP() functions. In this post, I will use the other popular method of PIVOT TABLES. Just to reiterate, we have two lists “List A” and “List B” and we want to know how many values in List B is not available in “List A”.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PIVOT TABLE.

For PIVOT Table, I am going to prepare the above two lists into one mega list. I have added a column to denote Year and merged both these lists like below.

Now that our data is prepared, our next step is to initialize the PivotTable. Goto INSERT Tabe –> PivotTable.

The Create PivotTable dialog comes up. Under Select a table or Range, select our complete table. Under PivotTable Placement-> Select a cell in our same worksheet. I have shown a typical example below.

To aggregate Drag the corresponding columns to get the result I have shown.

Columns –> Year

Rows –> Company

Values –> Revenues

Voila, our task is complete. Now in one shot we have got, the red empty boxes are the new values in 2012 that are not available in 2011. While the blue box below represents, values in 2011 that are missing in 2012 year.

Thanks for reading this far. I hope this was useful and you learnt something new. Do let me know if you want me to write some other topic.

Continue reading...