How to Create Index Sheet Hyper-linked to Other Sheets

Some days ago, I was working with some data which was distributed in about 18 sheets within a single Excel file. I was supposed to make calculations and then present that file to senior management. Now presentation was important as well. I must had made it easier for others to go through data. First thing i did was obviously name all the sheets so that they speak for the nature of data contained within. Next thing was to insert another sheet at start and create links from that sheet to all other sheets in the file.

To solve above problem, I created an ‘Index’ sheet. But its not easy to add hyper-links to 18 other sheets manually. It would have taken a lot of time and energy as well.

I wrote a small code snippet to create index file using VBA.

Simply open VBA editor (ALT+F11) and paste below function there. Run the macro ‘createIndex’ from Macro list. It will create a new sheet named ‘Index’ at start of the document. Names of all the sheets are copied to newly created sheet and those names are linked to the sheet as well. Please make sure that when you run this macro, there is no sheet named Index already.

Sub createIndex()
Dim i As Integer
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "Index"
counter = Sheets.Count
i = 2
Do
Range("A" & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
i = i + 1
Loop Until i = counter + 1
End Sub

Keyboard shortcut for Autosum option in Excel

Working with Excel in office environment extensively needs you to work with keyboard. You need to be quick to be productive. Time and motion study proves it that making use of keyboard shortcuts reduces time required to accomplish a task. You must learn the short cuts for the tasks you use more frequently.

Autosum option in Ribbon

Probability is quite higher that you use autosum option quite frequently. When applied, this deploys SUM function on selected cell such that cells above that are added. If there is no value above, preceding cells in the row are checked and sum of the values is returned.

hold ALT and press +

This task can be done quickly using keyboard shortcut. You only need to hold ALT and press + button. And SUM function as described above is implemented on selected cell. Like Cell A6 was selected and ALT+ is pressed to get sum of cells above selected cells.

Calculate Age and Remaining Days in an Event using DateDif in Excel

You might need to calculate your age in Excel. There may be requirement to find number of years, months and days between two dates. Excel function DateDif helps us find this difference:

Syntax for this function is as under:

DatedIf (first Date, Second Date, Interval)

In order to use above function, first date must be occurring before the second date as per calender. If first date is later than second date, this function will return #NUM error. If either of first or second date is not a valid date, #VALUE error is returned.

Value of interval can be used from following as per requirement.

Interval Meaning Description
m Months Complete calendar months between the dates.
d Days Number of days between the dates.
y Years Complete calendar years between the dates.
ym Months Excluding Years Complete calendar months between the dates as if they were of the same year.
yd Days Excluding Years Complete calendar days between the dates as if they were of the same year.
md Days Excluding Years And Months Complete calendar days between the dates as if they were of the same month and same year.

Finding Your Age:

Your date of birth will be first date and the date at which you want to calculate your age will be Second date in DateDif function. You can use value of interval as required.

 =DATEDIF(B1,B2,”d”) will return your age in number of days.

=DATEDIF(B1,B2,”m”) will give the number of months you have lived on earth

=DATEDIF(B1,B2,”y”) will simply give your age in years.

=DATEDIF(B1,B2,”y”)&” years “&DATEDIF(B1,B2,”ym”)&” months “&DATEDIF(B1,B2,”md”) &” days” will return your age in more understandable and complete form like number of years, months and days.

 

Calculate Number of Days Remaining in a Year:

You can also calculate how many number of days are left in this year. You will first find current year and then use today as first date and 31st December of current year as second date and “d” as interval. Our formula will look like: =DATEDIF ( Today’s date, 31 December of current year, “d”)

 

Similarly by changing second date in above example, remaining days in any event can be calculated.

You can download Excel file having above calculations here.

How To Add Colored Cells in Microsoft Excel

Use of SUM formula is very common to add a range of cells. You can also use SUMIF as well if some condition has to be fulfilled for addition of certain cells. But if we have to add certain cells from a range on basis of cell background color or font color of those cells, Excel doesn’t has any built-in solution so far.

Only way to fulfill above requirement is to write a user defined function. We will write a simple function which will help us use a custom formula to add content of colored cells.

You will have to open VB Editor (short key is ALT+F11) and insert new module there. Simply paste below code inside new module.

Function SumIfColor(sumRange As Range, requiredColor As Range) As Variant
Dim dataCell As Range
For Each dataCell In sumRange
If dataCell.Interior.ColorIndex = requiredColor.Interior.ColorIndex Then
SumIfColor = dataCell + SumIfColor
End If
Next
SumIfColor = SumIfColor
End Function

After you have pasted above code, as an easy example, we have below data where highlighted in green are to be added. We must have a cell where required color is fixed so as to compare cells from range.

Use of Custom Formula

We are going to add colored cells from E1 to E4. Our required color is in D5. To have our sum result in E5, simple write down above function name SumIfColor as formula like =SumIfColor(range of cells to be added , cell containing required color)

Formula in our example will be =SumIfColor(E1:E4,D5)

Please Note that whenever you change the color of any cell, you have to press CTRL+ALT+F9 to update formula result. Result of the formula doest get updates by F9 or even by reopening your Excel file.

How to Assign Keyboard Shortcut to Open Excel in Windows

If you are used to keyboard short-cuts, it really makes life easier. You can really do tasks way faster.

You have plenty of keyboard shortcuts once you are working in Excel. But you want to open using a shortcut? It’s very easy.

Simply go to the Excel icon and open properties using right click. Rest is explained below.

In properties, go to Shortcut tab; there is option of Shortcut key. You can use any combination of your choice. I am using Ctrl + Shift + E. To enter this, you need to hold Ctrl, then Shift and then press E.

using Ctrl+Shift+E as short key

Working With Excel

Let's Start Working with Excel

Era of electronic spreadsheets started from VisiCalc in 1983 and now well developed version of Microsoft Excel 2010 is available at this moment and too much is to appear yet.

Name any profession where employees use computers, Excel exists there. No organization working with computers for its communication and operations can live without using Excel. Although there are many advanced and focused tools available for statistical analysis and accounting tasks, yet Excel is the simplest and effective. Less expensive as well.

It is fact that many professional come across importance of Excel when they have completed their education and starting some job or internship. Before this point, Excel was almost of no use for them. Excel is widely used tool in corporate environment for creating basic reports and handling extensive data as well. This is being used as a simple calculator to high end data analysis assistant. Many a companies which can’t afford to have expensive ERPs, run their finances and operations using this software.

I got to know about Excel in 10th grade of school, back in 1999. That was limited to typing names, using different sort of text options and formatting outlook of cells. Later in 2000, I got opportunity to attend Microsoft Office Certification Course. This course introduced me to basics of using formula, creating basic line and bar charts and some taste of macro recording. During my Bachelors in Electrical Engineering, prime use of excel was to make a table to fill it with experimentation values and sometimes making a graph of available data. It was in January 2007 when I started my first job in Operations Core Networks department of a leading telecommunication company. Since then, Excel became part and parcel of my working life. Usage of formals, conditional formatting, different charts and pivot tables became a part of my job.

Aim of this blog is to prepare and share a sort of Excel tips and tricks library so as to document all my tasks such that those are understandable by you.