Excel shortcuts and tricks

Introduction

Excel is a very powerful tool, and it can be intimidating to use. It has many features that are beyond the scope of this article, but here are some shortcuts I use when working with Excel for my job

The idea behind these shortcuts is so that instead of having one person standing at their keyboard typing out long formulas using two hands while other people stand around watching... we can take turns sitting at one computer each with our laptops open showing what we want done and doing it ourselves without waiting around for someone else who may not ever show up again! So if anyone wants any tips or tricks regarding using excel just message me through comments section below please!!! Hope this helps everyone out there! Bye :*(

how to select multiple cells

In this section, we'll cover how to select multiple cells.

First, you need a way of selecting all the cells in your workbook. You can use Ctrl+A or click on the cell with your mouse and then press Ctrl-A (or Command+A on Mac).
Then, you should be able to see all the data in one place and make changes without having to move around like crazy every time. To do this: 1) Click anywhere inside any cell; 2) Hold down Shift while clicking any additional cells; 3) Release Shift when done selecting everything

how to select row or column by using shortcut keys

To select row or column, press SHIFT key and click on the desired row or column number.
To select multiple rows and columns at once, hold down ALT (or Ctrl) while clicking on the desired range.

How to insert or delete roe or column using shortcut key.

You can insert or delete rows and columns using shortcut keys.

To insert a new row, press Ctrl+H (or Cmd+H on MacOS) to display the Home tab and then choose Insert > Row.
To delete a row, press Ctrl+D (or Cmd+D) to display the Home tab and then choose Delete > Row.

How to hide rows or columns using shortcut key.

You can hide rows or columns by using the shortcut keys.

Press Ctrl+H to hide all visible rows.
Press Ctrl+Shift+H to hide all visible columns.

How to unhide rows or columns using shortcut key.

The first thing you should do is to select the cells that you want to unhide.

If you are using a mouse, click on the row or column header and drag it to another position.
You can also use keyboard shortcuts like Ctrl + H or Shift + H to hide/unhide rows or columns respectively.

This can save time

Excel has a number of shortcuts and tricks that can save you time. If you use the ribbon as much as I do, these shortcuts will come in handy for any task.

Ctrl + A selects all cells in a column or row (minus headers)
Ctrl + C copies one cell to another location on your worksheet
Enter opens a new blank cell (or inserts data into an existing one)

1) CTRL + PgDn & PgUp : Switch between open worksheets.

CTRL + PgDn & PgUp : Switch between open worksheets.
CTRL + SHIFT + Z : Undo the last action you took in a workbook, or any number of actions that were performed on a specific sheet.
SHIFT+F11: Open Microsoft Excel’s Spell Checker window (this is included with every copy of Microsoft Office). This can be useful if you want to double-check your typing before submitting an online form such as one found on Gmail or Facebook!

2) CTRL + S : Save the workbook.

CTRL + S : Save the workbook.

This will save your progress, so you don’t lose any data.

3) CTRL + SPACEBAR : Select the entire column.

CTRL + SPACEBAR : Select the entire column. This shortcut does not work in Excel 2019, but it does in previous versions of Excel if you're using Office 365 or other Office apps that support importing data from older versions of Microsoft Office.

4) CTRL + ARROW key : Move to the edge of the current data region in a worksheet.

CTRL + ARROW key : Move to the edge of the current data region in a worksheet.

This shortcut will move you to the start or end of a column or row on your worksheet, depending on which direction you press it. If you want to go back one cell and then right, hold down CTRL while pressing the up arrow key. This is useful if you're doing some kind of data entry and need to enter numbers at certain positions in columns or rows (e.g., "123456789" would be entered at cell A2).

5) Ctrl + F1 : Hide or display the ribbon.

Ctrl + F1 : Hide or display the ribbon

This is a great way to hide all of your tools until you need them. You can also use this shortcut to open or close all tabs at once, which can be useful when working with large spreadsheets.

6) SHIFT + F11 : Insert a new worksheet.

Press SHIFT + F11 to open the Insert ribbon.
Click on New Worksheet in the bottom left corner of this window, as shown below:
Type a name for your new worksheet and click OK, then you're done. This shortcut will insert a blank worksheet into Excel!

7) Shift + Spacebar : Select the entire row.

If you want to select the entire row and not just a single cell, press Shift + Spacebar. The current selection will be highlighted in red, but this won't affect any other cells or text in that column.

If you have multiple rows selected at once then hold down Shift when clicking on a cell in the first row (or click on any other location within that column). This will then move all of those items into their own separate groups which can be manipulated individually by using keyboard shortcuts such as Ctrl+C (copy) or Ctrl+V (paste).

8) Shift + F3 : Insert a formula with a function.

>Insert a formula with a function. Just press Shift+F3, and you'll see the Insert Function dialog box. Click on one of the available functions in this window, or type your own name for a custom function if needed.

9) Alt, H, O, I : Automatically inserts rows and columns in a worksheet to fit the size of your data or selections.

9) Alt, H, O, I : Automatically inserts rows and columns in a worksheet to fit the size of your data or selections.
This shortcut is useful when you have an Excel workbook with many different sheets that contain similar data. For example: if you have a sheet that contains all of your sales figures for each product line, there might be some duplicates on this sheet (e.g., if one product line has different quantities sold over time). You can use this shortcut to automatically insert new rows/columns into any empty cells so that they are sorted by their respective values (i.e., lowest value first).

Takeaway:

Takeaway: The shortcuts and tricks in this article will help you get more done in Excel.

3D cell references

If you're familiar with Excel's worksheet, then you should know that it has an object called a formula bar. This is where all your formulas are displayed and edited. To create a formula, click on this bar and then type in your text: (1 + 2) * 3. The result will be rounded to the nearest multiple of 5, which means it'll display as 5 in the cell:

The great thing about this shortcut is that once you have created it once (and saved it), you can use this little trick every time!

AutoComplete

AutoComplete is a handy feature that can be used to speed up your work, but it's also a great way to save time in other ways. You can use AutoComplete for all sorts of things, like typing in a name or phone number or email address and having the program automatically fill in what you're missing. This will save you some precious seconds by doing the tedious part for you instead of making you do it manually every time.

You can also use AutoComplete when typing names into textboxes (like those found on form fields), so that as soon as you start typing something with an obvious match, Excel will show suggested words based on context. For example: if there are two possible locations within a city called "New York," then maybe one could be "New York City" while another might be "Manhattan." With this feature enabled, Excel will provide suggestions based on what word(s) were typed before them—helping make sure users don't get stuck looking for those same types of places over and over again!

Open files in new windows

To open a file in a new window, right-click on the file and select “Open With”. Then select Excel or another program whose functionality you wish to use.

Shortcut to insert a hyperlink

If you want to insert a hyperlink, simply press Ctrl+H.

Insert a hyperlink from clipboard text

A hyperlink is a link that you can click to take the user to another web page, website or document. Hyperlinks are usually invisible but provide the means for users to navigate around websites and locate information quickly.

The way this shortcut works is pretty simple: You copy some text on your clipboard (for example, "www.excel-tips."), then press Ctrl+V (or Cmd+V if you're using a Mac). The program will insert an image link into your document and save it as an image with the same name as what was copied in Step 1 above!

Open the link in a browser directly from Excel

You can click on the cell and then click the small triangle in the top left corner, which will open up your browser as if you had just opened it from there.

This is useful when you want to view a web page from within Excel but don't want to use any new software or apps.

Remove all hyperlinks at once

To remove all hyperlinks at once, use the following formula:

=HYPERLINK(“http://www.example.com")

If you want to remove just one or two hyperlinks, use this formula:

=HYPERLINK(“http://www.example.com”,"link_to_first")

Format cells as date or time even if they look right already

Format cells as date or time even if they look right already.
If you have a cell that looks like it's formatted correctly, but is not, try using the following shortcut: CTRL + T. This will format all cells in the selected range as dates and times (if applicable).

Copy formatting with the Format Painter tool

The Format Painter tool is a handy way to copy formatting from one cell or range to another. You can also use it to copy text and get rid of any formatting that may have been applied by the user, such as bolding or italics.

To use this feature, select any cells or ranges in the active document and then click on the Format Painter icon in the Home tab (or press Ctrl + B). When you do so, Excel will display a menu where you can choose from various options for copying what you see on one cell onto another; select "Format Painter" for an example of how this works:

Apply conditional formatting to blank cells

You can apply conditional formatting to blank cells.

For example, if you have a column of numbers and want to display a different color or background depending on whether the value is positive or negative, apply the following formula:=IF(ISBLANK(VLOOKUP($B$2:$B$11,1)),”#FF0000","

Clear all conditional formatting rules at once

This allows you to clear all conditional formatting rules at once.

To do this, right-click on the cell and choose Conditional Formatting > Clear All Rules.

Remove all comments at once

If you've used Excel to create a chart and have comments on it, there's a way to remove them all at once. This can be useful if you're going through a large spreadsheet and want to make sure that no comments remain.

To do this:

Click in the cell with your comment(s) in it, then press Ctrl+A (or Cmd+A on Mac). The entire column will be highlighted as one item—this is what we're going to use for our example below.

Edit multiple worksheets at the same time

If you want to edit multiple worksheets at once, use the Ctrl+Shift+N keyboard shortcut. This will open all the worksheets in your workbook and let you make changes on each one individually. You can also use this shortcut if you want to close a document without saving it or closing the whole Excel window.

Use this tricks to design your excel program

Here are some useful Excel shortcuts and tricks to make your life easier:

Use VBA to repeat a function multiple times: You can use this trick to repeat the same function on different cells. For example, you can write something like this in a cell: =SUM(A1:A1000) and then press shift+Enter or ctrl+shift+Enter. This will now add each number from A1 through A1000 into its own row within that column! It's that simple! Just remember that if you want an item from another list (or other parts of your spreadsheet), it will need its own variable name so that it doesn't overwrite anything else's values when repeated over many lines of data later down the road."

Use Alt+Enter when inputting data for a new line.

When you are working on a new line of data, use the Alt key and enter. This will insert a line break in Excel.

If you have multiple rows of data that need to be input and separated by tabs, simply press Shift+Enter instead of using Alt+Enter.

Use F4 to repeat the last action.

If you need to repeat an action, use F4. This will bring up a dialog box where you can select an event or task to repeat.

When you want to delete a column or row, right-click on the column number or row letter and select Delete.

To delete a column or row, right-click on the column number or row letter and select Delete.

To enter a formula in multiple cells at once, start by typing the formula in the first cell and then Copy that cell and Paste it into as many other cells as you need.

To copy a cell:

Select the cell or range you want to copy (you can also select multiple rows or columns)
Right-click on the selected area and select [Copy] from the context menu

Instead of copying a cell and pasting it, Double-click on the cell you want to copy and then drag it to where you want to paste it. It will even copy formulas correctly!

To copy a cell, right-click on the cell and choose Copy. Then, right-click on the location where you want to paste the copied cells and choose Paste Special:

If you want to sort Excel data, don't select all of the rows and columns that contain your data -- only select the rows and columns that have data in them.

This is because Excel uses a formula (or function) called RANK() to determine how many rows should be included in each group of data. In other words, if there are 100 items in your list, but only 50 are actually needed for sorting purposes (because they're not relevant), then only 50 will be included in any given group.

You can use VLOOKUP instead of HLOOKUP if you turn your spreadsheet sideways.

If you're using Excel, you can use VLOOKUP instead of HLOOKUP. You simply need to turn your spreadsheet sideways and then edit the formula in this way:

=VLOOKUP(A1,B2:B10, 2 , FALSE)

The first argument is the column index (or row number), so if your data looks like this:

Column A - 1st value from column A

Column B - 2nd value from column B...etc...

The Find function is incredibly powerful! You can use it to find formulas, text, numbers or formatting with just one click of a button.

For example, if you want to find all the cells that have an amount in them and then copy and paste them somewhere else on your worksheet (or even pasted into another document), this is how you would use the Find command:

Go to cell A2 (or whatever cell you want)
Click on any empty row above or below where your cursor is located at. This will highlight all cells between these two points. For example, if we wanted our current selection highlighted along with its neighboring rows above/below where we had selected our starting point then we would have highlighted B1 through B7 by clicking somewhere in between those two spots instead of directly over either row

Subtotals can be added automatically in any row or column in an Excel spreadsheet by selecting Subtotal from the Data menu option.

Subtotals can be added automatically in any row or column in an Excel spreadsheet by selecting Subtotal from the Data menu option. The rows will then be highlighted, with a subtotal icon next to each one. Clicking this icon will add a subtotal of that column's values, allowing you to quickly see how much has been filled in at a glance.

The Freeze Panes option can be used to make the top row or leftmost column visible even when scrolled down or across a large spreadsheet by selecting Freeze Panes from the Window > Freeze Panes menu option in Excel.

The Freeze Panes option can be used to make the top row or leftmost column visible even when scrolled down or across a large spreadsheet by selecting Freeze Panes from the Window > Freeze Panes menu option in Excel. This makes it easier for you to see what's going on at any given time, especially when there's a lot of content in your workbook.

The Freeze Panes feature offers several benefits:

It lets you keep track of important information without constantly scrolling around as you work on your spreadsheets;
It prevents data loss due to accidental deletions (which would happen if someone accidentally hit Delete while they were trying to move something); and
It allows users who have trouble viewing small objects such as photos or charts (for example) because those items are too small for their eyesight--or because they're simply bad at reading text--to still get access all relevant information without having everything else hidden away behind multiple layers of folders/pages stacked up one after another like an accordion folder filled with paper clips instead

To select a range of cells, click the first cell and drag your mouse to the last cell. Then, press SHIFT + CTRL + up/down arrow to select the whole range of cells.

To copy multiple rows or columns from one sheet to another:

Press Ctrl+A (select all) on your keyboard
Select a column on the left side of your screen with your mouse cursor then drag it over onto an empty area on the right side of your screen

If you want to copy a formula to multiple cells without changing the references, you can do so by pressing CTRL+` (the key above TAB)

If you want to copy a formula to multiple cells without changing the references, you can do so by pressing CTRL+` (the key above TAB). This will paste the formula into each cell in turn.

When copying a formula from one row and pasting it into another, be careful not to accidentally move the cursor off of any other rows or columns as this could cause errors in your workbook.

You can remove all non-numeric characters from a column using this formula =VALUE(A1)

You can remove all non-numeric characters from a column using this formula:

=VALUE(A1)

This will remove any character other than numbers or special characters (such as “$”) from the value of A1.

You can delete duplicate entries using Remove Duplicates function in data tab.

To delete duplicate entries, you need to use Remove Duplicates function in the data tab. The following screenshot shows how it is done:

The Remove Duplicates function will remove all duplicates from your worksheet and not just those which have been manually added by you or anyone else.

Selecting Non-Adjacent Cells - Hold Ctrl and then click on each cell that you want to select on your spread sheet. To deselect one of the non-adjacent cells, hold Ctrl and click on that desired cell again.

Conclusion

We hope you enjoyed our guide to Excel shortcuts and tricks. We only covered a few of the many ways that Excel can be used, and there are plenty more! You’re probably wondering why we didn’t talk about some of them. Well, it’s because we don’t have time for everything! But if any of these tips seem useful or fun in your workday, definitely try them out!