ExcelHub - Learn Excel Easily

Excel Tip #016 COUNTIF Partial Matching There are many COUNT functions that are available in Excel. One of which is the COUNTIF() function. Let us see a more detailed use of it. The use of COUNTIF() function - It is used to count the number of cells in a given range which meets the specified condition. Its syntax is, =COUNTIF(range,criteria) The range field is the group of cells that you want to count. The criteria field tells the cells that needs to be counted which can be a number, expression, cell reference, or text string. Consider the below example. Example - COUNTIF() function =COUNTIF(A2:A6,"Cadbury") - This gives me 1 as the result =COUNTIF(A2:A6.A5) - In A5 we have Toblerone and the function searches for Toblerone in the list which is 1 =COUNTIF(B2:B6,23) - Here Mars chocolate has 23 and the result will be 1 =COUNTIF(B2:B6.">50") - The condition is to count cells which are above 50. We have Toblerone and Ferrero Rocher have q...

Excel Tip #015 Alphabets auto-fill You would have done auto-fill for numbers in Excel but auto-fill for alphabets is not possible. This is because Excel is a number crunching tool.  But first how do you auto-fill numbers in Excel. Type 1 in A1, 2 in A2 and 3 in A3 Then click the fill handle (which is the thick block at the right bottom corner) Drag the fill handle to the number of rows you want the data to populate Auto-fill Numbers To do the same, there is a hidden Excel shortcut . To do this, you don't need to enter 3 numbers, just 1 in A1 would do. Type 1 in A1 Select cells from A1 to A10 to populate numbers from 1 to 10 Type ALT + E + I + S A pop-up window will open, check the Trend box Click OK Auto-Fill Numbers Hidden Excel Shortcut But this is not possible in the case of Alphabets. But we have an option which can enable this feature. Click File menu Go to Options tab Click on Advanced options Scroll to the bottom in ...

Excel Tip #014 Dynamic Name Range Charts in Excel It would be nice if a chart that gets updated right before your eyes at each data entry. This can be done creating Dynamic Charts . The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. There are two ways to build dynamic charts in Excel - Table method and Formula method. Let us see how to work with both the methods. Consider the below example having the sales for 5 sales rep. Example - Dynamic Chart Table Method: The first step is to create the table. To do so, simply select the data range and do the following: Click the Insert tab . Click Table . Excel will display the selected range, which you can change. If the table does not have headers, be sure to un-check the " My table has headers " option. Click OK and Excel will format the data range as a table. Creating the data as Table ...

Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exa...

Excel Tip #012 VLOOKUP & INDEX + MATCH Excel has many formulas which functions differently. To analyze data, one of the most important formula is VLOOKUP and INDEX + MATCH . Let us now get into the details of it. VLOOKUP : Let us consider the below data as an example. Sales Data Let us say, we want to know how much sales did Jerald make?  VLOOKUP  can be used to answer this question. What does VLOOKUP actually do?      VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, = VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) The final parameter is optional but very important. In the next tutorial, I will provide a few examples explaining how to correctly make formulas for exact (or) ...

Excel Tip #011 Zoom to make your spreadsheet fit your screen Usually a new Excel sheet is available at Zoom % of 100. Sometimes for the look and feel of the workbook, we usually change the zoom %, I usually have my workbooks mostly at 90% zoom. But to fit the data into the screen, we might have to decrease the zoom % further so that the entire data is viewed properly. Before going to our today's tip, let us first check out the ways we can zoom in (or) zoom out the Excel sheet. Option 1 (Usual method): People mostly follow either of these 2 usual methods. One way is going round the Excel ribbon tab and the other is using the Zoom option available at the right bottom corner of the sheet. Using Zoom option via Excel ribbon Using Zoom option at the right bottom corner of the Excel sheet Option 2 (Keyboard Shortcut): When we move over the Excel ribbon by pressing ALT keys, alphabets appear. The shortcut key is ALT + W + Q   Shortcut key using Exce...

Excel Tip #010 Changing default chart color layout Whenever we want to use the chart series, Excel comes up with certain default colors to beautify the charts. It is high time we should know to change the default color layout and below is the same. To change the colors of the current workbook: On the Page Layout tab, in the Themes group, click Theme Colors Theme Colors under Page Layout tab Click Customize Colors Customize Colors option In the Create New Theme Colors dialog box, under Theme colors , click the button of the theme color element that you want to change Under Theme Colors , select the colors that you want to use Choosing colors for the new theme In the Create New Theme Colors dialog box, under Sample , you can see the effect of the changes that you make Repeat step 3 and 4 for all of the theme color elements that you want to change In the Name box, type an appropriate name for the new theme colors Click Save To revert all theme col...

Excel Tip #009 Inserting and deleting cells, rows and columns There are ways inserting and deleting cells, rows and columns. Below are 3 methods (which I can recollect) in inserting (or) deleting rows and columns. Option 1 (Standard Procedure) : Selecting a row (or) column by clicking it, then right click for options. You can see options "Insert" and "Delete" . This will add a new row in the left (or) a new column in the top. Inserting/Deleting row (or) column - Option 1 (Standard Process) Option 2 (Unusual mouse method) : You can quickly insert a range of empty cells or rows by holding down the SHIFT key while dragging the fill handle.   To insert a range of cells, select a single cell above where you want the cells inserted and drag the fill handle down the number of cells you want to insert.  Inserting/Deleting row (or) column - Option 2 (Unusual mouse method) Inserting columns works basically the same way by dragging the Fill Ha...

Excel Tip #008 Split multiple row data in one cell into separate cells To force the contents of a cell onto multiple lines in the same cell, you can insert a line break within the cell by holding down the ALT key and pressing Enter . But what if you have a worksheet with many of these cells with multiple lines and you would like to put the contents of each row in separate cells. Doing this manually by copying and pasting parts of each cell would be very slow and tedious. So here is an option to split multiple row data in one cell into separate cells. To do this, we are going to take help from Word . This is an effective approach to break each row within a cell into separate rows in the same column. Consider the below example where cells A1 through A4 are filled with random names where each cell occupying 5 names. Multiple data in a single row in Excel Copy and paste the column of data into a blank Microsoft Word document. Copy pasted data from Excel to ...

Excel Tip #007 Rounding Formulas in Excel We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. Before learning the tips, first lets understand various rounding formulas & what they do. Various Rounding Formulas Below are the examples explaining the different round functions. ROUND formulas in Excel

Excel Tip #006 Replace formulas with values This is one of the most useful tip that we use in everyday while we use excel. Consider you have a range of cells (or) formulas written. You want to copy the range and move it to other range of cells (or) in a new sheet. This can be done in 2 ways - either by a keyboard shortcut (or) by a mouse shortcut. Let us consider the below range of values which are all formulas. Range of cells with Formulas The usual way of doing this is - copying the range, right click and then paste as values (shown in the below image). Copy pasting as values - Usual method Keyboard shortcut After copying the range, press ALT + E + S which will pop up a window. Press V (denotes values) and enter, will result the range as values. Copy pasting values - Keyboard Shortcut Mouse shortcut Select all the cells that contain formulas Right click on the selection border (your mouse pointer changes to 4 sided arrow) Right click...

Excel Tip #005 Relative Vs. Absolute References in Formulas A reference in excel "Identifies a cell (or) a range of cells on a worksheet and tells Microsoft Excel where to look for the values (or) data you want to use in a formula". Difference between Relative and Absolute references When you say a reference is relative, you are telling excel to adjust that reference in formulas based on where you move or copy the formula. For example, if you have a formula in cell B2 as =A2*2 and now if you copy paste this in another cell, lets say, C2, the new formula would read like =B2*2 Relative reference When you say a reference is absolute (denoted by $ symbol), you are telling excel not to adjust that reference in formulas when you move or copy them. Absolute reference Switching between Relative and Absolute references While editing the formula you can use F4 function key to change the reference of a cell on which cursor is focused. By pressing F4, excel...