I was working on MS Access and had to export a table to an excel workbook. This happened nice and fast. Then, I opened the workbook and made some changes and when I closed the workbook, I found another file in that folder. See below:

This is something new to me. The new file created was of the same size as the original size and it said that it’s a backup file. I did not want that to happen and so, I searched and found out things about XLK files.

The backup file that was created was an XLK file. XLK file extensions are associated with MS Excel and not MS Access, as I initially thought. These files are used to create backups of the normal XLS or XLSX files. These backup files carry the data and formatting that the originals had just before they were saved for the first time.

It is not easy to create such files in MS Excel as this option is not a default one. However, when a new excel workbook is created from MS Access, the file is saved with an optioned enabled, which creates backups automatically when it is saved.

Now, how to create a MS Excel workbook which will keep on taking backups? Here is it, for computers running MS Office 2007.

We need to save the file once more, this time, choose ‘Save As…’ option, instead of a simple Save. When you have the ‘Save As…’ dialog box open, click on the small down arrow in the Tools button and choose ‘General Options.’

In the new window that appears, check the option ‘Always create backup’ to enable automatic backing up of your MS Excel file.

Now, when you open this file, make some changes and save it, the backup file, with the XLK extension will automatically be created. However, this option does not change how your MS Excel behaves, but is specific to that one file, in which you have enabled this option.

If you have MS Office 2003 installed in your computer and you want it to create similar XLK files when you save them, click on File, go to ‘Save As…’ and then go to Tools and finally you will find ‘General Options’ present in there, where you can check the ‘Always create backup’ checkbox.

While searching, I landed up in a website, which felt nice. It started with saying:

Backup your files, always take backups Three things are certain: Death, taxes, and lost data. You can put them off but you can’t eliminate them. The only difference between a good hard drive and a bad hard drive, is that the good hard drive hasn't crashed yet.

Check this out. They talk about the advantages of backing up your data.

Cheers!

Automation using Excel macros is finding its deserving position amongst many businesses. This article talks about the benefits that automation using Excel macros brings to a business.

---

Excel macros have proved over the last few years that technology driven automation of regular and repetitive works can reduce the turnaround time, increase productivity and will always ensure better quality and hence, benefit business.

In reporting and analysis sector, we come across huge data that has to be processed, analyzed and employed daily to get analysis and reports that impact our business. There exist huge amount of repetitive work that is also time taking, prone to manual errors and in terms of quality are nothing but a waste or Muda. Automation of these reports can complete the entire process quickly and hence, can easily reduce the turnaround time. Excel macros are the perfect automation tools that we can get in a reporting and analysis environment. They can help us not only in getting the process done quickly, but also can eradicate human errors. With zero human errors we do not waste time on re-work and hence, a lot of direct and indirect resources are saved. This has a direct impact on our business.

Excel macros show us how technology benefits business. This technology has changed the way one used to look at raw data that has to be analyzed and converted into useful business information. Loads of data can now be processed within minutes using Excel macros and the analysis becomes easier, with zero error and better quality data. These macros can be run from any machine and by anyone and a business utilizing this technology can reduce the overall headcount too. Automation using Excel macros can do the job otherwise manually done by many employees, with a quality output, zero errors and within a faster turnaround time. When the cycle time is reduced and you do not need to worry about doing a re-work on the project, you can rest assured that this will technology will benefit the business in both monetary terms and in gathering better customer satisfaction.

Hi Friends,

How is life?

Ever wanted to get the Age of a person in Excel? Yes, often we come across a situation when we need to calculate that. I am sure using Excel VBA, it will be very easy; however, I will show you a way to get the results, in Years, Months and Days without using VBA. Sounds good, right? OK then... Check this out:

We need to know the date of birth of the person to calculate his or her age, right? Given that the date of birth of the person is in cell A1, we can use the following formula to calculate his age in excel:

=DATEDIF(A1,TODAY(),"y") & " years " & DATEDIF(A1,TODAY(),"ym") & " months " & DATEDIF(A1,TODAY(),"md") & " days "

We might use this formula while calculating the age of a person in excel 2000, 2003, 2007 and even 2010. The same formula works. I like this, because it uses a function DatedIf which we normally do not use; and all of it is done without using VBA.

Try this out and let me know...

Behind everything is in Excel there is a secret number which is stored in the background - be it text or date or number. So it's like deception in front of your eyes... :)

1. All Characters

Try this in any worksheet cell -- = code("A") and the result is 65 and when you enter a function = Code ("a") you get result 97. Why?

Because: Every font set has 255 characters. Every character is represented by number. It's called the Unicode character set. It's a character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) – 255 Characters.

In this 255 character set - there are 32 non-printable characters which represent machine language. 7-bit ASCII code - values 0 through 0- 31 – Non Printable characters.

2. Date and time

They are stored as sequential serial numbers so that they can be used in calculations.

By default, January 1, 1900 is serial number 1, and January 1, 2010 is serial number 40179 because it is 40179 days after January 1, 1900.

Microsoft Excel for the Macintosh uses a different date system as its default.

Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date.

Eg: the serial number 0.5 represents the time 12:00 noon.