EXCEL shortcuts 2016 tutorial PDF


Télécharger EXCEL shortcuts 2016 tutorial PDF

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


14 Simple Excel Shortcuts, Tips & Tricks

1)   Pivot Tables

Pivot Tables are used to reorganize data in a spreadsheet. They won't change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you'd like them to do.

Let's take a look at an example. Let's say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don't have too much data, but for longer data sets, this will come in handy.

To create the Pivot Table, I go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.

1.  Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.

2.  Column Labels: These could be your headers in the dataset.

3.  Row Labels: These could be your rows in the dataset. Both Row and Clumn labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label -- it just depends on how you want to see the data.)

4.  Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.

Since I want to count the number of students in each house, I'll go to the

Pivot Table and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.

 

2)   Add More Than One New Row or Column

As you play around with your data, you might find you're constantly needing to add more rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there's always an easier way.

To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, rightclick and select "Insert."

In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I'm able to add an additional three blank rows into my spreadsheet quickly and easily.

 

3)   Filters

When you're looking at very large data sets, you don't usually need to be looking at every single row at the same time. Sometimes, you only want to look at data that fit into certain criteria. That's where filters come in.

Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data -- and from there, you can then choose which cells you want to view at once.

Let's take a look at the example below. Add a filter by clicking the Data tab and selecting "Filter." Clicking the arrow next to the column headers and you'll be able to choose whether you want your data to be organizing in ascending or descending order, as well as which specific rows you want to show.

In my Harry Potter example, let's say I only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.

 

Pro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.

4)   Remove Duplicates

Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.

To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab, and select "Remove Duplicates" (under Tools). A pop-up will appear to confirm which data you want to work with. Select "Remove Duplicates," and you're good to go.

 

You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows with Harry Potter's information and you only need to see one, then you can select the whole dataset and then remove duplicates based on email. Your resulting list will have only unique names without any duplicates.

5)   Transpose

When you have low rows of data in your spreadsheet, you might decide you actually want to transform the items in one of those rows into columns (or vice versa). It would take a lot of time to copy and paste each individual header -- but what the transpose feature allows you to do is simply move your row data into columns, or the other way around.

Start by highlighting the column that you want to transpose into rows. Rightclick it, and then select "Copy." Next, select the cells on your spreadsheet where you want your first row or column to begin. Right-click on the cell, and then select "Paste Special." A module will appear -- at the bottom, you'll see an option to transpose. Check that box and select OK. Your column will now be transferred to a row or vise versa.

 

6)   Text to Columns

What if you want to split out information that's in one cell into two different cells? For example, maybe you want to pull out someone's company name through their email address. Or perhaps you want to separate someone's full name into a first and last name for your email marketing templates.

Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select "Text to Columns." A module will appear with additional information.

First, you need to select either "Delimited" or "Fixed Width."

•   "Delimited" means you want to break up the column based on characters such as commas, spaces, or tabs.

•   "Fixed Width" means you want to select the exact location on all the columns that you want the split to occur.

In the example case below, let's select "Delimited" so we can separate the full name into first name and last name.

Then, it's time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or something else. ("Something else" could be the "@" sign used in an email address, for example.) In our example, let's choose the space. Excel will then show you a preview of what your new columns will look like.

When you're happy with the preview, press "Next." This page will allow you to select Advanced Formats if you choose to. When you're done, click "Finish."

 

Excel Formulas

7)   Simple Calculations

In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.

•   To add, use the + sign.

•   To subtract, use the - sign.

•   To multiply, use the * sign.

•   To divide, use the / sign.

You can also use parenthesis to ensure certain calculations are done first. In the example below (10+10*10), the second and third 10 were multipled together before adding the additional 10. However, if we made it

(10+10)*10, the first and second 10 would be added together first.

 

Bonus: If you want the average of a set of numbers, you can use the formula=AVERAGE(Cell Range). If you want to sum up a column of numbers, you can use the formula =SUM(Cell Range).

8)   Conditional Formatting Formula

Conditional formatting allows you to change a cell's color based on the information within the cell. For example, if you want to flag certain numbers that are above average or in the top 10% of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information the is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Then, choose "Conditional Formatting" from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select "OK" when you're done, and you should see your results automatically appear.

 

9)   IF Statement

Sometimes, we don't want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.

For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. Instead of manually typing in 10's next to each Gryffindor student's name, I can use the IF THEN Excel formula to say that if the student is in Gryffindor, then they should get ten points.

The formula: IF(logical_test, value_if_true, value of false)

Example Shown Below: =IF(D2="Gryffindor","10","0")

In general terms, the formula would be IF(Logical Test, value of true, value of false). Let's dig into each of these variables.

•   Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor" because we want to make sure that the cell corresponding with the student says "Gryffindor." Make sure to put Gryffindor in quotation marks here.

•   Value_if_True: This iswhat we want the cell to show if the value is true. In this case, we want the cell to show "10" to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.

•   Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show "0" to show 0 points. Only use quotation marks if you want the result to be text instead of a number.

 

Note: In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn't be able to because the 10's are in quotes, thus making them text and not a number that Excel can sum.

10)    Dollar Signs

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn't representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.

You see, a cell reference -- when you refer to cell A5 from cell C5, for example -- is relative by default. In that case, you're actually referring to a cell that's five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it'll adjust the values in the formula based on where it's moved. But sometimes, we want those values to stay the same no matter whether they're moved around or not -- and we can do that by making the formula in the cell into what's called an absolute formula.

To change the relative formula (=A5+C5) into an absolute formula, we'd precede the row and column values by dollar signs, like this: (=$A$5+

$C$5). (Learn more on Microsoft Office's support page)

Excel Functions

11)   VLOOKUP Function

Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other -- but you want the names, email addresses, and company names of those people to appear in one place.

I have to combine data sets like this a lot -- and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.

The formula: =VLOOKUP(lookup value, table array, column number, [range lookup])

The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.

•   Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).

•   Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1.

•   Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that "House" is located in. "House" is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 -- Email, Age, and House -- and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the "2" to a "3" so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]

•   Range Lookup: Use FALSE to ensure you pull in only exact value matches.

In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.

 

So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.

We've also written afull post about how to use the VLOOKUP function here(complete with video tutorial) if you still feel confused.

Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.

12)    INDEX MATCH

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:

1.  VLOOKUP is a much simpler formula. If you're working with large data sets that would require thousands of lookups, then using the INDEX MATCH function will significantly decrease load time in Excel.

2.  INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you'd have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren't the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I'd choose to do an INDEX MATCH instead.

Let's look at an example. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I'd use the INDEX MATCH formula instead of VLOOKUP so I wouldn't have to switch any columns around.

So what's the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. You'll see I differentiated the MATCH formula using a different color here.

The formula: =INDEX(table array, MATCH formula)

This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))

The formula with variables from our example below: =INDEX(Sheet2!A:A, (MATCH(Sheet1!C:C,Sheet2!C:C,0))) Here are the variables:

•   Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, "A"

means Column A, which contains the "Patronus" information for each person.

•   Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the "email" column on Sheet 1, which is Column C. So: Sheet1!C:C.

•   Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the "email" column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX MATCH formula in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.

 

13)    COUNTIF Function

Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.

For example, let's say I want to count the number of times the word "Gryffindor" appears in my data set.

The formula: =COUNTIF(range, criteria)

The formula with variables from our example below: =COUNTIF(D:D,"Gryffindor")

In this formula, there are several variables:

•   Range: The range that we want the formula to cover. In this case, since we're only focusing on one column, we use "D:D" to indicate that the first and last column are both D. If I were looking at columns C and D, I would use "C:D."

•   Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is "Gryffindor."

Simply typing in the COUNTIF formula in any cell and pressing "Enter" will show me how many times the word "Gryffindor" appears in the dataset.

 

14)    Combine cells using "&"

Databases tend to split out data to make it as exact as possible. For example, instead of having a data that shows a person's full name, a

database might have the data as a first name and then a last name in separate columns. Or, it may have a person's location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell by using the "&" sign in your function.

The formula with variables from our example below: =A2&" "&B2

Let's go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we'd first put our cursor in the blank cell where we want the full name to appear. Next, we'd highlight one cell that contains a first name, type in an "&" sign, and then highlight a cell with the corresponding last name.

But you're not finished -- if all you type in is =A2&B2, then there will not be a space between the person's first name and last name. To add that necessary space, use the function =A2&" "&B2. The quotation marks around the space tell Excel to put a space in between the first and last name.

To make this true for multiple rows, simple drag the corner of that first cell downward as shown in the example.

 

How to Make a Chart or Graph in Excel

Step 1: Get your data into Excel.

First, you need to input your data into Excel. This is the easy part! You may have exported the data from elsewhere, like a piece ofmarketing softwareor a survey tool. Or maybe you're inputting it manually.

In the example below, in Column A, I have a list of responses to the question, “Did inbound marketing demonstrate ROI?”, and in Columns B,C, and D, I have the responses to the question, “Does your company have a formal sales-marketing agreement?” For example, Column C, Row 2 illustrates that 49% of people who have an SLA (service level agreement) also say that inbound marketing demonstrated ROI.

 

Step 2: Choose a type of chart/graph to create.

In Excel, you have plenty of choices for charts and graphs to create. (For help figuring out which type of chart/graph is best for visualizing your data, check out our free ebook,.)

The data I'm working with will look best in a bar graph, so let's pursue making that one. To make a bar graph, highlight the data and include the titles of the X and Y axis. Go to the ‘Insert’ tab, click ‘Charts,’ click ‘Column,’ and choose the graph you wish. In this example, I will be picking the first 2-D Column choice -- just because I prefer it over the 3-D look.

 

Step 3: Switch axes, if necessary.

If you want to switch what appears on the X and Y axis, right click on the bar graph, click ‘Select Data,’ and click ‘Switch Row/Column.’

 

Step 4: Adjust your labels and legends, if desired.

To change the layout of the labeling and legend, click on the bar graph, then click the ‘Layout’ tab. Here you can choose what layout you prefer for the chart title, axis titles, and legend.

In my example, I clicked on ‘Chart Title,’ and selected ‘Above Chart.’ To format the X axis title, I clicked on ‘Axis Titles,’ clicked ‘Primary

Horizontal Axis Title,' and clicked ‘Title Below Axis.’ To format the Y axis title, I clicked on ‘Axis Titles,’ clicked 'Primary Vertical Axis Title,’ and chose ‘Rotated Title.’ To change the placement of the legend, click ‘Legend’ on the ‘Layout’ tab and choose your preferred location.

 

Step 5: Change the Y axis measurement options, if desired.

To change the type of measurement shown on the Y axis, right click on the Y axis percentages, and click ‘Format Axis.’ Here you can decide if you want to display units located on the Axis Options tab, or if you want to change whether the Y axis shows percentages to 2 decimal places or to 0 decimal places.


 

 

The resulting graph would be changed to look like this:

Step 6: Reorder data, if desired.

To sort the data so the software choices appear in descending popularity order, click on the column that is most important to you (in this case, I picked column B), click on the ‘Data’ tab, and click ‘Filter.’ Then go back to

Column B, click the down arrow, and click ‘Sort Largest to Smallest.’

 

If you click on the downward arrows located at B1 and C1, you can choose to sort based on smallest to largest or largest to smallest, depending on your preference. Here, I sorted largest to smallest on B1.


 

Use an Image as a Background in Excel

Option 1: Adding a Background Image to your worksheet:

For Excel 2003:

1.    First of all open the worksheet where you wish to add the background.

 

2.    After this navigate to ‘Format’ > ‘Sheet’ > ‘Background’.

2. Now, browse through all the available images, select the image that you wish to add and click the insert button as shown in the below image.

 

4. This will add the picture that you just selected as the sheet background.

For Excel 2007, 2010 and 2013:

1.    First open the worksheet where you have to add the background.

2.    Next, navigate to ‘Page Layout’ Tab in the ribbon and click the ‘Background’ option.

 

3.    This will open a sheet background window, select the image that you wish to use as a background and then click the insert button.

 

4.    Now the image that you just selected will be used in the background your worksheet.

 

5.    The resultant will look somewhat like the above image.

Few Important tips about spreadsheet backgrounds:

•    Try to use contrast colours to make you spreadsheets more readable. For example- if you use a dark colour font on a light colour background image, it will be easier to read.

•    You can also use the shortcut Alt + P G to add an image background to your worksheets.

•    The image in your spreadsheet background can increase the overall size of the spreadsheet. So, only select those images that have small sizes.

•    You cannot add a background image to multiple worksheets at once. You can only add the background to one worksheet at a time.

•    Remove gridlines on your spreadsheets when you are using a background image, this makes them look smooth.

•    It should be noted that when you add an image background to your spreadsheet using the above method then it won’t show up when you print the sheet. To add printable backgrounds to your spreadsheet use the second option.

How to Remove the Background Image in Excel:

To remove the background in image follow the below steps:

For Excel 2003:

1.    First open the spreadsheet, where you have already added an image as background.

2.    Next, navigate to ‘Format’ > ‘Sheet’ > ‘Delete Background’.

 

3.    This will delete the image from the background.

For Excel 2007, 2010 and 2013:

1.    First of all open the spreadsheet, where you have already added an image as background.

2.    After this, navigate to the ‘Page Layout’ tab in the ribbon and click the ‘Delete Background’ option.

 

3.    Now, the image in the background of your worksheet will be deleted.

Option 2: Adding a Printable image to your worksheet:

In this method we will are not going to add the image as a background but instead we will add the image on top of another object. And then we will adjust the object so that it fits in the whole printable area. To use the method follow the below steps:

1.    Open the worksheet where you wish to add your image background. Before proceeding further make sure that you have filled the required data to your spreadsheet.

 

2.    Next, navigate to ‘Insert’ Tab > ‘Shapes’ > ‘Rectangle’, and draw a rectangle on your sheet.

 

3.    After this, right click on the rectangle and select the option ‘Format Shape’ as shown in image.

 

4.    This will open the ‘Format Shape’ options, click the ‘Fill’ option, in the ‘Fill’ type select the ‘Gradient or Texture’ radio button. Then using the ‘File’ button select the picture of your choice as the background and set its transparency according to your needs.

 

5.    Now adjust the image such that it shows in the printable area and then print the sheet.

Excel Function Keys and Shortcuts

This is a long post of around 3000 + words. So, below is a tabular format for you to know about the F1 to F12 function keys in excel in an easy to digest manner.

     

F1 Key:

“F1” is used for opening “Excel Help”. Its output is same as obtained by clicking on

“question mark button” available on top right hand side of your excel sheets as highlighted in below image.

 

Alt + F1:

If you use “Alt and F1” Keys together then it will insert a new chart in your excel and will open the chart options. It is a column chart by default as shown in below image.

 

Ctrl + F1:

You can minimize or maximize the ribbon of excel by pressing “Ctrl & F1” Keys together.  By minimizing the ribbon only tab names will be displayed on the ribbon. This could also be achieved by clicking on the button highlighted in below image:

 

Shift + F1:

Unfortunately, “Shift + F1” Keys alone does not provide any output. However, it works when Alt button is pressed.

Alt + Shift + F1:

A new worksheet is added to the workbook in which you are working currently by holding Alt + Shift + F1 Keys together.

Ctrl + Shift + F1:

This set of keys when pressed together does not give any output either like “Shift + F1” Keys.

Alt + Ctrl + F1:

This set of keys when pressed together does not give any output either like “Shift + F1” and “Ctrl + Shift + F1” keys.

Alt + Ctrl + Shift + F1:

“Alt + Ctrl + Shift + F1” does not provide any output.

F2 Key:

Using “F2” Key, you edit the contents of a cell in excel. If you select a cell that is filled with some value and then press “F2” Key, then it will allow you to insert at the end of the current content in cell. However if you press “F2” for an empty cell then it simply allows you to type in the empty cell.

If editing directly in cells is disabled in your workbook, then by pressing “F2” you can edit the contents in Formula Bar. In case you are unaware that if direct editing is disabled or not then just follow below steps:



•       Click on “File Menu”.

•       Select “Options” and then select “Advanced”.

•       In the “Advanced Option” for Excel, verify if the check-box against “Allow editing directly in cells” is ticked or not as shown in below image.

Alt + F2:

By holding “Alt + F2” keys together the “Save As” dialog box opens for saving the excel workbook.

Ctrl + F2:

To open Print options you can just hold “Ctrl + F2” keys together. It opens the same printing options as shown in below image which could also be opened by clicking on “File menu” and then by selecting “Print Option”.

 

Shift + F2:

Insert comments in a cell by just holding “Shift and F2” Keys together. You can also edit the existing comments in the selected cell using “Shift + F2” keys.

Alt + Shift + F2:

This set of keys “Alt + Shift + F2” when pressed together saves the workbook. It is same as “Ctrl + S” keys.

Ctrl + Shift + F2:

“Ctrl + Shift + F2” Keys don’t provide any output.

Alt + Ctrl + F2:

This set of keys “Alt + Ctrl + F2” when pressed together opens the “File Open Dialog Box”. It is same as “Ctrl + O” function.

Alt + Ctrl + Shift + F2:

“Alt + Ctrl + Shift + F2” keys open the print option in excel, same as “Ctrl + F2” keys.

F3 Key:

“F3” key helps you to paste name or paste list into the selected cell as displayed in below image. This function key will not work until and unless the list that you have created is having a “named range”.

You can refer thisarticleif you are not aware of a “named range”.

Alt + F3:

“Alt + F3” Keys don’t provide any output.

Ctrl + F3:

If you hold “Ctrl + F3” keys together then “Name Manager” dialog box is opened as shown in below image. If you want to know about “Name Manager” in a detailed manner then you canrefer this article.

 

Shift + F3:

“Shift + F3” keys when pressed together opens a window using which you can insert any function of your choice in the selected cell.

 

Alt + Shift + F3:

“Alt + Shift + F3” keys don’t provide any output.

Ctrl + Shift + F3:

“Ctrl + Shift + F3” Keys are used to “create names” from the row or column labels as shown in below screenshot. Although you can create the names for the selected cell from the values of top row, bottom row, left column or right column as per your preference.

 

Alt + Ctrl + F3:

“Alt + Ctrl + F3” keys are used to provide name the selected range or cell.

 

Alt + Ctrl + Shift + F3:

“Alt + Ctrl + Shift + F3” keys don’t provide any output.

F4 Key:

“F4” key provides you two different outputs depending on what you are doing currently in your excel.

If you are editing, then it will cycle you through the all three types of references, like – absolute reference, relative reference and mixed reference.

Refer below example for detail.

As displayed in below image in the selected cell of excel, we are first using the relative reference of Cell having Student A’s marks in English Subject. After pressing “F4” key it toggles and suggests the absolute reference of same cell and again by pressing “F4” key the mixed reference of the cell are suggested. If you are new to these terms of references – then you cango through this articleto understand these in details.

 

In addition to toggling the reference of a cell, “F4” keys also repeats the last action performed in workbook. Although, this could also be achieved using “Ctrl + Y” keys.

Example: If you have just inserted or deleted a row or column in your workbook then by pressing “F4” key you can repeat the action of insertion or deletion of rows and \ or columns.

Likewise if you have just pasted something in your workbook, then using “F4” you can repeat it.

Alt + F4:

I believe you all would be aware what magic “Alt + F4” keys do. For those who don’t know, by pressing “Alt + F4” excel would be closed. It will prompt you for a confirmation to save changes if there are any.

Ctrl + F4:

Using “Ctrl & F4” keys you can close the current open excel workbook window. It does the same operation as done by the “Close Window” button available on Excel workbook.

Shift + F4:

“Shift + F4” keys select the empty cell available next in the right direction to the current selection. It toggles in the same row until it finds the data, after which it goes to next row available.

Alt + Shift + F4:

“Alt + Shift + F4” keys provide same output as “Alt + F4” Keys, that is it will quit excel thereby closing all open excel workbooks.

Ctrl + Shift + F4:

“Ctrl + Shift + F4” keys select the empty cell available next in the left direction to the current selection. It toggles in the same row until it finds the data, after which it goes to next row available. Note that the difference between “Ctrl + Shift + F4” keys and “Shift + F4” keys is the direction in which movement is done. The cells are toggled in left direction using “Ctrl + Shift + F4” keys and in right direction using “Shift + F4” keys.

Alt + Ctrl + F4:

“Alt + Ctrl + F4” keys provide same output as “Alt + F4” Keys, that is it will quit excel thereby closing all open excel workbooks.

Alt + Ctrl + Shift + F4:

“Alt + Ctrl + Shift + F4” keys closes all open excels like “Alt + F4” Keys.

F5 Key:

“F5” key displays the “Go To” dialog box. “Go To” box will list down the valid “named range” available in the opened excel workbook. Along with this you can enter the reference of cell where you want to go in the “Reference” text box. Using “Special” button you can go to the various options as displayed in below image – comments, constants, formula, etc.

 

Alt + F5:

“Alt + F5” don’t provide any output.

Ctrl + F5:

“Ctrl + F5” keys when hit together restore window size from maximum of the current open excel workbook. Although, using it again after restore does not maximize the window.

Shift + F5:

“Shift + F5” keys when hold together opens the “Find and Replace” Window.

Alt + Shift + F5:

“Alt + Shift + F5” keys don’t provide any output.

Ctrl + Shift + F5:

“Ctrl + Shift + F5” keys don’t provide any output.

Alt + Ctrl + F5:

“Alt + Ctrl + F5” keys don’t provide any output.

Alt + Ctrl + Shift + F5:

“Alt + Ctrl + Shift + F5” don’t provide any output.

F6 Key:

“F6” key is used to switch between the panes of an excel workbook if you have a split window in the current sheet of your excel workbook. If your sheet is not split then “F6” key will shift between the help task pane and application window.

Alt + F6:

“Alt + F6” keys don’t provide any output.

Ctrl + F6:

Using “Ctrl + F6” keys you can switch to the previous excel workbook if you have multiple workbooks open.

Shift + F6:

“Shift + F6” keys works same as “F6” key.

Alt + Shift + F6:

“Alt + Shift + F6” keys don’t provide any output.

Ctrl + Shift + F6:

“Ctrl + Shift + F6” keys works same as “Ctrl + F6” key.

Alt + Ctrl + F6:

“Alt + Ctrl + F6” keys don’t provide any output.

Alt + Ctrl + Shift + F6:

“Alt + Ctrl + Shift + F6” don’t provide any output.

F7 Key:

“F7” key opens the “Spelling” option in excel using which you can check the spellings in your workbook. It provides the same output as you get by navigating to “Review” Tab on top of excel ribbon and then clicking on “Spelling” button.

Alt + F7:

“Alt + F7” keys don’t provide any output.

Ctrl + F7:

“Ctrl + F7” keys move the excel window of the current workbook which is open. However it works only if your workbook is in a restored window.

Shift + F7:

“Shift + F7” opens the “Research” task pane in your excel workbook. It provides the same output as you get by navigating to “Review” Tab on top of excel ribbon and then clicking on “Research” button.

Alt + Shift + F7:

“Alt +Shift + F7” keys don’t provide any output.

Ctrl + Shift + F7:

“Ctrl +Shift + F7” keys don’t provide any output.

Alt + Ctrl + F7:

“Ctrl +Shift + F7” keys don’t provide any output.

Alt + Ctrl + Shift + F7:

“Alt + Ctrl + Shift + F7” don’t provide any output.

F8 Key:

“F8” key enables and disables “Extend Mode” selection in excel. By enabling “extend mode” you can select multiple cells at a time. Likewise by disabling “Extend Mode” the cell selection would be set to one cell at a time.

Alt + F8:

“Alt + F8” keys open the “Macros” dialog box as shown in below image:

 

Ctrl + F8:

“Ctrl + F8” keys move the excel window of the current workbook which is open. However it works only if your workbook is in a restored window. It provides the same output as “Ctrl + F7” keys.

Shift + F8:

“Shift + F8” keys provide you an option to select cell or multiple cells or a range of cells using arrow keys.

Alt + Shift + F8:

“Alt +Shift + F8” keys don’t provide any output.

Ctrl + Shift + F8

“Ctrl +Shift + F8” keys don’t provide any output.

Alt + Ctrl + F8

“Alt +Ctrl + F8” keys don’t provide any output.

Alt + Ctrl + Shift + F8:

“Alt + Ctrl + Shift + F8” don’t provide any output.

F9 Key:

F9 key recalculates output of all formulas in your excel workbook. Please note that it will update all the formulas present in various sheet of the open excel workbook.

If you are editing a cell having a formula and press F9 key then it will replace the cell having formula with the value of that formula.

Alt + F9:

“Alt + F9” keys don’t provide any output.

Ctrl + F9:

Using “Ctrl + F9” keys your excel window will be minimized. Although pressing it again would not maximize the screen again.

Shift + F9:

The function of “Shift + F9” keys is same as “F9” key. It recalculates all the formulas in your excel workbook.

Alt + Shift + F9:

“Alt + Shift + F9” keys don’t provide any output.

Ctrl + Shift + F9:

“Ctrl + Shift + F9” keys don’t provide any output.

Alt + Ctrl + F9:

The function of “Alt + Ctrl+ F9” keys is same as “F9” key or “Shift + F9” keys. It recalculates all the formulas in your excel workbook.

Alt + Ctrl + Shift + F9:

Using “Alt + Shift + Ctrl + F9” keys, all the dependent formulas are rechecked and recalculated in all the open workbooks. It also works for the cell which are not marked for recalculation.

F10 Key:

Using “F10” key you get the Excel Tips – the keyboard shortcuts for excel ribbon. Its output is same as obtained by pressing the “Alt” key.

Alt + F10:

“Alt + F10” keys display the selection window in excel as shown in below image.

 

Ctrl + F10:

“Ctrl + F10” keys resizes or restores the excel window if it is open in maximum window first. Similarly it maximizes the excel window if it is open in restored window initially.

Shift + F10:

“Shift + F10” keys display the excel shortcut window also known as “Context Menu” for the selected cell. Its output is same as you obtain by right clicking the mouse on the selected cell.

If “Help Window” of Excel is opened then it displays the set of commands for Help Window.

Alt + Shift + F10:

“Alt + Shift + F10” keys displays the menu for the “Smart Tag” if only a single “Smart Tag” is present in your workbook. In case of multiple “Smart Tags” it simply toggles to next smart tag and displays its menu.

Ctrl + Shift + F10:

“Ctrl + Shift + F10” keys works same as “Shift + F10” keys.

Alt + Ctrl + F10:

“Alt + Ctrl + F10” keys don’t provide any output.

Alt + Ctrl + Shift + F10:

“Alt + Ctrl + Shift + F10” keys don’t provide any output.

F11 Key:

“F11” key adds a chart to your excel. If you hit F11 after selecting a specific data range then it uses the same range in chart.

Alt + F11:

“Alt + F11” keys open the Microsoft Visual Basic Editor. If VB Editor is already open then by pressing this key you can toggle between the Excel workbook and editor.

Ctrl + F11:

“Ctrl + F11” keys add a macro sheet in your currently open excel workbook with a default name like “Macro1, Macro2, etc”. These “Macro Sheets” were actually used to store macros in the excel versions prior to Excel 97. They are not being used anymore.

Shift + F11:

“Shift + F11” keys insert a “New Sheet” in your currently open excel workbook. The new sheet is added on the left hand side of the sheet where you are currently working.

Alt + Shift + F11:

“Alt + Shift + F11” displays the Microsoft Script editor for your current open excel workbook. Microsoft Editor was an additional tool provided by Microsoft in version from MS Office 2000 to 2007. It allows you to work in a large variety of codes in office products ranging from: HTML code, DHTML objects, Java Script and VB Script.

Ctrl + Shift + F11:

“Ctrl + Shift + F11” keys don’t provide any output.

Alt + Ctrl + F11:

“Alt + Ctrl + F11” keys don’t provide any output.

Alt + Ctrl + Shift + F11:

“Alt + Ctrl + Shift + F11” keys don’t provide any output.

F12 Key:

“F12” key opens the “Save As” dialog box to save your current open excel workbook. You can also use “Alt + F2” for same output.

Alt + F12:

“Alt + F12” keys don’t provide any output.

Ctrl + F12:

This set of keys “Ctrl + F12” when pressed together opens the “File Open Dialog Box”. It is same as “Ctrl + O” function and opens below window. You can also use “Alt + Ctrl + F2” for the same output as well.

Shift + F12:

This set of keys “Shift + F12” when pressed together saves the workbook. It is same as “Ctrl + S” keys. You can also use “Alt + Shift + F2” for same output as well.

Alt + Shift + F12:

“Alt + Shift + F12” keys don’t provide any output.

Ctrl + Shift + F12:

“Ctrl + Shift + F12” keys open the print options. “Ctrl + F2” keys and “Alt + Ctrl + Shift + F2” keys also do the same function.

Alt + Ctrl + F12:

“Alt + Ctrl + F12” keys don’t provide any output.

Alt + Ctrl + Shift + F12:

“Alt +Ctrl + Shift + F12” keys don’t provide any output.

So, these were the 96 types of ways in which you can use the 12 excel functions keys (F1 to F12).

Named Range in Excel

When and Why to Use Named Range:

When you normally enter data into columns of a spreadsheet you tend to give column headers so that the data in that particular column is much understandable. But when you use this data for various functions then all you use is the data range instead of the column headers. Named range is the name given to list of data, you can use this name in formulas to make them easier to understand.

Have a look at below image to get an insight. In this example, I have given a Name – “Sales_of_Apple_in_US_in_KGS” to the sales of apples in US available from Jan 2014 to Sep 2014, making it easier to understand.

So, this is how a “named range” makes your formula easier to understand.

How to create a Named Range:

You have two methods to create a named range in excel. We have detailed both of them in this section.

Method 1: Create Named Range using “Formulas Tab” Define Name Option:

You need to follow below listed steps to create a “Named Range” in your excel using “Define Name” option under “Formulas Tab”.

•       Select the “Data range” which you want to name.

•       Click on “Formulas Tab” available on Excel Ribbon.

•       Click on “Define Name” button as shown in below image:

•       Now enter the desired name for named range in “Name” text-box and click on “OK” button once done.

 

Now, your data range is having a name that could be easily referred in formulas.

Method 2: Create Named Range using Name Box:

If you are looking for a much easier way of adding named range to your data instead of hopping onto a number of tabs, then this method is definitely for you.

Follow below steps to create a named range using name box:

•       Select the “data range” for which you want to create a named range.

•       Click on the “Name Box” which appears next to the “Formula bar” wherein the cell number is usually updated as shown in below image:

•       Now enter the “name” of this range and hit “enter button”.

That’s all your data range will be named after this and could be easily referred.

Name Manager in Excel:

Using Name Manager you can list all the “Named Ranges” that are present in the current workbook. Name Manager gives you a wide variety of operations to perform, which are listed below:

 

•       Create – Using this you can create new ranges in the workbook.

•       Edit – Using this you can edit any existing range created in your workbook.

•       Delete – As the name implies, using this you can delete the existing names created in your workbook.

•       Filter – Filter gives you many options as depicted in below image to sort the listed names.

 

How to Use Name Manager:

There are two options available to check the name manager.

Method 1: Selecting Name Manager from Excel Ribbon:

Below listed steps need to be followed to view Name Manager:

•       Navigate to “Formulas Tab” in Excel.

•       Click on “Name Manager” button available as shown in below image:

 

Method 2: Keyboard Shortcut for Name Manager:

Name Manager could be used easily by holding “Ctrl + F3” Keys together.

Deleting Named Ranges:

Named Ranges could be deleted using Name Manager only. All you need to do is to follow below steps to delete any named ranges available in your excel workbook.

•       Select “Name Manager” from “Formula Tab” or by holding “Ctrl + F3” Buttons together.

•       Once the “Name Manager Window” opens, select the range which you want to delete.

•       Click on “Delete” Button available on “Name Manager Window” as shown in below image:

 

•       Click on “OK button” on the Confirmation Page.

 

After this the selected “Name” will be deleted from your workbook.

So, this was all about Named Range functionality and various ways to use it in Excel.

How to Use Excel Sparklines

Excel Sparklines were introduced in Excel 2010 version. It is small line chart that could be easily embedded with the text and gives a great presentable output for easier depiction when aligned next to the tabular data. They are supported in Excel 2010 and above version only. Although sparkline’s design is not limited to lines they can also be represented as columns or win loss. Below example depicts how sparklines look:

 

When to Use :

Excel Sparklines can be useful if you have your data in a tabular format. You can place the sparklines next to each row to give a clear graphical presentation of the data selected in that particular row.

How to Use :

Below listed steps give a clear understanding on how to use excel sparklines:

•       Click on the cell where you want to insert the “Sparkline(s)”.

•       Go the “Insert” tab on the top ribbon.

•       Select the Type of “Sparkline” (Line, Column, Win/ Loss), in this case we will take “Line Sparkline” as example.

•       After the above selection a dialog box will open to select the data range for creating sparklines as shown in below image:

 

•       You can enter the data range manually by typing in the details or you can use your mouse to select the data range on excel and it will automatically get reflected in the dialog box.

•       After selecting the data range you can simply click on “OK” button and sparklines will be added in each row as shown in below image:

So, this was all about how to use excel sparklines. You can also do formatting, add markers, axis, etc to enhance the sparklines. Below section suggests on type of sparklines and their formatting.

Type of Excel Sparklines :

•       Line – These sparklines are displayed in the format of simple lines. You could change the style of line, sparkline color and marker color.

 

•       Column – These sparklines are displayed in the format of bars. If the data is of positive value then the column would be lying on the upper axis. For negative data value the column would be below the axis. Likewise for zero value the column would not be displayed and an empty space would be left at that data point. In these sparklines too, you can change the style of column, sparkline color and marker color.

•       Win / Loss – These sparklines are displayed in the format of bars like “Column” sparklines although these only represent profit or loss. If the data is showing profit (positive data points) then bars would be above the axis and if the data is showing loss (negative data points) then the bars would be inverted and will be below the axis. If the data is of zero value then the column would not be displayed and an empty space would be left at that data point, as shown in above figure as well. In these sparklines as well, you can change the style of column, sparkline color and marker color.

You can change the type of sparklines for the whole group or for single row by un-grouping the sparklines first. Thus each row can have a different type of sparkline.

Formatting Excel Sparklines :

After creating the sparklines you can easily edit \ format sparklines by selecting the sparklines and then by clicking on “Design” tab available at top ribbon. Below are the various options available for formatting sparklines in Excel 2010 and above versions. Below image suggests the various actions that could be performed on sparklines. Most of them are self – explanatory, however below sections provide detailed overview about all of these.

Edit Data: Using Edit Data you can either change the data range or the location of sparklines or both. Follow below steps to change data range and \ or location of sparklines.

•       Edit Group Location and Data – You can select this option if you want to change the location and \ or data range for all the sparklines in the group.

You can achieve this by clicking on the entire cells where sparklines are displayed and then selecting “Edit Group Location and Data” from “Edit Data” option from “Design” tab in the ribbon.

•       Edit Single Sparkline’s Location and Data – You can select this option if you want to change the location and \ or data range of a single sparkline.

You can achieve this by clicking on the cell where sparkline is displayed and then select “Edit Single Sparkline’s Location and Data” from “Edit Data” option from “Design” tab in the ribbon.

•       Hidden and Empty Cells in Data Range – You can use this feature if the data range used in your sparklines is having some empty cells or hidden cells.

o  Empty cells – If you are having empty or blank cells then you can choose how to show them in sparkline.

§   Gaps – You can choose to show the empty cells as “Gaps” by selecting the Radio Button against “Gaps”, as shown in below image.

§   Zero – You can choose to show the empty cells as “Zero” by selecting the Radio Button against “Zero”, as shown in below image.

§   Connect Data Points – Else you can ignore the empty cells by selecting “Connect data points with line”, as shown in below image.

 

o  Hidden cells – If you have some hidden rows or columns in the then you can easily select how you want these cells to be displayed in Excel.

§   Show Data of Hidden Rows or Columns – You can show the data of the hidden columns or rows by selecting the check box under “Design” tab in “Edit Data” option for “Hidden and Empty Cell Option”.

§   Hide Data of Hidden Rows or Columns – You can hide the data of the hidden cells by simply unchecking the check-box.

Excel Sparkline Highlights:

Highlight is a feature that allows you to show the important points in sparklines by using high points, low points, first and last points, etc detailed in below section.

 

•       High Point – By selecting this you can highlight the highest points of data in the sparklines. It works in all types of the sparklines (line, columns and win/loss) as shown in above image.

•       Low Point – By selecting this you highlight the lowest points of data in the sparklines. It also works for all types of the sparklines (line, columns and win/loss) as shown in above image.

•       Negative Point – By selecting this you can highlight the negative points of data (data having value less than zero). It works in all types of the sparklines (line, columns and win/loss) as shown in above image.

•       First Point – It highlights the starting point of the data in the sparklines as shown in above image.

•       Last Point – It highlights the end point of the data in the sparklines as shown in above image.

•       Markers – It highlights all the data points (highest, lowest, first, last and everything) in all types of sparklines as shown in above image.

How to Style your Sparklines:

Using the “Style” section available under “Design” tab you can change the color of lines and columns of sparklines using a pre-defined set of designs.

•       Sparkline Color – Using this option you can change the color of the sparkline and also change the weight of the line.

•       Marker Color – Using this you can change the color of markers (high, low, negative, first and last point).

•       Axis – This section gives you an option to change scaling and visibility details of horizontal and vertical axis of sparkline.

How to Group \ Ungroup Sparklines:

If you are using multiple sparklines in a single sheet then you could group them by selecting “Group” option. By grouping you could easily apply all the changes in format, style, color, weight, etc to all the sparklines available in the group. If you want each sparkline to be unique then you can ungroup the sparklines by selecting “Ungroup” option. Below listed steps could be used to Group the Sparklines:

•       Select the set of sparklines which you want to “Group”. It is not necessary that the sparklines should be adjacent.

•       After selecting the sparklines, go to the Design Tab and select “Group” Option.

•       Now the grouping is done and you could easily apply same set of styles to the grouped sparklines.

Now, same set of style is applied to the Group Sparklines, as shown in below image.

Likewise, you can select the “Ungroup” option to ungroup the sparklines.

How to Clear a Sparklines:

If you want to erase the sparkline and remove its data, location range everything then you could select the “Clear” option. To achieve this just follow below steps:

•       Select the Cell(s) whose sparklines have to be removed.

•       Go to “Design” tab and select clear option.

After this the selected sparklines will be removed, as if they didn’t exist. This was all about excel sparklines. Hope this article helps you out to make your excels much more lively using sparklines.

Adding Current Date and Time Stamp in Excel

Sometimes it happens that you might want to enter the current date and time stamp in your excel spreadsheets automatically. This need usually arrives because of various reasons, in my case I was urged to find a solution to it as I need to do some team management wherein I required the time my associates are spending on work daily. So, in this case adding the current date and time stamp manually is a very tedious task. Thanks to MS Excel that there are certain functions and keyboard shortcuts to do it on the fly.

Now, let’s jump quickly onto the actual topic of adding current date and time stamp in excel. There are three simple ways as listed below of doing it. You can follow anyone of them depending on your needs.

Method 1. Add Current Date using Keyboard Shortcut

Brief about Keyboard Shortcut (Ctrl + ;):

•       The main advantages of using this method is that being a keyboard shortcut it’s pretty much handy and easier to use multiple times.



•       Its output gives you only current date no time stamp. Hence if you need only the date then you can quickly go for it.

•       It does not get refreshed after opening the spreadsheet every time. Hence if you are looking for a feature just to add current date without getting it refreshed then you should select this method.

How to Use it:

Below listed steps have to be followed to use Current Date using a Keyboard Shortcut:

•       Click on cell wherein you want to add current date.


Right click on the selected cell and select “FORMAT CELLS” option.

•       Select “Category” as date.

•       Click on “OK” button once you are done with the formatting.

•       Now click on the formatted cell and press and hold “Ctrl Key” on Keyboard along with this press semi-colon key (;)

•       Release “Semi-Colon Key” and “Ctrl Key” of keyboard.

•       After this you will be able to see the current date in the cell as depicted in below image

 

If you need the date to be updated every time you open excel then this shortcut will not be able to help you out. However, don’t lose heart we have other functions listed below which would cater to your needs.

Method 2. Add Current Date using Excel Function TODAY():

Brief about TODAY Function:

•       Its output gives you current date. Hence if you need only the date then you can quickly go for it.

•       It gets refreshed after opening the spreadsheet every time. Hence if you are looking for a current date that should get updated every time you open excel then go for it.

How to use Excel Function – TODAY()

•       Click on cell wherein you want to add current date.

•       Right click on the selected cell and select “FORMAT CELLS” option.

•       Select “Category” as date.

Click on “OK” Button once you are done with the formatting.

•       Now click on the formatted cell and type TODAY to display the now function as shown in below image.

 

•       After typing << =TODAY() >> in the cell, simply hit “Enter” button or just click outside the cell.

After this you will be able to see the current date in the cell as depicted in below image:

Note: Please note that there are no

arguments passed in Today Function, i.e. its correct formula is =TODAY()

So this was about using Today function using which you will get date as the only output and this will get auto-refreshed every time you open the excel or press F9 key. However, if you are looking for a function that gives you both date and time as output and also gets refreshed then you can use below function.

3. Add Current Date and Time Stamp using Excel Function NOW():

Brief about NOW Function:

•       Its output gives you both current date and time stamp. Hence if you need both the date and time stamp then you can quickly go for it.

•       It gets refreshed after opening the spreadsheet every time. Hence if you are looking for a current date and time stamp that should get updated every time you open excel then go for it.

How to use Excel Function – NOW()

•       Click on cell wherein you want to add current date.

•       Right click on the selected cell and select “FORMAT CELLS” option.

•       Select “Category” as custom.

•       Click on “OK” button once you are done with the formatting.

Now click on the formatted cell and type “NOW” to display the now function as shown in below image.

 

•       After typing << =NOW() >> in the cell, simply hit “Enter” button or just click outside the cell.

After this you will be able to see the current date and times tamp in the cell as depicted in below image:

 

Note: Please note that there are no arguments passed in Now Function, i.e. its correct formula is =NOW()

That was all from my end. Do let us know if these functions helped you out or in case you are using some other function to do this job which is not listed in this article.


Sum Cells based on Background Color

Few weeks back, one of my regular visitors dropped me an email describing an issue that he was facing. His task was to add the contents of certain cells based on their background colors.

As we all know, Excel by default has no formula or feature to calculate such a thing. So, in this post I will share few methods that will help you to achieve this.

To make the task more clear let’s have a look at the below image.

 

This image depicts that here we don’t need the total sum of all the elements but instead we want the sum of elements that have the same background color.

Recommended Reading:Weighted SUM in Excel

Method 1: SUM cells on the basis of background colour using SUMIF Formula:

We know that SUMIF function is a combination ofSUMandIF formulaand hence SUMIF can come quite handy for adding cells based on color.

If you don’t know how to use a SUMIF Function, then before going any further I would strongly suggest you toread this post.

First of all let’s try to understand how we are going to do this:

Consider we have a table as shown in the below image.

 

Next, we will add one more column to this table where we will manually type the background colors of their adjacent cells as shown in the below image.

 

Now, we try to use SUMIF Formula for finding the SUM of cells with yellow background as:=SUMIF(B2:B13,"Yellow",A2:A13)

 

Similarly, for finding the SUM of Orange and Green background cells we will use the formulas=SUMIF(B2:B13,"Orange",A2:A13) and =SUMIF(B2:B13,"Green",A2:A13) respe ctively.

 

But, as we can see that this method is quite cumbersome, particularly if we need to use this on lists with hundreds of elements.

So, what’s the faster alternative?

To make the above process easier to use we need to reduce the effort of writing cell background colors manually.

So, for this task we can use a small user defined function (UDF) which will do the trick for us.

 

Note: This function does not return the color name but it returns the color index which is also a unique value and can be used in our task.

Follow the below steps to use the UDF:

1.  First of all open your worksheet where you need to add the cells based on background colors.

2.  Next, press ALT + F11 to open theVB Editor. Navigate to ‘Insert’ > ‘Module’.

3.  After this, paste the “ColorIndex” UDF in the Editor.

 

4.  Now, add one column next to the range that you wish to sum up. In this new column enter the formula as:

=ColorIndex(<refrence_ofcell_whose_background_colour_index_you_wish_know>)

 

5.  After that, drag this formula to the whole range.

 

6.  Now, you can use the SUMIF function to add the cells that have same background color as shown in the above image.

Method 2 : Using a much faster and better UDF:

The UDF that we are going to use in this method is simply an extension of the above used function.

This Function is as under:

 

How to use this UDF:

Follow the below steps to use this Function:

1.  Open your target worksheet.

2.  Press ALT + F11 to open the VBA Editor and navigate to ‘Insert’ > ‘Module’.

3.  Paste the “SumByColor” Function in the Editor.

 

4.  Now, simply type the “SumByColor” function to call it and pass the following arguments:

=SumByColor(<cell_with_background_color_that_you_wish_sum>,

<Range_to_be_summed_up>)

 

Note:In the formula shown in above image instead of the first argument “A2” we could have also used any one of A2, A5, A8, A10, A12. Because all these cells have yellow background.

In our case we can use the following formulas:

•    Sum of Yellow Cells: =SumByColor(A2,A2:A13) [As ‘A2’ is the address of yellow cell and A2: A13 is the range to be added]

•    Sum of Orange Cells: =SumByColor(A3,A2:A13) [As ‘A3’ is the address of orange cell and A2: A13 is the range to be added]

•    Sum of Green Cells: =SumByColor(A4,A2:A13) [As ‘A2’ is the address of green cell and A2: A13 is the range to be added]

So, this was all from me about this topic. Don’t forget to download the sample spreadsheet [link] and do let me know in case you face any issues while using these methods.

Printing Comments in Excel – Few Easy Ways

An Excel workbook with comments looks more presentable and easier to understand. But do you know that, by default Excel only allows you to see comments on the screen. In other words, it has made the option of printing comments very inconspicuous.

Now, what if you have to print those comments along with your data?

No need to worry. In this post, I will highlight some methods that will help you to print comments in your spreadsheets.

Recommended Reading :How to Hide GridLines in Excel

So here we go:

Option 1: Printing Comments in Excel from Page Layout Tab:

In this method we are going to use the “Page Setup” property in Excel to print comments. Follow the below steps to do this:

1.     First of all you have to ensure that your comments are visible in the worksheet. If comments are not shown in the display, then they would not show up in the print.

 

So, to check this navigate to the “Review” tab in ribbon and ensure that the button “Show All Comments” is clicked. After this all the comments in your worksheet will be visible.

If you don’t wish to show a particular comment then, you can select the cell that contains the comment and then hide it using the “Show/Hide Comment” button present in the “Review tab”.

Alternatively, you can also do this from the Right Click menu.

 

2.     Next, navigate to the “Page Layout” tab in the Ribbon and click the small “More” button near the “Sheet Options” as shown.

 

3.     Clicking “More” button will open the “Page Setup” window. In the “Sheet” tab of “Page Setup” window, click the comments drop down. This will have three options as follows:

 

o       None: Selecting this option means that you don’t wish to print comments at all. This is the default option.

o       At the end of Sheet: Selecting this option will print the comments.

But the comments will not be linked with the cells, rather they will be printed on a separate sheet.

o       As displayed on the Sheet: This option will print the comments as they are displayed on the screen.

4.  Select any one of the last two options and click “Ok”. And it’s done.

5.  Now you can simply press the Ctrl + P keys to open the print dialog or to see the print preview.

Bonus Tip: For those who want to perform this using the shortcut keys, here is the shortcut.

Alt + P S O Alt + M

This will open the comments dropdown. Select and value from the dropdown and press “Enter”.

Option 2: Printing comments from the Print page:

In this method we are going to print comments from the Print preview page. Follow the below steps to do this:

1.  First of all similar to the step 1 of Option 1, make sure that the comments on your worksheet are visible.

2.  Next, navigate to the Print page, by pressing Ctrl + P and click the “Page Setup” link as shown.

 

3.  This will open the “Page Setup” window. On the “Page Setup” window navigate to “Sheet”Tab.

 

4.  Next, in the comments dropdown select any option except “None” and click “Ok”. If you need to know more about these options then see the Step 3 of Option 1.

5.  This will do the trick and now you can simply print your worksheet.

Option 3: Printing comments in Excel using VBA:

For printing comments in VBA you can use the below line of code:

 

 Also, before this you would need to add the following line of code to make all the worksheet comments visible.

Application.DisplayCommentIndicator = xlCommentAndIndicator

So, this was all about how to print comments in excel. Do let us know in case you face any problems while implementing any of the methods explained in this tutorial.

Hiding Gridlines in Excel

Hiding gridlines in Excel is a common task and most of the Excel users should know about it. It makes your spreadsheet clean and presentable. Although grid lines in excel have their own benefits but in some cases it is better to hide them.

What are Gridlines?

A spreadsheet contains cells and gridlines or gridbars represent the borders of these cells. Grid lines are the faint lines on your spreadsheet that help you to distinguish the cell boundaries.

 

Uses of Gridlines:

1.  They make it easier for you to align text or objects by giving you a visual cue.

2.  They help you to distinguish between cell boundaries.

3.  They make your data-tables more readable especially when they are without a border.

Grid lines are hidden during printing but if you want you can show them explicitly.

Method 1: Hide Excel Gridlines using the option in ribbon:

Excel has a default option to hide these mesh lines.

For Excel 2007, 2010, 2013:

•       Navigate to the “View” tab on the Excel ribbon.

 

•       Uncheck the “Gridlines” checkbox and the gridbars will be hidden.

•       Or you can also hide the grid lines from “Page layout” and uncheck the Gridlines “View” option.

 

For Excel 2003 and Earlier:

•       Navigate to “Tools” > “Options”.

 

•       Now, a dialog box will open. Navigate to the “View” tab and then uncheck the “Gridlines” option and click ‘OK’. This will hide these annoying lines.

Method 2: Make Gridbars invisible by changing background colour:

Another obvious way to hide the grid lines in excel is by changing their background colour so that it matches the worksheet background. This can be done by using following steps:

•       First of all select all the rows and columns of the spreadsheet by pressing

“Ctrl + A”. Alternatively you can also click the little triangle icon under the “Name box”.

•       With all the cells selected, click the “Fill Color” option and select the white colour.

 

•       Now, all the gridlines will be hidden.

Method 3: Hiding gridlines by using the shortcut key:

If you are someone who love to see keyboard as compared to mouse then here is the shortcut key for you. You can use the “Alt + WVG” key combination to hide the excel gridbars.

Method 4: Hide spreadsheet gridlines using a Marco:

If you want to hide the grid lines by using a macro then you can use the below code. This code hides the gridbars if they are visible but if the grid lines are already hidden then it displays a message saying “Grid lines are already hidden!”

   

Hide Grid lines when you are printing the sheet:

To hide the grid lines while printing the sheet follow below steps:

•       Navigate to “Page Layout” Tab and uncheck the “Print” option under Grid lines area. Or you can also use the “ALT + PPG” key combination to do this.

•       After this print the spreadsheet and the grid lines won’t be visible.

How to bring the grid lines back if they are already hidden:

If your grid lines are invisible by default and you want to show them up, then just check back the gridlines option that we have unchecked in the Method 1. Alternatively you can also press the keys “Alt + WVG” to make the gridbars visible again.

So, this was all how to hide gridlines in excel. Do share your thoughts and ideas related to the topic.

Excel Nested If’s – Explained

In our last post we talked aboutIF Statement, which is one of the most important functions in Excel.  The limitation of IF statement is that it has only two outcomes. But if you are dealing with multiple conditions then Excel Nested If’s can come very handy.

Nested if’s are the formulas that are formed by multiple if statements one inside another. This nesting makes it possible for a single formula to take multiple decisions. In Excel 2003 nesting was only possible up to 7 levels but Excel 2007 has increased this number to 64.

Syntax of Excel Nested If formula:

The syntax of Nested If statement is as follows:

=IF(Condition_1,Value_if_True_1,IF(Condition_2,Value_if_True_2,Value_if_Fal se_2))

Here, ‘Condition_1’ refers to the condition used in the first IF.

‘Value_if_True_1’ will be the result if first IF statement is True.

‘Condition_2’ is the condition used in the second IF. The second IF will only come into pictue when the First IF statement results a False value.

‘Value_if_True_2’ will be the result if second IF statement is True.

‘Value_if_False_2’ will be the result if second IF statement is False.

 This is equivalent to:

IF Condition1 THEN

Value_if_True1

THEN

Value_if_True2

ELSE

Value_if_False2

END IF

Example of Nested IF’s in Excel:

Now, let’s understand Nested If’s with an example.

Example 1:

In the below image an Employee table of a company is shown. The company decides to give bonus to its employees but their bonus criteria is quite strange. As you can see in the below image they are giving 20% bonus to the North Region Employees, 30% to the South Region Employees, 40% to the East Region Employees and 50% to the West Region Employees.

In this case we can use Excel Nested IF formula to find the bonus for each employee. The formula can be:

=IF(B2="North","20%",IF(B2="South","30%",IF(B2="East","40%",IF(B2="West","5

0%", "Region is Invalid"))))

The formula is quite simple, it just checks if ‘B2’ (cell that contains region details for first employee) is equal to “North”, then value should be 20%, if not then check if B2 is equal to “South”, if yes then value should be 30%, if not move on to next IF statement and so on.

Similarly for the second employee the formula would be:

=IF(B3="North","20%",IF(B3="South","30%",IF(B3="East","40%",IF(B3="West","5

0%", "Region is Invalid"))))

In this case I have handled another important thing i.e. If the Region does not matches with any one of the IF conditions then the output should be “Region is Invalid”.

Example 2:

In the second example we have a table of students and their scores. Now based on their scores we have to give a grade to the students.

 

Students with scores below 40 are considered as “Fail”, scores between 41 and 60 are considered “Grade C”, scores between 61 and 75 are considered “Grade B” and scores between 76 and 100 are considered as “Grade A”

In this scenario we can use a nested If formula as:

=IF(B2<=40,"Fail",IF(AND(B2>=41,B2<=60),"Grade

C",IF(AND(B2>=61,B2<=75),"Grade B",IF(AND(B2>=76,B2<=100),"Grade A"))))

This formula just checks if B2 (cell containing the score of first student) is less than or equal to 40, if true then the value should be “Fail” if not then check the next IF condition and so on.

You can see that here in the inner-most IF statement I haven’t used the ‘Value_if_False’ , it is perfectly alright to omit this parameter in such a case. In-case all the IF conditions in this formula will result into a False value then the formula will simply return a ‘FALSE’ keyword.

How to Hide Formulas in Excel

Using formulas in excel can make your day to day tasks easier. Excel provides formulas for just about anything. But if you are sharing your spreadsheets with other people then it looks quite rude to write formulas as it is.

All the formulas and the calculation stuff should be hidden from the viewers. The best way to do this is to hide your formulas in excel sheet.

Today I will be sharing few tips on how to hide formulas in Excel.

1. Toggling the ‘Show Formulas’ option in Excel:

This method is simplest of all. To toggle ‘Show Formulas’ option in excel simply follow the below steps:

 

•       With an excel sheet opened, navigate to the ‘Formulas’ tab.

•       There you will see an option ‘Show Formulas’, clicking once on that button will reveal all the formulas and clicking twice will hide the formulas.

•       Instead of performing step 2 you can simply hit “ Ctrl + ~ “ keys on keyboard, pressing the keys once will show all the formulas and pressing them again will make the formulas hidden.

As you can see that this method is not completely fool-proof because the formula is still visible in the top formula bar. It only helps to hide your formulas from showing up inside the cells.

2. Replacing the formula with Values:

This is an easy way to get rid of all your formulas at once. Here we are going to replace the formula with the value that it has resulted into. This will remove all your formulas and will replace them with corresponding values, use this method only if you don’t need the formulas again.

 

•       Select the range where you have applied the formula.

•       Now right click and select the ‘Paste Special’ option from the menu.

•       After the paste special window gets opened select the radio button ‘Values’ and click ‘OK’.

•       This will replace all the formulas with their values.

This method is not suitable if you might need the formula again in near future. In such cases the next method comes quite handy.

3. Hiding the formula by protecting the Excel sheet:

If you want to hide your formulas temporarily and use them again at your wish. Then you should try hiding formulas by protecting the worksheet. In this method we will first hide the cell formulas and then lock the complete excel sheet. Follow below steps to do this:

•       Firstly select all the cells for which you have applied the formula. ?        After this, right click and select the option ‘Format Cells’.

•       Next, in the ‘Format Cells’ window navigate to the protection tab.

•       In protection tab, check the ‘Hidden’ checkbox and click ‘OK’.

 

•       One thing that I want to make clear is that just checking the hidden checkbox can’t do anything until you have protected the excel sheet.

•       To protect the excel sheet navigate to the ‘Review’ tab, select the option ‘Protect Sheet’.

•       After this the system will ask you to enter your password, after entering the password click ‘OK’.

 

•       Then re-enter the Password and all your excel formulas are hidden.

To view you formulas again simply visit the ‘Review tab’ again and unprotect the sheet. After this uncheck the hidden option in the ‘Protection’ tab of format cells.

So, this was all about how to hide formulas in excel. If you know any other trick then please do share them with us.

 Convert pounds to KGs, meters to yards and tsps to table spoons

You need not ask Google if you need to convert 156 lbs to kilograms or find out how much 12 tea spoons of olive oil actually means. The hidden convert() function is really versatile and can convert many things to so many other things, except one currency to another, of course.

 

Example: Use CONVERT(150,"lbm","kg") to convert 150 lbs to 68.03 kgs.

Use CONVERT(12,"tsp","oz") to findout that 12 tsps is actually 2 ounces.

Convert those lengthy nested if functions to one simple formula with Choose()

Planning to create a gradebook or something using excel, you are bound to write some if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this) returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE.Learn more about spreadsheet if functions like countif, sumif etc.

Where as choose(m, value1, value2, value3, value4 ) can return any of the value1,2.., based on the parameter m.

Example: Use CHOOSE(3,"when","in","doubt","just","choose") to get doubt

Remember, you can always write another formula for each of the n parameters of choose() so that based on input condition (in this case 3), another formula is evaluated.

Formula Debugging in Excel – Tip [spreadcheats]

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing it.

When I learned IF() formula for the first time, I have spent a whole Sunday morning debugging a stupid error in a grade calculation formula.

So as part of ourspreadcheatsseries, we will learn a handy trick you can use to debug formulas and fix the errors quickly.

Assuming we have a moderately lengthy formula like this

=IF(AVERAGE(B2:B6)<=AVERAGE(C2:C6),MAX(B2:B6),MAX(C2:C6)) and we want to know where the error is occurring

1.      Select the cell with formula.

2.      Now click on the formula bar

3.      Just select the parts the formula and press F9 (for eg: the first average() formula)

4.      This will evaluate only the selected part and replaces it with the result. Like this:

 

5.      Using this technique you can narrow down the errors to particular range or values causing it.

6.      Now that you know where the error is occurring you can wrap that part of formula with an ISERROR() formula to avoid unpleasant surprises.



3