Ctrl+Tab switches to the next tab in a dialog box, or (if no dialog box is open) switches between two Excel windows. Extend the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, to the next nonblank cell. You can also get access to more advanced fill handle options by dragging the fill handle using the right mouse button instead of the left. When you drag down and release, a menu with more advanced options will appear. There are a couple different ways to access the Value Field Settings for a field in your pivot table. Hold the Shift key while you drag a selected row, column, or selected cells to move the selected cells and drop to insert them in a new location.

You can make the data in your spreadsheets larger by zooming in, or you can see more of the spreadsheet by zooming out. Many of the shortcuts that use the Ctrl key on a Windows keyboard also work with the Control key in Excel for Mac. However, not all do. If you're familiar with keyboard shortcuts on your Windows computer, the same key combinations work with Excel for Android using an external keyboard, too. F6 alone: switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split, F6 includes the split panes when switching between panes and the ribbon area. Apply the number format with two decimal places, thousands separator, and minus sign (-) for negative values.If a function key doesn't work as you expect it to, press the Fn key in addition to the function key. If you don't want to press the Fn key each time, you can change your Apple system preferences. For instructions, go to Change function key preferences with the mouse. Ctrl+End moves to the last cell on a worksheet, to the lowest used row of the rightmost used column. If the cursor is in the formula bar, Ctrl+End moves the cursor to the end of the text.

Pivot tables have an option to show a small icon next to a field item to expand or collapse the field. This can be turned on from the Analyze tab with the +/- Buttons command. The problem with inserting shapes is you have to insert them one at a time. This means going to the Insert tab ➜ selecting Shapes from the ribbon ➜ selecting your desired shape ➜ then drawing in the sheet. You have to do this each and every time, even if you’re wanting to draw the same shape multiple times. These shortcuts might move in another direction other than down or up. If you'd like to change the direction of these shortcuts using the mouse, select Excel> Preferences > Edit, and then, in After pressing Return, move selection, select the direction you want to move to.

Stay In The Loop

Edit the active cell and then clear it or delete the preceding character in the active cell as you edit the cell contents. In the power query editor, there’s a handy data type icon to the left hand side of each column heading. You can use this to easily change the data type of any column. The ribbon groups related options on tabs. For example, on the Home tab, the Number group includes the Number Format option. Press the Alt key to display the ribbon shortcuts, called Key Tips, as letters in small images next to the tabs and options as shown in the image below. Open the Insert tab and insert PivotTables, charts, add-ins, Sparklines, pictures, shapes, headers, or text boxes. With two or more fields in the Rows (or Columns) area of a pivot table it’s possible to expand or collapse fields to show more or less detail.

Ctrl+Shift+End extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, Ctrl+Shift+End selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.

Mouse Shortcuts For The Ribbon

To go directly to a tab on the ribbon, press one of the following access keys. Additional tabs might appear depending on your selection in the worksheet.

Cycle through all the various combinations of absolute and relative references when a cell reference or range is selected in a formula. If you’re looking for more screen real estate to view your awesome spreadsheet, then temporarily removing the ribbon can help. In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button). Open the View tab to choose a view, freeze rows or columns in your worksheet, or show gridlines and headers.End turns End mode on or off. In End mode, you can press an arrow key to move to the next nonblank cell in the same column or row as the active cell. End mode turns off automatically after pressing the arrow key. Make sure to press End again before pressing the next arrow key. End mode is shown in the status bar when it is on. Pivot tables are the great for summarizing data, but what if you then want to see the detail behind those summarized values? If you’ve got a ton of Excel workbooks open and you want to close them all, then it can be a pain go through and close each workbook individually. Grouping sheets together can be a really powerful tool. It allows you to do the same edits on all the grouped sheets at the same time, which can save massive amounts of time if you need to edit the exact same thing on dozens of sheets. If you're familiar with keyboard shortcuts on your macOS computer, the same key combinations work with Excel for iOS using an external keyboard, too.