28 Jun
Some Excel users, especially new comers, may think that you can only use one Excel function at a time in a cell. But the fact is that you can join as many functions as necessary in a cell. You can even nest multiple functions within each other to achieve a certain formula. Let us take [...]
Posted in Formulas, Functions by: admin
No Comments
25 Jun
If a picture worth a thousand words as the saying goes then a chart could be a good proof of this.
Excel has a chart wizard that provides the user with a variety of chart types and many options to customize those charts. So in this post I will not get into the details of [...]
Posted in Charts by: admin
No Comments
22 Jun
If you have a number of workbooks which you usually open together at the same time, then you can save those in a workspace file. This will allow you to later open all workbooks by opening the workspace file. You will also be able to resume work with the same window sizes, print areas, screen [...]
Posted in General by: admin
No Comments
19 Jun
The HLOOKUP function (short for Horizontal Lookup), searches for a value in the first row of a table array and returns the corresponding value in the same column from another row in the table array.
Syntax
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value:The value to search for in the first row of the table array.
Table_array:Two [...]
Posted in Data, Functions by: admin
No Comments
16 Jun
In a previous post I explained how to validate a cell using a fixed range of data. In this post we will see how to validate a cell based on the value of another cell.
Suppose we have a list of documents each with issue date and expiry date. We want to validate the expiry date [...]
Posted in Data, Validation by: admin
No Comments
14 Jun
If you have a column of text like this:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
And you want to put this data in a row like this:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Instead of moving the data manually, you can use an option of the Paste Special command as follows:
Select the data in the column.
Right click and then select copy.
Select the first cell in the destination row.
Right click [...]
Posted in Data, Formatting by: admin
No Comments
12 Jun
When you save a workbook (or any other Microsoft Office document), some of your personal information like Author, Manager, Company and Last saved by are saved as File properties. If you want to share your workbook with others, then you may want to remove this information before you distribute it.
There are two things you can [...]
Posted in Security and Protection by: admin
No Comments
10 Jun
If you want to preview a workbook in Windows Explorer without opening it, then you have to save the workbook with a preview picture.
This is how you do it:
In the File menu click Properties. The Properties window will open.
Click on [...]
Posted in General by: admin
No Comments
08 Jun
When you open an Excel workbook, by default Excel provides three worksheets named sheet1, sheet2 and sheet3 respectively.
You can change this number to your needs as follows:
On the Tools menu click Options.
On the Options window click the General tab.
Next to [...]
Posted in Customization by: admin
No Comments
05 Jun
Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().
However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.
This is OK [...]
Posted in Visual Basic by: admin
No Comments