TIP 20 : MS Excel : Define Named Array Range For Quick Reference

In this blog we will see how to define a name for the array range and refer it in formulas in excel sheet.

• Select the array range which you want to define in excel sheet.

• Enter the name of the array range in Name Box field on the top left corner of excel sheet and press enter key.

• Now you can easily use this named array range in formula and also quickly select the array range by selecting the array range name from the Name Box list.

TIP 19 : MS Excel : Insert/Delete column in excel sheet

In this blog we will get to know the keyboard shortcut to insert and delete the column in excel sheet without using mouse at all.

• Select the column where you want to insert the new column.

• Press Ctrl & +(plus) key, you will observe the new added column. It will always add the new column on the left to selected column.

• Select the column/s which you want to delete.

• Press Ctrl & -(minus) key to delete the selected columns.

TIP 18 : Insert alternate empty lines in large data set in fraction of seconds

In this blog we will see how to see how to insert alternate blank lines in the large data set in excel sheet in fraction of seconds.

• Let us create alternate lines in below data set in excel sheet.

• To create alternate empty lines we need to create a number series in the row that is next to the last row. Easiest way is to use the auto fill feature of MS excel to generate the number series. Enter the two starting numbers 1 and 2 and select the entered number, now double click on the + icon or click on the + icon and drag till end of the row to auto fill rows with number series.

• Use the (Ctrl + Shift +  ↓(down arrow key)) to select and copy the numbers in last row. Now go to the last row of the data set and paste the number series for the next row.

• Go to the first row of the number series and select the cell, now use mouse right click and click on sort and then Sort Smallest to Largest to sort the number series.

• Once the list is sorted you will see the magic and as well the alternate empty lines in between data.

• Select the number series again in the last row and delete the series from the sheet.    

TIP 17 : MS Excel : Merge excel workbooks with single mouse click action

As a team lead you might need to collaborate the data of your team members by requesting data in different sheet and then merging it back in single sheet. Manually merging the details takes lot of time and is a very time consuming activity. In this blog we explain how to create a shared worksheet and merge the data from different sheets with single mouse click.

Let us create a worksheet to merge leave details of team members.

• Create a sheet with leave details template format and save the excel workbook.

• Make this workbook as shared workbook. Go to review tab and click on the share workbook button.

• It will open up Share Workbook dialog window. Select the check box “Allow changes by more than one user at the same time. This also allows workbook merging” and press OK button.

• There will be prompt to save the workbook, press OK to continue.

• You will observer that your sheet now changed to shared sheet as we have shared keyword added next to file name in the header bar.

• This workbook is now ready to be shared among team members. They can fill the details and can save the workbook with different names as well. We can easily merge the sheets using compare and merge feature of excel from quick access toolbar.

Now, share the leave details sheet with all the five employees so that we can have five different sheets to be merged into one single sheet.

Leave_Details_1.xlsx

Leave_Details_2.xlsx

Leave_Details_3.xlsx

Leave_Details_4.xlsx

Leave_Details_5.xlsx

• Add compare and merge option by customizing the quick access toolbar. Click on the Customize Quick Access Toolbar icon and then select More Commands.. from the list or alternatively you can access it from File tab then click on options and select Quick Access Toolbar from the left hand side panel.

• Now select “Commands not in Ribbon” from the Choose commands drop down and then scroll down to select “Compare and Merge Workbooks…” option.  

• Add the Compare and Merge Workbooks… to the toolbar by clicking on the Add button and press OK.

• You will find the Compare and Merge Workbooks option added to the workbook header.

• Click on the Compare and Merge Workbooks icon it will open up file manager window. Select the files to be merged.  

• We can see all files are merged into single file in fraction of seconds.

TIP 14 : MS Excel : Create Custom Auto Populate List

We have frequently used the serial number auto fill using mouse double click and drag options. In this blog we will learn how to use the MS Excel auto fill feature for our own custom list. 

Let us create custom list of countries that we want to use frequently in excel sheet.

• India
• United States
• China
• Japan
• Bhutan
• Brazil

Follow below mentioned steps to create custom auto fill list - 

Go to file and then click on Options.

It will open up Excel Options dialog window. Select Advanced option in the left hand panel and then scroll down and click on Edit Custom List... button.

Select NEW LIST from Custom Lists window and then click on Add button.

Add the entries for the auto fill list and press OK.

Press OK button on Excel Options dialog window.

Custom Auto fill list is ready for you. Type the first entry of the list and drag outside the selection to fill the series.

TIP 11 : MS Excel : Sheet Selection

In this blog we will see how we can quickly select sheet using single click when we have large number of sheets in the excel file. This shortcut will save your time in selecting a sheet without traversing all the sheets using arrow buttons.

As shown in screenshot below we have large number of sheets and it time consuming to select sheet 20 by traversing from sheet 1 to sheet 20. We can select sheet within fraction of seconds using this method.   

• Press mouse right button on the arrow keys.

• It will open up a pop with a list of all the sheet names. Select the sheet and press OK, it will open up the selected sheet in excel file. 

TIP 9 : MS Excel : Select Entire Row/Column

Shortcut To Select Entire Row/Column In Excel Sheet

In this blog we will look for keyboard shortcuts to select entire row and column in the excel sheet.

Select the cell in the row or column which you want to select. 

• CTRL + Spacebar - Select entire column

• Shift + Spacebar - Select entire row

TIP 8 : MS Excel : Sheet Navigation

Shortcut To Navigate Between Sheets in Excel File

In this blog we will see how to navigate among sheets in the file. We can navigate both left and right of the current sheet without moving mouse at all.

Sheet2 is the current sheet in the below screenshot.

Press CTRL + Page Down key to move right of the current sheet.

Press CTRL + Page Up key to move left of the current sheet. We can traverse from Sheet1 to Sheet3 by pressing CTRL + Page Up key twice 

 

TIP 7 : MS Excel : Create New Sheet

Shortcut To Add New Sheet In Excel

In this blog we will see how we can add new sheet in excel file using keyboard shortcut without using mouse at all.

Press SHIFT + F11 to add new sheet in excel file. It will always add sheet left to the current selected sheet.

TIP 6 : MS Excel : Navigation in Excel Sheet

Shortcut For Navigation In Excel Sheet

• Zoom In/Zoom Out - We can Zoom In and Zoom Out the excel sheet using the mouse wheel. Move the mouse wheel upwards to Zoom Out and downwards to Zoom In the excel sheet.

Zoom Out - Mouse Wheel Upward Direction

Zoom In -  Mouse Wheel Downward Direction

• Move To First Cell (HOME) - While working with excel sheet with huge amount of data with million of record, we most of the time want to move to the beginning of the sheet. We can achieve this using keyboard shortcut rather than scrolling with mouse or scroll bar. 

 CTRL + HOME is the Keyboard Shortcut which is used to move the index to the first cell(A1) in the excel sheet.