How to Select Cells Quickly and Easily in Excel [Solved]

How to Select Cells Quickly and Easily in Excel

Excel LogoOnce you get used to using Excel, you can find that using the mouse to select data in your spreadsheet is somewhat slow and time consuming. Here’s a quick technique for selecting a range of cells in Excel.

  • Move the active cell (that’s the cell currently selected) to the start of the range of cells you want to select.
  • Then, hold the SHIFT key down and use the arrow keys on the keyboard to move in the direction of the last cell in the range of cells you want to select.
    • Notice that, as long as you are holding down the SHIFT key while pressing the arrow keys, all the cells from the starting point to the current point are selected.
  • Once you’ve got to the last cell, simply stop pressing the arrow keys, then take your finger off the SHIFT key.

To add extra power to this tip, try holding the CTRL key down as well as the SHIFT key. You’ll find that the active cell jumps across empty sections of the spreadsheet, or across non-empty sections. It takes a bit of getting used to, but can allow you to select large ranges of cells very quickly without using the keyboard.

Credit: http://fiveminutelessons.com/learn-microsoft-excel/select-cells-quickly-and-easily-excel-using-keyboard-instead-mouse

How To Import Data Into an Excel Spreadsheet

How To Import Data Into an Excel Spreadsheet

Excel 2013If you have data from an alternative source, you may be able to import it into Excel 2010 instead of having to re-enter all the information again.  Depending on the type of data you would like to import, you can select from a number of options.  We’ll look at one of the most common import types – a text file.

Import CSV

  1. Open the worksheet you would like to import the data to and select the cell where you would like to data import to start.
  2. Click the Data tab and choose from the options in the Get External Data group.
    Get External Data
    NOTE: For this example, we will import a text file.
  3. Click From Text. This will allow text file types such as prn, txt and csv to be imported.
  4. Browse to find the file you would like to import, select it and click Import.
    Import Text File
  5. The Text Import Wizard appears to guide you through the process of importing your data.
    Import CSV - Step 1
  6. Select Delimited if the text contains a character such as a comma, tab, space or semi-colon to separate the various fields.  Otherwise select Fixed Width if there are a certain number of spaces between each field.
    NOTE: in this tutorial, we will be looking at the Delimited option.
  7. A preview of the file is displayed below.  If you would like to start the import at a row other than 1, enter the selected row number in the Start import at row field.
  8. Click Next.
  9. Select type of character that separates the various fields.  You can select as many as are applicable.  If you would like to include your own characters that aren’t listed, select the Other checkbox and enter the specific character in the field provided.
    Import CSV - Step 2
    NOTE: If you have some fields where the selected delimiter appears several times in a row, you should select the Treat consecutive delimiters as one checkbox.  This ensures that you won’t have several blank fields where only the delimiter character was found.
  10. A preview of the data in columns appears below, according to the delimiter selected. Click Next.
  11. You now need to choose the format for each of the columns.  Select the column heading in the Data preview and then select a data type from the Column data format options.
    Import CSV - Step 3
    NOTE: If there are some columns that you don’t want to have included in the text to columns conversion, select the Do not import column (skip) option for the data type of those specific columns.
  12. Once you have selected the data type for each column, click Finish.
  13. Select where you would like the imported data to be in the workbook and click OK.
    Import Data
  14. Your imported data will now appear in your selected worksheet.

That’s it for importing data into Excel 2010.  Hope you found it useful – don’t forget to take a look at some of the other tutorials about working in Excel 2010.

Credit: http://pcunleashed.com/excel/how-to-import-data-into-an-excel-spreadsheet/

How to Link an Excel Table to Powerpoint

How to Link an Excel Table to Powerpoint

Excel LogoWhen you convert your Excel data to a table and create a link between the table and PowerPivot, changes to the Excel data can be passed through to PowerPivot using the Update All icon on the PowerPivot tab of the Excel ribbon.

Follow these steps to convert your normal Excel data to a table:

    1. You should have a single row of headings above the data. If your headings use more than one row, you should convert them to a single row. One easy way to do this is to enter the first heading cell, press Alt+Enter, and type the second heading cell.
    2. Select one cell in your data and press Ctrl+T.
    3. Excel will show you the range of data. Provided you have no blank rows or columns, this will be correct.
    1. Excel will apply a default format to your data. If you don’t like the formatting, open the Table Styles drop-down and choose a new style. Note that the very first style in the gallery contains no formatting.
    1. Look on the left side of the Table Tools Design tab. Excel chose an unimaginative name along the lines of Table1 for this table. Type a new name that describes the data.
    2. On the PowerPivot ribbon tab, choose Create Linked Table. Excel will open the PowerPivot window.

You will see your data appear in the PowerPivot window. Additional ribbon tabs and icons are available in the PowerPivot window.

Using Copy and Paste or Linked Tables will work when your data is small enough to fit in the 1,048,576 rows available in Excel. When your data is larger than that, you will need to import the data from another format.

Credit: http://www.quepublishing.com/articles/article.aspx?p=1716292&seqNum=3

How to Use Slicers in Excel

How to Use Slicers in Excel

Excel 2010 makes it possible to insert slicers to quickly and easily filter pivot tables. However, using the report filter gives the exact same result.

Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot table.

Two-dimensional Pivot Table in Excel

To insert a slicer, execute the following steps.

1. Click any cell inside the pivot table. The PivotTable Tools contextual tab activates.

2. On the Options tab, insert a slicer.

Insert a Slicer

3. Check Category and click OK.

Check Category

4. For example, Click Fruit to only show the fruit exported to each country.

Slicer Example

Note: notice how the report filter changed to Fruit. Hold down CTRL to include fruit and vegetables.

Credit: http://www.excel-easy.com/examples/slicers.html

How to Use Cell Styles in Excel

How to Use Cell Styles in Excel

excel_for_mac_2011_iconQuickly format a cell by choosing a cell style. You can also create your own cell style. Quickly format a range of cells by choosing a table style.

1. For example, select cell B2 below.

Select a Cell

2. On the Home tab, in the Styles group, choose a cell style.

Choose a Cell Style

Result.

Cell Style in Excel

To create your own cell style, execute the following steps.

3. On the Home tab, in the Styles group, click the bottom right down arrow.

Click Bottom Right Down Arrow

Here you can find many more cell styles.

4. Click New Cell Style.

Click New Cell Style

5. Enter a name and click the Format button to define the Number Format, Alignment, Font, Border, Fill and Protection of your cell style. Simply uncheck a check box if you don’t want to control this type of formatting.

6. Click OK.

Style Includes

7. On the Home tab, in the Styles group, apply your own cell style.

Apply Your Own Cell Style

Result.

Your own Cell Style in Excel

Note: right click a cell style to modify or delete it. Modifying a cell style affects all cells in a workbook that use that cell style. This can save a lot of time. A cell style is stored in the workbook where you create it. Open a new workbook and click on Merge Styles (under New Cell Style) to import a cell style (leave the old workbook with the cell style open).

Credit: http://www.excel-easy.com/examples/cell-styles.html

How to Use Flash Fill in Excel

How to Use Flash Fill in Excel

Excel 2013Excel 2013’s great new Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes.

The series of entries appear in the new column, literally in a flash (thus, the name Flash Fill), the moment Excel detects a pattern in your initial data entry that enables it to figure out the data you want to copy. The beauty is that all this happens without the need for you to construct or copy any kind of formula.

Rather than manually entering first, middle, or last names in respective columns (or attempting to copy an entire client name from column A and then editing out the parts not needed in the First Name, Middle Name, and Last Name columns), you can use Flash Fill to quickly and effectively do the job. And here’s how you do it:

1. Type Keith in cell B2 and complete the entry with the down-arrow or Enter key.

When you complete this entry with the down-arrow key or Enter key on your keyboard, Excel moves the cell pointer to cell B3, where you have to type only the first letter of the next name for Flash Fill to get the picture.

2. In Cell B3, type only J, the first letter of Jonas, the second client’s first name.

Flash Fill immediately does an AutoFill type maneuver by suggesting the rest of the second client’s first name, Jonas, as the text to enter in this cell. At the same time, Flash Fill suggests entering all the remaining first names from the full names in column A in column B.

3. Complete the entry of Jonas in cell B3 by clicking the Enter button or pressing an arrow key.

The moment you complete the data entry in cell B3, the First Name column is done: Excel enters all the other first names in column B at the same time!

To complete this example name table by entering the middle and last names in columns C and D, respectively, you simply repeat these steps in those columns. You enter the first middle name, Austen, from cell A2 in cell C2 and then type W in cell C3.

Complete the entry in cell C3 and the middle name entries in that column are done. Likewise, you enter the first last name, Harper, from cell A2 in cell D2 and then type S in cell D3. Complete the entry in cell D3, and the last name entries for column D are done, finishing the entire data table.

Credit: http://www.dummies.com/how-to/content/how-to-use-flash-fill-in-excel-2013.html

How to Create a Chart or Graph using Excel [Solved]

How to Create a Chart or Graph using ExcelExcel Logo

Step 1 – Launch Excel – If Excel is already open on your workstation open a new Excel workbook, There are three ways to do that.
1. Go to the Standard toolbar. Click on the New Workbook button.
2.
Go to the File menu. Select New.
3.
Use a keyboard combination: on a Macintosh use Command + N and on a Windows computer use Ctrl + N

Step 2 Enter the data to be graphed. For the purpose of this lesson you will use data from a Favorite Fruit Survey. Enter it as you see below:

Step 3 – Highlight data to be graphed. Do not include the row with heading titles, only the names of fruit and the numbers. If your worksheet looks like the one above; put your cursor in call A2, click hold the mouse button down and drag to cell B7. Highlighted data should look like the image below:


Note: Cell A2 is selected, the select color extends around the cell
Step 4 – Select the Chart Wizard. That is done by going to the Insert menu and selecting Chart. You can also click on the Chart Wizard button on the Standard toolbar.

Step 5 – From the Chart Wizard box that opens select Chart type. For this activity, I selected pie.

After you have selected the Chart type, click and hold your mouse pointer down on the Press and Hold… button to see what your data looks like in the chart type you selected. If you do not like the look, select another chart type. After you have selected the chart type you will have two options:

  • Select Next and let Chart Wizard show you a series of options to make changes to your chart.
  • Select Finish and Chart Wizard puts your completed chart on the spreadsheet. You can see the finished product below.

The second step taken by Chart Wizard is to verify the range of data being used for this chart. The Data range displayed below is read “all cells from A2 to B7.”

Notice where the cursor is located in the dialog box above. It is pointing to the small box at the end of the line where the Data range is displayed. If the data range should be changed, click on the box the cursor is pointing to.

The dialog box shrinks allowing you to see your entire spreadsheet. You can edit the data range in this small window. When you are finished, click the same box at the end to restore the window.

Select Next to go to the dialog box below. This box allows you to add a title to the chart, make changes on the legend, or make changes on the data labels.

Select Next to move to the final dialog box which allows you to see the chart as a new sheet or place it on one of the sheets in your workbook.

If you let the Chart Wizard finish your chart after the first dialog box, or work through each of the four steps, your chart will look something like the one below.

Credit: http://www.internet4classrooms.com/excel_create_chart.htm

How to Use AutoFill in Excel [Solved]

How to Use AutoFill in Excel

Excel 2013Your spreadsheet from the previous section should look like this one:

Spreadsheet from previous section

You have a title in cell A1, but nothing else. You’ll now see how to use the AutoFill feature of Excell to quickly enter the days of the week. Off we go, then.

Click inside cell B3 of your spreadsheet, and type Monday, as in the image below:

Enter Monday in cell B3

The days of the week are going to be entered on Row 3 of our spreadsheet, from cell B3 to cell H3. Fortunately, you don’t have to type them all out. You can use something called AutoFill to complete a known sequence like days of the week. In other words, Excel will do it all for us.

  • Position your mouse pointer to the bottom right of the B3 cell
  • The mouse pointer will change to a black cross, as in the images below. The image on the left shows the normal white cross; the image on the right, the black cross, tells you AutoFill is available:

The normal cursor      The AutoFill cursor

  • When you can see the AutoFill cursor, hold down your left mouse button and drag to the right
  • Drag your mouse all the way to cell H3, as in the following image:

Drag to cell H3

  • When your cursor is in the H3 cell, let go of the left mouse button
  • Excel will now complete the days of the week:

AutoFill has completed the days of the week

And that’s all there is too it! AutoFill can be a handy tool to use, when you want to complete a known sequence like days of the week, months, and even formulas. We’ll use AutoFill on a column of numbers, shortly. But let’s crack on with our spreadsheet

Now that we’ve got a heading for the spreadsheet, as well as the days of the week, we can enter a few chocolate bars.

  • Click inside cell A4 and enter the name of a chocolate bar. You can enter anything you like, but we’ve gone for Mars Bars. In cell A5 we chose Twix, and in cell A6 Bounty. In cell A7 we typed Other
  • In cell A9 of you spreadsheet enter the words Day Totals. Leave cell A8 blank. Your spreadsheet should then look something like ours below:

The spreadsheet so far

Time to enter some numbers.

  • Click inside cell B4, and enter the number 1. Press the enter key on your keyboard, and the active cell will jump down to cell B5
  • In cell B5 type the number 7. Press the Enter key again to jump down to cell B6
  • In cell B6 type 8
  • In cell B7 type 1
  • Your spreadsheet will then look like this one:
The Monday column now has numbers

To complete the numbers for the rest of the week, enter the following under each heading:

Tuesday: 2, 5, 3, 2
Wednesday: 1, 3, 2, 2
Thursday: 3, 2, 3, 2
Friday: 3, 4, 4, 2
Saturday: 2, 2, 1, 1
Sunday: 5, 4, 4, 1

When you’re done, your spreadsheet will look like this:

All the days of the week now have numbers

 Now that you have all those numbers typed out, we can move on to formulas, as you learn how to add up in Excel.

Credit: http://www.homeandlearn.co.uk/excel2007/excel2007s2p2.html

How to Create a Pie Chart in Excel [Solved]

How to Create a Pie Chart in Excel

Excel LogoPie charts are quite easy to create in Excel 2007 and Excel 2010. In case you’re not sure what a Pie Chart is, here’s the basic one you’ll be creating. Later, you’ll add some formatting to this:

An Excel 2007 Pie Chart

 To make a start, you need to highlight some data. If you’ve been following along with theprevious tutorials, then you’ll have some viewing figures data. You’ve created a 2D chart with the BBC data. This time we’ll use the ITV data. If you don’t have this data, create the following simple spreadsheet. The cells to use are D4 to E14:

The Data for the Excel 2007 Pie Chart

  • Click inside cell E4 and change “Millions” to ITV, if you already have the data from a previous lesson
  • Highlight the cells D4 to E14
  • Click the Insert menu at the top of Excel
  • Locate the Chart panel, and the Pie item:

The Charts Panel

In Excel 2013, the Pie chart is harder to spot. But it’s highlighted in green in the image below:

Pie Chart icon in Excel 2013

Click the down arrow and select the first Pie chart:

Available Pie Charts in Excel 2007

  • A new Pie chart is inserted
  • Move your new pie chart by dragging it to a new location
  • Notice how all the segments of the pie chart are the same colour in Excel 2007:

A Default Pie Chart

To get different colours, make sure that your chart is selected and locate the Chart Style panel:

Chart Styles in Excel 2007

Click the down arrow to the right of the Chart Style panel to reveal the available styles :

Available Pie Chart Styles

We’ve gone for the second one, Style two. If you haven’t got this style, select a similar one, such as style 4 in Excel 2013. The chart will then look like this (your labels may well be at the bottom, though, depending on which version of Excel you have):

Style Two Pie Chart

 But it looks pretty good for just a few mouse clicks! We can still do a bit more to it, though. In the next part, you’ll see how to add the viewing figures to the pie chart segments.

 Credit: http://www.homeandlearn.co.uk/excel2007/excel2007s3p8.html

How to Force a Page Break in Excel [Solved]

How to Force a Page Break in Excel

Excel LogoAs your worksheet gets larger, there may be times when you want to force Excel to starting printing on a new page. For instance, you might want only the first fifteen rows of information on the first page, then the balance of the worksheet on the second printout page. To make this happen, follow these steps:

  1. Make sure cell A16 is selected.
  2. Choose Page Break from the Insert menu. A dashed line appears between rows 15 and 16.
  3. Print your worksheet as normal.

If you want to later remove the page break, follow these steps:

  1. Make sure cell A16 is selected.
  2. Choose Remove Page Break from the Insert menu. The dashed line disappears.

Credit: http://excel.tips.net/T001930_Forcing_a_Page_Break.html