Making Excel Useful

25 May 2010 by Ivan Radenkovic in Excel | Leave a comment

In too many cases people misuse Excel and they are not even close to realize that. From beginners euphoria about Excel as ultimate tool they end up drowning in Excel hell. At this point they blame Excel for everything: working too hard, loosing themselves, giving the wrong figures – or even worst several different but correct figures… After some time they do not know who to trust and as instinct command they start looking who to blame for their situation.

Let’s face the ultimate truth: Excel cannot be good or bad, only the way you use it can be good or bad.

Share on Twitter
Read More

Business Intelligence, [Borg] Cubes and Users

24 May 2010 by Ivan Radenkovic in Business, ERP, Excel | Leave a comment

I have never thought about myself as a salesman. Simple reason is that person cannot be the best at everything. We have to choose. My choice was technical skills. Not sales ones. I cannot imagine myself selling crap products (if you are a salesman you often cannot afford to choose). I was prefer to be the one making product and assure no crap product will be produced with my participation.

I got business intelligence project now and my manager told me I have to sell it to the management. Ok, I will try to do that, but honestly I’ve never 

Share on Twitter
Read More

MS Office: Link Excel to Access

22 November 2007 by Ivan Radenkovic in Business, Excel | Leave a comment

Run Icon

or on any other data source. Ms Access ability to process large amount of data may come very handy in many cases. Of course, you can perform those analyzes in Excel itself but doing it using Access is both much more comfortable and error-proof. Excel is irreplaceable when you should check less than hundred rows of data but when row numbers came to several hundreds and more possibility to make errors raise exponentially. At the other hand, Access operate with whole columns and it make this job easily as possible. On this post it will be explained how to link Excel on Access, but you can use the vary same method to link Excel on any other data source like text files, xBase, ODBC…

Share on Twitter
Read More

MS Excel: ISERROR Function

2 November 2007 by Ivan Radenkovic in Excel | Leave a comment

Excel IconIsError function is used to check for errors in expressions or cells. At first sight most usefull case when you will need to use this function is to suppress division by zero error automatically. If not, you can always check for #DIV/0! cell by cell – but it would be inappropriate use of all this comfortable technology. This can’t be done without IF function which one is described in previous post on this website right here. You can save lots of your time resources and energy just by using combinations these two functions.

Share on Twitter
Read More

MS Excel: VLOOKUP and HLOOKUP Function

19 October 2007 by Ivan Radenkovic in EN, Excel | 7 Comments

Excel IconExcel’s VLOOKUP and HLOOKUP function, which stands for vertical and horizontal lookup, is used to search for wanted information stored in sheet which could be in same or different XLS file.

If you have sheet “order” with enlisted items and sheet “prices” with items price list you can search for the prices of ordered items to calculate total amount of order.

If you have sheet “overdue” with enlisted over debt partners for sending warning letter and you have sheet “partner master data” with full partners addresses, you can search for selected addresses using these LOOKUP function.

Share on Twitter
Read More

MS Excel: IF Function

18 October 2007 by Ivan Radenkovic in EN, Excel | 1 Comment

Excel IconVery useful function in Excel is this one. It tests a value in a specific cell to see if that value meets a certain condition. If condition is true, first result will happen, if false, a second result will happen.

It looks complex (especially combined with VLOOKUP – my favorite one) so many people avoid using it. It was complicated to me in beginning but after some time I’m stopped to notice it complexity. Now, its use is so mechanical, I’m not even think much of it when I’m using it.

Share on Twitter
Read More

Excel 2007 Error!

8 October 2007 by Ivan Radenkovic in EN, Excel | Leave a comment

Excel IconBe alerted to an issue in Excel 2007 (and Excel Services 2007) which involving calculation of numbers around 65,535. The first example was =77.1*850, but it became clear from testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but testing showed that this really didn’t have anything do to with multiplication – it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.

Share on Twitter
Read More

Slow opening Excel files through explorer

1 August 2007 by Ivan Radenkovic in EN, Excel | 4 Comments

Excel IconYou can open Excel and within Excel you can open XLS files, but not with double click on XLS file in windows explore. This is not a solution for the issue above, it’s bypass with some side effects. You have to disable DDE for selected file type and to pass XLS filename to Excel as parameter. Side effect is with each doubleclick on XLS file you will start new instance of Excel but using existing one. This will affect windows ability to group similar buttons on taskbar.

Share on Twitter
Read More