Excel (By S. Van Eenoo)

How an Excel Expert can Help Your Business?

kalyan — Tue, 19 May 2009 04:35:30 +0000

How an Excel Expert can Help Your Business?

I have spent a number of years learning Excel - its Formulas, Pivot Tables, Macros, and a variety of time saving and insight generating tools and have become an Excel Expert. I have painstakingly gone through and learnt all the tools in Microsoft Excel. So I bring with me high productivity so you excel and get your stuff done fast and save cost.

Much of info you use is in Excel

We use it to organize your data into rows and columns. We also use it to perform mathematical calculations quickly. Microsoft Excel is easy to use and has very many features so the more you use it the more you learn. The Excel spreadsheet has great documentation through the built-in help menu.

Each feature in the menu has a unique attribute to help the user in understanding it. Each tab defines its own presence and each button helps itself with alternative text on it. The basic formulas has made very simple, where respective formulas are stored in respective function kit. The user has just to choose what function he has to apply and the formula is ready!

The aligning of tables, inserting the images, selecting the charts, shapes and many more are now in just one click away.

Working with database has become extremely easy. Just import the data from access and here we go.

A novice can use excel but they use it like a type writer

Yes, a novice can use it, but to use it with EXCEL an Expert is needed. A Novice can find excel useful. It will be more attractive to those who are having basic knowledge of Excel and Word, which includes procedures for entering equations in excel and some experience with creating graphics. An Expert can make the things differently. Expert defines himself by using excel effectively and professionally. There is a fringe which defines these both. How to use Excel is Novice and how to make use of excel is Expert.

An Excel expert means someone who knows excel and business logic

Knowing the very end of excel will not make him an expert. A combination of expertise in subject + Business Logic will make him exceptional in the market. Business logic generally used to describe the functional algorithms that handle information exchange between user interface and database. It is distinguished from input/output data validation and product logic.

An expert can give you productivity by saving cost and time

Save .. Save ..Save .. Excel expert can save the time n cost by using the formulas, Pivot tables, Vlookups etc., will definitely help you in giving you the productivity. Instead of same old time consuming process they can make budgeting in to much more intellectual exercise with just few simple templates.

Conclusion

I conclude that, hence an expert with vast exposure in any professional field will definitely make his presence felt in this competitive world.

For any queries mail me at chaitanya.sagar@p2w2.com

Navigating Excel sheets

Stefaan — Mon, 09 Feb 2009 07:14:03 +0000

Hi,

A workbook very often contains a lot of worksheets, which makes it hard to easily find the correct sheet.

Moving the sheets by left clicking the arrows on the bottom left corner is an option, but not the best choice.

Try a right click on the arrows . A nice overview of the worksheets is displayed.

Left click on the desired worksheet name, in order to jump to that worksheet.

Enjoy,

Stefaan

Move buttons on a toolbar (Shortcut)

Stefaan — Mon, 09 Feb 2009 07:07:52 +0000

Hi,

In order to move a button on a toolbar, press and hold the ALT key, whilst dragging the button of your choice to the desired location.

Enjoy,

Stefaan

Vlookup and Match (Matrix Lookup)

Stefaan — Sat, 24 Jan 2009 09:58:22 +0000

Purpose

Vlookup can only perform a vertical lookup.

By nesting these two functions we can perform a vertical and horizontal lookup at the same time.

Workinstruction

The instructions apply to the file that you can download here.

Match

Returns the relative position of an item in an array. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
In this example we want to find the position of Carol in the list.

Select the cell B9
Click the button
Choose the function Match.

Click the button .
As required, complete following fields as shown in the screenshot below.

Field name	Value
Lookup_value	Is the value you use to find the value you want in a table.
Lookup_array	Is a contiguous range of cells containing possible lookup values.
Match_type	If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on. If match_type is omitted, it is assumed to be 1.

Vlookup & Match

In this example we want to determine the license fee for 20 users, for the product Office 2000.

Select the cell D2
Open the function Vlookup.
As required, complete following fields as shown in the screenshot below.

Since the Vlookup can only perform a vertical lookup, we’ll use the number of users as the lookup value.

We used the value 2 in the field Col_index_num, because the the license fee for Office 2000 is in the second column of the Table_array.

Although this would result in the correct license fee for this example, it will not be correct if we change the Product.
When the product changes, the result can be found in another column, hence the Col_index_num should change according to the product.

We need an automated manner to determine the Col_index_num, to determine in which column the product can be found.
In other words, we need to determine the position of the product.

This can be done with the function match.

Replace the value 2 in the field Col_index_num with the function Match, as shown in the screenhot below.

Chart with two value axes

Stefaan — Sat, 24 Jan 2009 09:42:20 +0000

Purpose
When a range of values for different data series in a chart varies widely, or when you have mixed types of data (such as price and volume), you can plot one ore more data series on a secondary value axis.
The scale of the secondary axis reflects the values for the associated series.

Workinstruction

The instructions apply to the file that you can download here.

Two value axes – three series

When you have more than two series, Excel isn’t sure which serie uses which Value Axis.

In this exercise, we will manually correct the chart.

Select the range A12:D17
Click the button
Click the tab

Select the Chart type Line – Column on 2 Axes
Continue with the Chart Wizard as you have learned in the previous procedure.

Notice that is using the first Value Axis, which is incorrect.
schould use the secondary axis, and the chart type should be Line.

Two actions are required:

Change the chart type for the series
Plot the series on the secondary axis.

Change the chart type for the series

Right Click the series
Choose
Select the chart type as shown in the screenshot below.

Click the button .

Plot the series on the secondary axis

Right Click the series
Choose
Click the tab
Select
Click the button

Shortcut to paste as value (one cell)

Stefaan — Sun, 07 Dec 2008 13:02:30 +0000

Hi,

This trick is only valid for one cell.
As far as I am aware, there is no trick to do the same for a range of cells.

Select the cell
Hit F2 key (=edit the content of the cell)
Hit F9 key (=paste as value)

Enjoy,

Stefaan

Disable Printing in a Workbook

Stefaan — Wed, 26 Nov 2008 10:10:41 +0000

Hi,
in order to disble printing in a specific workbook, please continue with the steps as described below.

Open the Visal Basic Editor (ALT + F11)
Double Click “This Workbook” in the Project Explorer
Hit CTRL + R if the Project Explorer isn’t shown

Paste this code
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox “Printing has been disabled”, vbOKOnly, “Information”
End Sub

Enjoy
Stefaan

Convert decimal numbers to “hours”

Stefaan — Wed, 19 Nov 2008 12:40:59 +0000

Hi,
Very often, in time registration, decimals are used to indicate hours.
e.g. 1.5 instead of 1:30
This is in fact incorrect. Excel translates 1.5 into a date.
1.5 is in fact 1.5 days after 01-Jan-1900.
When you apply a date format to a cell containing 2, this would return 02-Jan-1990

Back to the problem. How Can we convert decimal numbers to hours ? e.g. 1.5 = 1:30

You can download the file here.

convert_decimals.xls

Enjoy,
Stefaan

Microsoft Excel function translations - updated

Stefaan — Mon, 17 Nov 2008 20:29:31 +0000

Thanks to the contribution of Zoltan Till, I was able to update the Function Translation file with the Hungarian translations.

His contribution is much appreciated, and I sincerly hope some of you will benefit from it.
You can download it here. translator1

Cheers,

Stefaan

Year Team Calendar (Whereabouts)

Stefaan — Fri, 14 Nov 2008 14:38:36 +0000

Hi,
Last week I found an excellent Year calendar via http://www.spreadsheetpage.com.
It was an Excel book that created a year calendar.
This was very sofisticated. My needs however were slightly different.
I was in need of an overview by month for all my team members’ whereabouts.(yes I know, webbased solutions,blablabla)
Creating this file one time is fine, but I didn’t want te recreate the same file over and over again each year. (Yes, I am easily bored).
Hence the creation of this Year Team Calendar. You can download it here. year_Team_Calendar.zip

Enjoy,
Stefaan