A clear list of over 200 Excel shortcuts for both Windows and Mac, side-by-side. Windows shortcuts in dark gray on the left, Mac shortcuts in white on the right. CtrlShift: ⌃⇧: Fill down from cell above. Fill right from cell left.
Excel is a powerful tool, but like many powerful tools, its user interface has become cluttered with layers of menus, ribbons and multi-tabbed dialog boxes. Getting to the feature you want can feel like navigating one of those new urban developments designed to prevent traffic cutting through – tiresome unless you know the hidden shortcut. Or, in Excel's case, the keyboard shortcut. Here are 10 of our favorites to help you navigate and edit your spreadsheet at speed, without lifting your fingers from the keyboard. The default display mode in Excel shows you the results of formulas, so a column of sales figures ends in your total sales for the month, not a truncated formula beginning =SUM(D2. If you want to check your work, you can always click on a cell to see the formula behind the cell's value; it will be displayed in the formula bar, between the ribbon and the worksheet. If you have a lot of cells to check, that's going to take you a while.
But there is a way to see all the formulas in a worksheet at once: You can toggle the display between formulas and values by hitting Ctrl+` – that's a backtick, the symbol found to the left of the 1 key on U.S. This trick is particularly useful if you're auditing a shared spreadsheet, for example, to see why some calculated cells in one column are not updating when you change the inputs in another. It may be that someone has inadvertently replaced one of the formulas with a value they calculated by hand. You can also find this on the Formulas tab of the Ribbon. Sometimes you want to draw a box around a result to call attention to it – but Excel offers so many different border styles, it's easy to become sidetracked. Double lines? Ooh, dotted lines!
Or maybe dashes? What about the color? A standard one? Or a themed set?
And that's without considering whether you want the same border on all sides of your box, or something different across the top or along the bottom. Before you realize it, you can lose half your morning on irrelevant esthetics. Surely there's a simpler, distraction-free way of applying a border. To draw a quick, simple border around the current cell (or cells) just hit Ctrl+Shift+7. You won't notice the change until you select another cell.
You distractedly type in a whole series of labels, then realize that you meant them to go across a row, and not down a column. Rather than cut and paste them one at a time to their correct position, why not flip the column of data through 90 degrees using the Paste Special/Transpose option? Start by selecting the column of labels or other data you want to transpose.
You can click and drag or, if the column is very long, try this: Select the first cell in the column, hold down the shift key, and double-click on the lower border of the selected cell: Excel will extend the selection downward until it encounters an empty cell. Copy the selection, then select the leftmost of the cells where you would like the transposed labels to appear. Now, for the magic: Call up the Paste Special dialog with Ctrl+Alt+V (or command+control+V in macOS); hit E to select the Transpose option, bottom right, then Enter.
As long as the source and destination areas don't overlap, you should see your cell entries spread across the sheet rather than down it. Excel will only let you perform this trick using Copy, not Cut, so to delete the data from its original position, click once again in the first cell of the column, hold Shift and double-click the lower border of the cell to extend the selection, then hit Ctrl+Delete (just delete in macOS) to empty the cells.
Note: This trick also works the other way, for transposing a horizontal block of cells into a vertical one. This is a quick one. Did you know you can extend the selection from one cell to an entire row by hitting Shift+spacebar? Or from one cell to an entire column by hitting Ctrl+spacebar? (This one is control+spacebar in macOS too, as Command+spacebar is the system-wide shortcut for Spotlight search.) If you have cells in several adjacent rows selected, Shift+spacebar selects the entirety of each of those rows, while Ctrl+spacebar will select all of the columns in which you have adjacent cells selected. Curious minds will want to know: What happens if you do both simultaneously? Hitting Ctrl+Shift+spacebar will extend the selection to form a rectangle encompassing all contiguous non-empty cells adjacent to the currently selection.
If any of the cells in the current selection are empty, then Ctrl+Shift+spacebar will select the entire sheet. This is a little like clicking on the triangle at the top-left of the sheet, with the difference being that it leaves the current cell selection unchanged, while clicking the triangle makes A1 the current cell.
If there's one thing I learned at college, it's that there's no shame in looking it up. You don't have to know all the answers, but it helps to learn where to find them. So it is with building formulas in Excel: There are so many functions, with so many parameters, that it's nigh-on impossible to remember them all. Happily, you don't have to.
Excel already provides a modicum of help, listing the parameters to a function when you type the opening parenthesis following its name. But that's not always enough of a clue. At that point, hitting Ctrl+A in Excel for Windows brings up a dialog box, Function Arguments, that will walk you through building the formula, providing a definition for each parameter in turn. On Excel for macOS, the same control+A shortcut will bring up the more expansive Formula Builder sidebar, where you can build the formula and see all the definitions at once. Let's take the example of one of Excel's powerful yet perplexing financial tools, AMORLINC, which when given five or six parameters returns the prorated linear depreciation of an asset for each accounting period. As you begin your formula by typing =AMORLINC( Excel responds by filling in placeholder parameter names after the opening parenthesis: cost; datepurchased; firstperiod; salvage; period; rate; basis - the square brackets indicating an optional parameter.
But what do they mean? Hit Ctrl-A and you'll learn that firstperiod needs to be a date, not a number, and that salvage is the salvage value at the end of life of the asset.
OK, this isn't a single keyboard shortcut, it's a whole family of them, but they all work on the same principle – and they're all in the row across the top of your keyboard, which makes them really handy if you want to change the presentation of your spreadsheet without picking up your mouse. Say hello to the number formatting shortcuts: Ctrl+Shift+ (control+shift+ on macOS): general format (displays all digits of a number if there is room in the cell, switching to scientific format if not) Ctrl+Shift+! Windows users don't know how lucky they are, being able to navigate all the elements of a dialog box, including buttons and checkboxes, without a mouse by tabbing from one to another. You can't do that on macOS, where tabbing just moves you from one text input field to another. Or rather, you can't do that on macOS by default. There is a way to turn on that Windows-like behavior – and there's even a keyboard shortcut for it.
It's not part of Excel, though: It's part of macOS. The long way to do it is to go to the Apple menu and select System Preferences. Keyboard, click on the Shortcuts tab and then, at the bottom of the dialog box under 'Full Keyboard Access' click on the button next to 'All controls.' The short way is just to hit control+Fn+F7 (or perhaps just control+F7, depending on the way you have function keys set up on your Mac). Now you can navigate dialog boxes without having to pick up your mouse or touch the trackpad. And if that becomes too confusing, just hit control+Fn+F7 again put things back the way they were.
To sum up this tour of useful keyboard shortcuts, let's conclude with one for what is probably the most commonly used Excel function: summing up a column. Select a range of cells in a column then hit Alt+= (macOS: command+shift+T), and Excel will insert a formula beneath them summing the range. For example, if you select cells B2 through B20, it will insert =SUM(B2:B20) into cell B21. If you select a range of cells in a single row - B2 through F2, say, then the shortcut promptes Excel to sum horizontally, inserting =SUM(B2:F2) into cell G2. Excel looks down on rectangular selections that extend across multiple rows and columns.
Selecting the cells B2 through B20 and C2 through C20 and applying the shortcut will insert =SUM(B2:B20) in cell B21, and =SUM(C2:C20) in cell C21, but no horizontal sums will be calculated.
I need to assign a keyboard shortcut to add a fill color to cells in a numbers table. I have tried Applescript but without success. Applescript seems to ignore the 'fill' 'color' operation. Does anyone have an Applescript for this?
I just need to hit, say, command-option-G and have the highlighted cells filled with green. It's pretty simple. I do it a hundred times on a file when I'm at work so every fraction of a second I can save is a huge help. I can do this in Excel with a macro, but I can't seem to figure out how to do it with Number. Thanks for any help you can provide!
To get the RGB values of a background color you have already assigned to a cell, you can select the cell and run this script (copy-paste it into AppleScript Editor and click the green 'run' button).get background color of cell - like a 'dropper' -if none set, returns 'missing value' tell application 'Numbers' to tell front document to tell active sheet to tell (first table whose class of selection range is range) tell the first cell of the selection range set bkgrRGB to its background color end tell end tell You can then copy-paste the RGB value from the results pane in AppleScript Editor. Apple Footer. This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site.
All postings and use of the content on this site are subject to the.