Step 1: place the cursor below the column of numbers you want to sum (or to the left of the row of numbers you want to sum). Step 2: hold down the Alt key and then press the equals sign while still holding Alt. Example of the Autosum Excel function. Here are two screenshots of the Autosum Excel shortcut in action.Great formula, thanks a lot. Since you saved ne a lot of processing power with sharing this formula, I will now take the time to explain how it works to your subscribers whom queries it.
Shortcut For Summing In Excel Mac And PCYou can test it by selecting 6 columns (one row) enter the formula and then press (CTRL+SHIFT+ENTER)Basically returns the column number, being 2,3,4,5,6,7 (starting at 2 because the first column is B). You can validate it by selecting 6 columns (one row) and pressing CTRL+SHIFT+ENTERCombining the above two basically indicate a counter for the column you are working in, being 0,-1,-2,-3,-4,-5>(ROW($B$3:$G$8)-MIN(ROW(B3:G8))=MIN(COLUMN(B3:G8))-COLUMN($B$3:$G$8))The above function now compares the two matrixes with each other, the "row" part looking like this:The only two cells that have a matching value is the top left cell, as per the below boolean table:This means that it will only sumproduct the parts of the matrix that is TRUE, being the top left cell.If you look carefully at the formula, you will notice that the min parts of the columns and rows is not fixed, as such, as you move the formula down one row, the matrices looks like this:Since we only copied it one row down and did not shift the columns the result looks the sameAs mentioned, great formula, well written. Excel shortcut keys can greatly improve your speed when conducting analysis. But often, it can be hard to remember these hotkeys especially if you regularly switch between the Mac and PC versions of Excel.![]() If you only want to copy the values, use the following keyboard shortcut. ALT =When you copy and paste a cell or a range of cells, it also copies the value, the formula in it, and the formatting. #1 Auto SumIf you have numbers in a column/row, you can quickly get the sum by using this Excel keyboard shortcut. Best mac makeup for oily skin#7 Inserting a CommentIf your work involves inserting a lot of comments in cells in Excel, you’ll find this shortcut mighty useful.To quickly insert a comment in a cell, use the keyboard shortcut: SHIFT F2This would insert the comment and place the cursor within the comment.When you have entered the comment, press the Escape key to come out of the comment mode. If you want to apply the Outline border only use ALT + H + B + S. #5 Delete Cells/Row/ColumnTo delete selected cells, rows, or columns, use the keyboard shortcut: CONTROL -This opens the Delete dialog box, where you can select from the following options:To quickly apply borders to the selected cells, use the keyboard shortcut: ALT + H + B + ANote that this applies the “All Border” format, which means that all the cells get the border format. CONTROL DAlso read: How to use Fill Handle in Excel #4 Select Entire Row/ColumnYou use the following shortcuts to select an entire row or column that contains the active cell.To select the entire column: CONTROL SPACEBARFor example, if cell B2 is selected, then Shift + Spacebar would select the second row and Control + Spacebar would select the second column. Note that this would copy the contents, formulas, and formatting as well. How to Multiply in Excel using Paste Special.This one would help you while entering data in Excel.Use it to copy the cell right above the active cell. Now if you copy the entire list and paste it somewhere else, it will copy all the cells (including the ones that are hidden, as shown below):Note that a few rows in the original data set are hidden, but when you copy-paste it, even the hidden rows get copied.If you don’t want this to happen, you first need to select only those cells that are visible. #10 Apply FilterWhile you can easily apply/remove the filter with the filter Filter option in the ribbon (in the data tab), it still takes time and a couple of clicks.Here is a keyboard shortcut that would instantly apply data filter (or remove filters if it’s already applied): CONTROL SHIFT LLet me first tell you why this is important.Suppose you have a list in Excel and you hide a few rows. #8 Insert Current Date and TimeThis keyboard shortcut can be used to quickly insert timestamps in cells.To insert the current date, use the keyboard shortcut: CONTROL To insert the current time, use the keyboard shortcut: CONTROL SHIFT If you have a lot of worksheets in the workbook, it can drive you crazy to navigate through it.Here is the keyboard shortcut that solves this problem.To go to the previous worksheet: CONTROL PAGEUPTo go to the next worksheet: CONTROL PAGEDOWNNote that if you continue to hold the keys, it will keep on moving on to the previous/next worksheet. #14 Start New Line in the Same CellSometimes, you may want to show the text/data in two or more than two rows in the same cell in Excel. It opens a dialog box where you have different tabs for different types of formatting. If you want to change the formatting of a cell or range of cells, select the cells and use the keyboard shortcut: CONTROL 1It’s a gateway to all the formatting you can do this Excel. For example, if you want to freeze the top two rows and one column to the left, select cell B3.Now use the shortcut: ALT + W + F + F #13 Opening Format Cells Dialogue BoxThis one is my favorite. This often wastes a lot of time as you have to scroll back to see what a data point means.Freezing rows and columns is the way to go in such cases.Here are the shortcuts for freezing rows/columns:Note that the left-most column gets fixed.If you want to freeze both rows and columns, select a cell above which you want to freeze rows (you can freeze more than one row), and to the left of which you want to freeze columns (could be one or more than one). The keyboard shortcut for this is: CONTROL KIf you want to insert bullets in Excel (in case you are creating a list), you can do that quickly by using a keyboard shortcut.If you have a numeric keypad in your keyboard: CONTROL TAB #16 Insert HyperlinkTo insert a hyperlink in a cell, you need to open the Insert Hyperlink dialog box. For example, if you have multiple applications open at the same time, such as a browser, PowerPoint, word, excel, skype, VLC, and so on, ALT + TAB would cycle through all these applications.Instead, activate a workbook and then use the following shortcut to switch between open workbooks only. A popular shortcut to do this is ALT + TABBut this would cycle through all the open applications. Ps3 emulator mac os xWhile it automatically picks the range that needs to be converted, and in most cases it is correct, it’s a good practice to cross-check this.Click on OK (or press Enter). As soon as you leave the ALT key, a bullet would appear.If you do not have a numeric Keyboard (like my laptop), activate the NumLock first and then repeat the above steps (or try with ALT + FUNCTION + 7/9) #18 Converting Tabular Data into Excel TableExcel Table is a must-use feature if you work with tabular data.To quickly convert tabular data into an Excel Table, select the data (or select any cell in the data set) and use the keyboard shortcut: CONTROL TThis will open the Create Table dialog box. Hold the ALT key, Press 7 or 9, leave the ALT key. Either double click on the cell or press F2 – to get into edit mode. I am sure you have some of your own too.Share it with me in the comments section. F7 #20 Open the VB Editor WindowWorking with VBA requires one to switch between the worksheet and the VB Editor.To make a switch from Excel workbook to VB Editor, use the keyboard shortcut: ALT F11These are my top 20 Excel keyboard shortcuts. However, if you run spell check, it will catch those errors.To run spell check, simply hit the F7 key. ![]()
0 Comments
Leave a Reply. |
AuthorDonna ArchivesCategories |