Excel (By S. Van Eenoo)

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

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

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

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.

Read the rest of this entry »

Read the rest of this entry »

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.

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

Enjoy,

Stefaan

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

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

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

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

Hi,

First day of the month

In this example the assumption is that you already know the funtions : Date, Year, Month
In order to calculate the first day of the month we need to combine Date, Year and Month.

The cell A1 contains the current date.
Year and month will extract the year and month of the current date.
The only thing left is the day. Read the rest of this entry »

Hi,

Some days ago I was surprised to find a link to this blog on http://spreadsheetpage.com/index.php/excelfeeds/

This site contains information about books and Excel-related products, plus lots of free tips, downloads, and other stuff for Excel mavens. Explore the menu at the top, and dig around. There’s an excellent chance that you’ll find something you didn’t know.

This site is owned by John Walkenbach.

Enjoy,

Stefaan

Hi all,

In Microsoft Excel the names of functions depend on the language of the installed version of MS-Office.
It can be quite a pain to find out what’s the translation of a particular function in your language. For example if you’re used to the English functions and have to work in another language.

I have developed a tool which might speed up the process of finding the translation.

Feel free to download it here. translator

Regards,

Stefaan

Excel has two date systems, but is defaulted to the 1900 Date system. This system however can not handle negative hours.

1900 Date system

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900.

1904 Date system

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Microsoft Excel converts the date to the serial number 34519.

The difference between the two date systems is always 1462.
1462 is equal to four years one day (including one leap day).

Which years are leap years?

In the Gregorian calendar, which is the calendar used by most modern countries, the following rules decides which years are leap years:

1. Every year divisible by 4 is a leap year.
2. But every year divisible by 100 is NOT a leap year
3. Unless the year is also divisible by 400, then it is still a leap year.

In this example we will calculate whether the employee did overtime or not.

 As you can see, 34:30-38:00 results in a bunch of hashes.
This is because the 1900 Date system can not handle negative hours.

Excel can only display negative times by using the 1904 date system

How can we correct this ?

Read the rest of this entry »

In this example we will add times. The result will be greater than 24:00 hours.
By default, Excel displays 24:00 as 12:00, 28:00 as 04:00

In the above example, the actual hours worked would be 38. However, the format being used does not allow Excel to display elapsed hours.
Instead Excel displays the result of the subtraction 38:00-24:00.

How can the be solved ? Read the rest of this entry »

In this example the maximum value will automatically display a green arrow instead of the standard marker.

At the end of this article, you’ll find the demo file.

How is this done ? Read the rest of this entry »

These following shortcuts are included in the complete list from post http://excel.vaneenoo.eu/?p=24.

These are the shortcuts I use all the time:

• CTRL+”*” : Selects the current region
• CTRL+”+” : Inserts the same number of selected rows/columns
• CTRL+”-” : Deletes the selected rows/colums
• CTRL+”;”  :Inserts a snapshot of the current date
• CTRL+SHIFT+”;” : Insert a snapshot of the current time
• SHIFT+F11 : Inserts a new worksheet before the active worksheet
• CTRL+”`” : Shows the formulas of the worksheet
• CTRL + pg up : Navigates to the previous worksheet
• CTRL + pg dn : Navigates to the next worksheet
• CTRL + F4 : Closes the active workbook
• ALT + F4 : Closes the active application

Ever wanted to past data (Table, Pivot Report, etc.) as a picture ?

When you follow the menu Edit, you’ll notice that no options are available to paste the copied data as a picture.

How can we paste data as a picture ?

Read the rest of this entry »

Tip: the function char works with any number between 1 and 254.
Tip: Instead of using the function Char, try pressing and holding Alt and type 0153. Use the same number as you would with the function Char, but add a leading zero.

Use custom number formats to create you own code 

• To add leading zeros.
• To make the input of phone numbers easier
•  To make the input account numbers easier
• To add text to values, and still be able to calculate with them
• …

Please find enclosed a word document (7 pages) whichs explains custom cumber formats, and an Excel file with some examples. 

Custom Number Formats.Doc

Custom Number Format.XLS