Tech2 Touch

Author: Aniruddh

  • Using Formulas in Excel using Named Ranges

    Constructing the formulas sometimes very complicated especially when you use several functions in the same formula or multiple argument for a single function. For this, if you use named ranges or constants which refers to a frequently used value or constant. A ‘Named range’ is a name you assign to a group of related cells. Using named constants and named ranges can make creating formulas and functions easier by enabling the use of names that clearly identify a value or range of values. The named ranges or named values also helps you to understand the formula to other easier. Let us have a look into how to define a named ranges and values in excel and how to use them in Excel formulas.

    Defining Named Ranges and Values in Excel

    Defining Named Ranges and Values in Excel
    • Click on the Formula Tab and then  Click on ‘Define Name’.  The new name dialog box appears.
    • Type the name you want to assign it to a range or constant. Ideally, it will be good practice to give a name which related to the value it refers to, though you can name it whatever you want.
    • Select the scope of the defined name. You can choose for the full workbook or specified sheet.
    • In the comment section, you can write something which describes your Named range or value. It is optional. It is better to write as it will be helpful when you refer this range after some time as you might forget what it refers to.
    • ‘Refers to’ section you can enter a constant value or a range of cells. You can use browse icon to select the ranges or even you can manually enter it.
    • Click Ok. You are done with defining the Names Range or Value.

    Creating the Formulas using defined Named Ranges

    We have already defined the range with a name. Now we will how to use that in the Excel functions or formulas. Use the functions or Formulas as usual. But instead of writing the range, type the name of that range you have defined. Below is the simple example which will give you better idea on how to use Named ranges in Excel Formulas.

    Below is the Sample Excel data which has Smartphone names and its price.

    Sample data for named ranges

    Here I have defined the range from B2 to B5 as ‘PriceRange’. Now for using this range in SUM function look as mentioned below.

    =SUM(PriceRange).

    Note: When you are using the named range in Excel Formula, as soon as you type first letter of your named range, you will see a drop down list which shows the named ranges. You can use that for easy to type the names.

  • Windows 8 Keyboard Shortcut Keys

    Windows 8 the latest operating system from Microsoft is made big news because of its new User interface. This operating system is mainly built to use on touch screen devices. However, it runs and functions properly on even on normal non touch screen laptops and desktops. Those who are familiar with older versions of Windows, feel little difficult to adopt. So here is a simple tricks using keyboard shortcut keys to make life easier on Windows 8.

    Windows 8 Keyboard Shortcut Keys:

    1. Windows Key+C: This brings the side bar menu. Alternatively, you can move the cursor to right bottom or top corner to get this menu.
    2. Windows Key+I: This will show you the quick settings menu in the right side. Using this menu provides access to  Control panel, brightness of the screen, Power options, Volume etc. This is one of the most handy shortcut key and it is very useful for me because I do not have the touch screen device.
    3.Windows Key+D: This shows the desktop. It works even if you are in modern UI page or any other pages are open and if you want to go back to desktop, you can use this shortcut key.
    4. Windows Key: This will bring the modern UI.
    5. Windows Key+.(Period): This will snap the active app to right side bar. For example, you might have opened news app and you want to open other things. You can keep that modern UI app in the right side, so that even if you are opened other software or app, the side bar shows the your modern UI app.
    6. Windows Key+X: This gives you the power menu through which you can access Device manager, Run command, Disk Management etc.
    7. Windows Key+E: This quickly takes you to the My computer screen. This is another very useful shortcut key in Windows 8. Because there is no direct shortcut key to My computer in Windows 8 like its predecessors.
    8. Windows Key+Q: This shows the page which shows all the apps installed on the system
    9. Windows Key+F: You can this shortcut key to search for an app
    10. Windows Key+R: This opens the run command

    Other Miscellaneous Shortcut Keys For Various Tasks:

    1. For shutting down or restarting the system: Press Windows Key+I. This brings the right side settings menu. Choose power and select shutdown or restart.
    2. For showing the system information: Windows Key+X. This brings the power menu options in the left bottom corner. In that select the ‘System’. The new page opens which shows the system information such as Processor, RAM etc.
    3. Switching between  pages or apps:  Press Alt+Tab: using these two keys you can toggle between the active applications or pages. Use Alt+Tab+Shift to select the applications in the backward directions.
    4. Typing Indian Rupee Symbol: You can use Shit+Ctrl+$: This writes the rupee symbol if you are enabled the Indian keyboard layout.   I have written a detailed post on how i enabled rupee symbol on my Windows system You can read on this here . It works on all the keyboards irrespective of whether rupee symbol is written in the keyboard or not.

  • Learning Excel Table Features-Introduction

    Excel Tables are defined as the series of rows and columns with related data which can be managed independently. In the processor of learning Excel, Excel Table is very important because it is a very useful and a powerful tool of Excel.

    Excel tips on Excel Tables
    Excel Table Example

    What is Excel Tables? What are the advantages of using the Excel Tables?
    You might be thinking on these questions. For better understanding, let us look into a simple example. We have a data in the range A1 to E20 which talks about the different products, their price variations.  By using Table, we are telling the excel that, The cells from A1 to E20 has related data and row 1 has table headers. Currently data is in only 19 rows. However it can be increased later. When you make a table in Excel, you can add rows without worrying about updating the formula references,  formatting of cells, Filter settings etc.
    Key Advantages of Excel Tables:

    • Activating any cell in the table gives the access to a new Table Tools contextual tab on the ribbon
    • Provides option to quickly apply background color and text color formatting by choosing from the gallery  This formatting is optional.
    • Each column header contains a drop down list which you can use to sort the data or filter the table to hide specific rows.
    • If you scroll down the sheet so that the header row disappears, the table headers replace the column letters in the worksheet header. In simple words, you don’t need to freeze the top row of the table to keep the column labels visible
    • Tables support calculated columns. A single formula in a column is automatically propagated to all cells in the column.
    • Excel Tables supports structured references. Instead of using the cell references formula can use table names and column headers
    • When you move your mouse pointer to the lower right corner of the lower right cell, you can click and drag to extend the table’s size, either horizontally(add more columns) or vertically(Add more rows).
    • Selecting the rows and columns within the table is simplified
    Disadvantages or limitations of Excel Tables:
    • When a workbook contains atleast one table, Excel doesn’t allow you to use the custom views features(choose view->Workbook Views-> Custom Views).
    • You cannot insert automatic subtotals within a table(by choosing Data->Outline->Subtotal)

  • Excel Formula to return the last nonblank cell in a column or Row

    Some times in Excel worksheet which we update frequently by adding new data to its columns and we need to reference the last value in a particular column. That is the value most frequently entered. We can achieve this combining two excel worksheet formulas COUNTA and INDEX. The source of this tips is John Walkenbach’s Excel 2010 Tips &Tricks book. To learn about find the last row using Excel VBA click here.

    Below is the simple example with a excel worksheet which tracks the value of three funds in columns B to D. The data updates each and every month. The motto is to get the latest value for each fund and add the values of this.These value will be updated in the cells G2:G4.

    Returning last Columns value in Excel
    The Excel formula used in G2,G3,G4 are as follows.
    =INDEX(B:B,COUNTA(B:B))
    =INDEX(C:C,COUNTA(C:C))
    =INDEX(D:D,COUNTA(D:D))

    Explanation on how this Excel Formula works: The COUNTA worksheet formula used above counts the number of non empty cells in the selected Column.Then this value is used as the second argument for INDEX worksheet function.  In the the first formula, in column B the last value is in row 6 hence COUNTA returns 6  and the INDEX function returns the 6th value in the column.

    The above formula works on most of the situations. However, if the column has one or more empty cells interspersed(Empty cell in between the cells which has values), determining the last nonblank cells is challenging as COUNTA function doesnot count the empty cells.
    The following array formula returns the content of the last non empty cell in the first 1000 rows of column C, even if column C contains blank cells.
    =INDEX(C1:C1000,MAX(ROW(C1:C1000)*(C1:C1000<>””)))
    Note: As this is a Array formula of Excel, you need to press Ctrl+Shift+Enter instead of just Enter. Otherwise it given wrong output.
    You can change the formula to work with a column other than column C. To use a different column, change the column reference from C to whatever column you need. If the last non empty cell occurs in a row beyond row 1000, you need to change the two instances of 1000 to a desired row number. The fewer rows referenced in the formula, the faster the calculation speed.
    To return the last non empty cell in a row, you need to use following excel array formula. It works similar to above formula, but finds last row instead of last column(In this example, row 1.
    =INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))
    to use this formula for a different row, change the the three 1:1 row references to correspond to the correct row number.

  • How to Perform two column Lookup on Excel

    The VLOOKUP function in excel does not handle multiple criteria by default. However, with little tweaking the source data or with the help array formula we can perform multi condition lookup. In this post, we will learn the way to do two column lookup on Excel.

    Let us take a sample data.

    Perform two column Lookup on Excel

    To make this formula we have defined user named ranges.

    Range(C4:D6) is named as lookup_Table

    Other related posts:

    VLOOKUP in EXCEL Tips and Tricks

    ROW and Column VLOOKUP

    Perform two column multi condition vlookup using the helper column

    As VLOOKUP does not take multiple criteria, we need to insert a new column called helper Column. In this column we need to concatenate the fields of two column. In our example, we have concatenated Brand and Model using the formula concatenate or with the help of “& “.

    Formula in cell C4 is

    =A4&B4

    Final vlookup formula is

    =VLOOKUP(G3&G4,lookup_Table,2,FALSE)

    How this conditional vlookup works

    In the example, we want to lookup price using vlookup based on brand and model.

    One of the limitation of VLOOKUP is it handles only one condition. The lookup value will be searched in first column of the selected data range. To overcome this limitation, we are using the helper column.

    In the helper column, we have concatenated the Brand and model. The lookup value also we have joined together. In the example, VLOOKUP searches for BajajPulsor in the range C4 to C16. It is there in 9 the row. Now it will return the corresponding value in 2nd column. That is 53000.