Basic Guide To MS-Excel (Newer Versions)

Create a new workbook

A workbook is a file that contains one or more worksheets to help you organize data. You can create a new workbook from a blank workbook or a template.

Create a workbook

  1. Open Excel.
  2. Select Blank workbook or press Ctrl+N.
  3. Start typing.

Create a workbook from a template

  1. Select File > New.
  2. Double-click a template.
  3. Click and start typing.

Insert or delete a worksheet

Insert a worksheet

  • Select the  Select  plus icon at the bottom of the screen.
  • Or, select Home > Insert > Insert Sheet.

Rename a worksheet

  • Double-click the sheet name on the Sheet tab to quickly rename it.
  • Or, right-click on the Sheet tab, click Rename, and type a new name.

Delete a worksheet

  • Right-click the Sheet tab and select Delete Delete.
  • Or, select the sheet, and then select Home > Delete > Delete Sheet.

Move or copy worksheets or worksheet data

Move a worksheet within a workbook

  • Select the worksheet tab, and drag it to where you want it.

    Note: Be aware that calculations or charts that are based on worksheet data might become inaccurate if you move the worksheet.

     

Copy a worksheet in the same workbook

  • Press CTRL and drag the worksheet tab to the tab location you want.

OR

  1. Right click on the worksheet tab and select Move or Copy.
  2. Select the Create a copy checkbox.
  3. Under Before sheet, select where you want to place the copy.
  4. Select OK.

Insert or delete cells, rows, and columns

Insert or delete a column

  1. To insert a column, select the column, select Home > Insert >  Insert Sheet Columns.
  2. To delete a column, select the column, select Home > Insert > Delete Sheet Columns.

    Or, right-click the top of the column, and then select Insert or Delete.

Insert or delete a row

  1. To insert a row, select the row, select Home > Insert > Insert Sheet Rows.
  2. To delete a row, select the row, select Home > Insert > Delete Sheet Rows.

    Or, right-click the selected row, and then select Insert or Delete.

Insert a cell

  1. Select one or more cells. Right-click and select Insert.
  2. From the Insert box, select a row, column or cell to insert.

Select cell contents in Excel

Select one or more cells

  1. Click on a cell to select it. Or use the keyboard to navigate to it and select it.
  2. To select a range, select a cell, then hold the right bottom edge and drag over the cell range. Or use the Shift + arrow keys to select the range.
  3. To select non-adjacent cells and cell ranges, hold Ctrl and select the cells.

Select one or more rows and columns

  1. Select the letter at the top to select the entire column. Or click on any cell in the column and then press Ctrl + Space.
  2. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space.
  3. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.

Select table, list or worksheet

  1. To select a list or table, select a cell in the list or table and press Ctrl + A.
  2. To select the entire worksheet, press Ctrl + A + A. Or use the Select All button at the top left corner.

Move or copy cells and cell contents

Use CutCopy, and Paste to move or copy cell contents. Or copy specific contents or attributes from the cells. For example, copy the resulting value of a formula without copying the formula, or copy only the formula.

When you move or copy a cell, Excel moves or copies the cell, including formulas and their resulting values, cell formats, and comments.

You can move cells in Excel by drag and dropping or using the Cut and Paste commands.

Move cells by drag and dropping

  1. Select the cells or range of cells that you want to move or copy.
  2. Point to the border of the selection.
  3. When the pointer becomes a move pointer move pointer , drag the cell or range of cells to another location.

Move cells by using Cut and Paste

  1. Select a cell or a cell range.
  2. Select Home > Cut Cut or press Ctrl + X.
  3. Select a cell where you want to move the data.
  4. Select Home > Paste Paste or press Ctrl + V.

Copy cells in your worksheet using the Copy and Paste commands.

  1. Select the cell or range of cells.
  2. Select Copy or press Ctrl + C.
  3. Select Paste or press Ctrl + V.


Resize a table, column, or row

Resize rows

  1. Select a row or a range of rows.
  2. Select Format > Row Width.
  3. Type the row width and select OK.

Resize columns

  1. Select a column or a range of columns.
  2. Select Format > Column Width.
    Select column
  3. Type the column width and select OK.

Automatically resize all columns and rows to fit the data

  1. Select the Select All button Select All at the top of the worksheet, to select all columns and rows.
  2. Double-click a boundary. All columns or rows resize to fit the data.

Find or replace text and numbers on a worksheet

Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or text string.

  1. On the Home tab, in the Editing group, click Find & Select.

  2. Do one of the following:

    • To find text or numbers, click Find.
    • To find and replace text or numbers, click Replace.
  3. In the Find what box, type the text or numbers you want to search for, or click the arrow in the Find what box, and then click a recent search in the list.

    You can use wildcard characters, such as an asterisk (*) or a question mark (?), in your search criteria:

    • Use the asterisk to find any string of characters. For example, s*d finds “sad” and “started”.
    • Use the question mark to find any single character. For example, s?t finds “sat” and “set”.

      Tip: You can find asterisks, question marks, and tilde characters (~) in worksheet data by preceding them with a tilde character in the Find what box. For example, to find data that contain “?”, you would type ~? as your search criteria.

  4. Click Options to further define your search if needed:
    • To search for data in a worksheet or in an entire workbook, in the Within box, select Sheet or Workbook.
    • To search for data in rows or columns, in the Search box, click By Rows or By Columns.
    • To search for data with specific details, in the Look in box, click FormulasValues, or Comments.

      Note:   FormulasValues and Comments are only available on the Find tab; only Formulas are available on the Replace tab.

    • To search for case-sensitive data, select the Match case check box.
    • To search for cells that contain just the characters that you typed in the Find what box, select the Match entire cell contents check box.
  5. If you want to search for text or numbers with specific formatting, click Format, and then make your selections in the Find Format dialog box.

    Tip: If you want to find cells that just match a specific format, you can delete any criteria in the Find whatbox, and then select a specific cell format as an example. Click the arrow next to Format, click Choose Format From Cell, and then click the cell that has the formatting that you want to search for.

  6. Do one of the following:
    • To find text or numbers, click Find All or Find Next.

      Tip: When you click Find All, every occurrence of the criteria that you are searching for will be listed, and clicking a specific occurrence in the list will make a cell active. You can sort the results of a Find Allsearch by clicking a column heading.

    • To replace text or numbers, type the replacement characters in the Replace with box (or leave this box blank to replace the characters with nothing), and then click Find or Find All.

      Note: If the Replace with box is not available, click the Replace tab.

      If needed, you can cancel a search in progress by pressing ESC.

  7. To replace the highlighted occurrence or all occurrences of the found characters, click Replace or Replace All.

Merge and unmerge cells

Merge cells

  1. Select the cells to merge.
  2. Select Merge & Center.

    Important: When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

Unmerge cells

  1. Select the Merge & Center down arrow.
  2. Select Unmerge Cells.

Available number formats

  1. Select a cell or a cell range.
  2. On the Home tab, select Number from the drop-down.

    Or, you can choose one of these options:

    • Press CTRL + 1 and select Number.
    • Right-click the cell or cell range, select Format Cells… , and select Number.
    • Select the dialog box launcher Alignment Settings next to Number Dialog box launcher and then select Number.

    3. Select the format you want.

     


Overview of formulas

Create a formula that refers to values in other cells

  1. Select a cell.
  2. Type the equal sign =.

    Note: Formulas in Excel always begin with the equal sign.

  3. Select a cell or type its address in the selected cell.

    select cell

  4. Enter an operator. For example, – for subtraction.
  5. Select the next cell, or type its address in the selected cell.

    next cell

  6. Press Enter. The result of the calculation appears in the cell with the formula.

To see a formula

  1. When a formula is entered into a cell, it also appears in the Formula bar.

    Formula Bar

  2. To see a formula, select a cell, and it will appear in the formula bar.

    See formula bar

Enter a formula that contains a built-in function

  1. Select an empty cell.
  2. Type an equal sign = and then type a function. For example, =SUM for getting the total sales.
  3. Type an opening parenthesis (.
  4. Select the range of cells, and then type a closing parenthesis).

    range

  5. Press Enter to get the result.

SUM function

The SUM function, one of the math and trig functions, adds values. You can add individual values, cell references or ranges or a mix of all three.

For example:

  • =SUM(A2:A10)
  • =SUM(A2:A10, C2:C10)


COUNTIF function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

  • =COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

  • =COUNTIF(A2:A5,”London”)
  • =COUNTIF(A2:A5,A4)


IF function

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).


Create and format tables

  1. Select a cell within your data.

  2. Select Home Format as Table.
  3. Choose a style for your table.
  4. In the Format as Table dialog box, set your cell range.
  5. Mark if your table has headers.
  6. Select OK.

Create a chart from start to finish

Create a chart

  1. Select data for the chart.
  2. Select Insert > Recommended Charts.
  3. Select a chart on the Recommended Charts tab, to preview the chart.

    Note: You can select the data you want in the chart and press ALT + F1 to create a chart immediately, but it might not be the best chart for the data. If you don’t see a chart you like, select the All Charts tab to see all chart types.

  4. Select a chart.
  5. Select OK.

Add a trendline

  1. Select a chart.
  2. Select Design > Add Chart Element.
  3. Select Trendline and then select the type of trendline you want, such as Linear, Exponential, Linear Forecast, or Moving Average.


Add or remove titles in a chart

Add a chart title

  1. In the chart, select the “Chart Title” box and type in a title.
  2. Select the plus sign.
  3. Select the arrow next to Chart Title.
  4. Select Centered Overlay to lay the title over the chart, or More Options for additional choices.
  5. Right-click the chart title to format it. with options like Fill or Outline.

Update a chart title

  1. Select a chart title.
  2. Add an equal sign to the formula bar.
  3. Select the cell you want to link any title changes to.
  4. Press Enter.

Leave a Reply

%d bloggers like this: