People often ask about the best principles to follow to make the better excel spreadsheets. Hope you might also be interested to know them. Here we go…
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
[PDF Download] Advanced Microsoft Excel 2013 Tutorial
Microsoft Excel is program designed to efficiently manage spreadsheets and analyze
data. It contains both basic and advanced features that anyone can learn. Once some
basic features are known, learning the advanced tools becomes easy. This lesson is
composed of some advanced Excel features.
. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization.
Objectives
1) Learn how to Customize the Interface
2) Advanced Formatting: Custom Lists, Cell Groups, and Transposing Tables
3) Learn how to Reference Across Sheets
4) Advanced Formulas and Using Data Ranges
5) Using Data Validation
data. It contains both basic and advanced features that anyone can learn. Once some
basic features are known, learning the advanced tools becomes easy. This lesson is
composed of some advanced Excel features.
. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization.
Objectives
1) Learn how to Customize the Interface
2) Advanced Formatting: Custom Lists, Cell Groups, and Transposing Tables
3) Learn how to Reference Across Sheets
4) Advanced Formulas and Using Data Ranges
5) Using Data Validation
Alternative Link : Download From Here
No-one is going you to tell about these "5 Powerful Excel Features"
Microsoft Excel is the most useful and easy tool for business analysts. It has large number of useful formulas, features and bundles of interactive charts. But, most of us are not known of all of them and there are some more features which are powerful and easy to use to make our work simpler. You might not have noticed some of the useful Excel 2010 & 2013 features like Sparklines, Slicers, Conditional Formatting and other formulas which add value to your work. In this article, I will take you through them and will give you an idea on what are those and how to use them.
Most Useful Excel Features
Among many Excel features, there are some hidden features which are easy to use and you many not know all of them. Without any further delay, we will look at 5 such Excel features.
Sparklines
Sparklines were first introduced in Excel 2010 and are used to represent visualizations for the trend across the data in a row. It fits in a single Excel cell and saves the space on the worksheet. This is a cool feature and is very easy to use. Calculating the trend for row data and placing the visualization in the single excel is really a great feature to use.
In order to create your own Sparklines, select the range of data. Click insert on the ribbon and select the type of Sparklines (Line, Column or Win/Loss). Next, enter the range of the target where you want to show the Sparklines.For more information on how to create neat charts & graphs in excel click here
Conditional Formatting
Conditional Formatting is a well known feature of Excel. It is used to visually present the data based on the conditions met. It is also useful to create heat maps. This would be helpful to find the interesting patterns by exploring the data effectively.
To create the heat map, select the data and head over to the ribbon. Under Home, clickConditional Formatting and then click Color Scales. Now, pick the color scale. You can even set the color scale by editing the formatting rule.
SMALL and LARGE Functions
We all know about MAX and MIN functions. They give you the maximum and minimum values of the selected data respectively. But, in order to find the 1st, 2nd, 3rd or nth largest or smallest value of the selected range if data, we can make use of LARGE and SMALL functions respectively.
In this example, in order to find the top two products for each month, we made use of MATCH and INDEX functions along with LARGE and SMALL functions. For more information on basic to advanced Excel formulas, visit this tutorial
Remove Duplicates
Do not blame me for mentioning this feature in this list. It is very important to get rid of redundant data from the available huge amount of data. It is one of the best ways for cleaning and organizing the data and so thought of having it in this list of powerful Excel features. Removing Duplicates feature was introduced from Excel 2007 and is helpful to remove duplicates which is the most important problem which we face.
To remove duplicates, select the data and head over to the ribbon. Under Data, click theRemove Duplicates button and what you see the data without duplicates. For more information on how to Find and Remove Duplicates, visit this tutorial.
Slicers
Slicers act as visual filters. It helps you to visualize the subset of data as a connected chart or as a raw data. For example, if you want to show the trend of sales of various products, then you can create the interactive sales trend chart using Slicers. Based on the product you select, respective chart is shown. Slicers were first introduced in Excel 2010 and enhanced a lot in Excel 2013.
In Excel 2013, if you want to add Slicer to your charts, select the data range and click on insert > Slicer. Now, select the part of the data you want to use as a filter. In the image above, Product column is used as a filter.
How many of you have used these powerful and useful Excel features? If you want to add more features to the list, please let us know through comments.
Extract text from a cell in Excel
Sometimes it is useful (or necessary) to extract part of a cell into another cell in Excel. For example, you may have a cell that contains a combination of text and numbers, or a cell that contains two numbers separated by a delimiter such as a comma.
5 Excel Tips and Tricks to Boost your Productivity
Taking the time to learn some Excel tips and tricks will likely help you boost your productivity and streamline your spreadsheets.
We previously shared some Excel insights. Here are five more tips to consider:
1. Use Number Formatting Shortcuts
For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:
- Format numbers to include two decimal places: Ctrl+Shift+1
- Format as time: Ctrl+Shift+2
- Format as date: Ctrl+Shift+3
- Format as currency: Ctrl+Shift+4
- Format as percentage: Ctrl+Shift+5
- Format in scientific/exponential form: Ctrl+Shift+6
2. Use Sparklines to Display Data
Sparklines are a built-in feature of Excel 2010, Excel 2011 for Mac and Excel 2013 that allow you to display small charts inside cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.
3. Manipulate Data with Pivot Tables
When you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner.
4. Move Between Formulas and Results
To efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.
5. Hide Zero Values
Hiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)
These are just a few of the helpful Excel features that can decrease the time you spend on spreadsheets and increase the amount of work you get done. What Excel tips and tricks do you use to boost productivity? Let us know in the comments section.
Top 10+ Free Excel Games To Download
Here you can download some of best games that can be played in Excel. Just download it and start playing in your excel :)
Adrenaline Challenge:This is a motor cycle game with predefined objectives. Complete the objectives as fast as possible and move to the next stage.
Angry Birds: I can bet that most of us would have played angry birds before, but this is a handy flash version of the game. This is a must try for all the angry birds lovers.
Bang Bang -2: Bang Bang-2 is a first person shooting game. Your objective is to kill as many people in the stipulated time.
Batman: Play the heroic Batman game to save the Gotham city from bad people. Defeat the bad people and move to the next levels.
BMX-Tricks: Ride your cycle and perform different stunts on the track to earn extra points in stipulated time. You can ride over grills, on tables etc. to earn extra points.
Bowling: This is a cool game with 3D graphics. Aim the bowl using the mouse to hit the pins. A must try game for bowling lovers.
Chopper Challenge: Fly the chopper as far as possible and avoid the obstacles in the path. Make a high score and challenge your friends.
Defend Your Castle: Defend Your Castle was one of the most rated games of 2012. Here your goal is to protect your castle from the opponent team.
Drunk Driving: In this game your task is to handle an uncontrollable car. Avoid the obstacles like other cars and buses to clear stages.
Famous Eyes: This game consists of images of eyes of some of the famous personalities, identify them and earn points.
Frog leap: This is a little puzzle game. The game challenges you to interchange the positions of 6 frogs. Clear the level and challenge your friends.
2D Knockout: 2D knockout is a boxing game. You have to play against the boxers of various countries. The objective of this game is to defeat your opponent.
Download it here.
3D Tic Tac Toe:3D Tic Tac Toe is a must try game for all the tic tac toe lovers. The objective of the game is to get 4 X’s or 0’s in a row, whosoever gets it first wins the game.
You can download this game here.
Adrenaline Challenge:This is a motor cycle game with predefined objectives. Complete the objectives as fast as possible and move to the next stage.
Get it here.
Air Fighting: This is a traditional Air Plane fighting game. The objective of this game is to destroy all the opponent air planes.
Click this link to download.
Angry Birds: I can bet that most of us would have played angry birds before, but this is a handy flash version of the game. This is a must try for all the angry birds lovers.
You can download this game here.
Angry birds Rio: This is another angry bird’s game with bigger and better graphics. I am quite sure that you will fell in love with this one.
Download it here.
Apple Shooter: Apple shooter is a very nice game designed by Wolf games. Here your objective is to shoot the apple without killing the man beneath it.
Get it here.
Ball Challenge: This game has a set of blue and red balls bouncing inside a box. You need to keep the blue balls inside one compartment and the red ones inside another compartment.
Click this link to download.
Bang Bang -2: Bang Bang-2 is a first person shooting game. Your objective is to kill as many people in the stipulated time.
Excel game here.
Baseball Pinch Hitter -2: This game has awesome graphics and great gameplay. The game has certain levels and you have to meet some targets to clear those level.
You can download this game here.
Batman: Play the heroic Batman game to save the Gotham city from bad people. Defeat the bad people and move to the next levels.
Download it here.
Bloxorz: The aim of this game is to roll the block such that it falls in the square hole inside the board. The game has 33 stages and different challenges that you will love to tackle.
Get it here.
BMX-Tricks: Ride your cycle and perform different stunts on the track to earn extra points in stipulated time. You can ride over grills, on tables etc. to earn extra points.
Click this link to download.
BowMan: This is a third person shooting game where you have to kill your opponent using arrows. You can shoot the arrow at different angles and power to hit the opponent.
You can download this game here.
Bowling: This is a cool game with 3D graphics. Aim the bowl using the mouse to hit the pins. A must try game for bowling lovers.
Download it here.
Chaser: This is a nice time pass game. The game is very similar to the traditional snake’s game with the only difference that you and computer are playing on the same board.
Get it here.
Chopper Challenge: Fly the chopper as far as possible and avoid the obstacles in the path. Make a high score and challenge your friends.
Click this link to download.
Crab Ball: This a little funny volleyball like game between two crabs. Simply download the game and enjoy playing it.
Excel game here.
Defend Your Castle: Defend Your Castle was one of the most rated games of 2012. Here your goal is to protect your castle from the opponent team.
Download the game here.
Down Hill Stunts: This is a motor bike game, your aim is to ride your bike on the track and collect the stars on the way and that too without crashing.
Download it here.
Drunk Driving: In this game your task is to handle an uncontrollable car. Avoid the obstacles like other cars and buses to clear stages.
You can download this game here.
Easy Chess: This a flash version of the chess game. A must try game for a die-hard chess lover.
Download it here.
Famous Eyes: This game consists of images of eyes of some of the famous personalities, identify them and earn points.
Get it here.
Fishy: Fishy is small time pass game, where you have to feed your fish on the other small fishes and also save your fish from the other fishes that are bigger than it.
Click this link to download.
Frog leap: This is a little puzzle game. The game challenges you to interchange the positions of 6 frogs. Clear the level and challenge your friends.
Excel game here.
Golf: The main objective of this game is to put the ball inside the hole in minimum number of strokes. The game also offers a multiplayer mode.
Download the game here.
Top 10 Quick Time-Saving Excel Shortcuts for All
Ok guys, so today I am listing top 10 quick time saving excel shortcuts that will improve your excel skills and save you lots of time.
Excel Tip No. 1: Automatically SUM() with ALT + =
Click ALT+= in first empty cell of column and get the result.
Excel Tip No. 2: Logic for Number Formatting Keyboard Shortcuts
At times keyboard shortcuts seem random, but there is logic behind them. Let's break an example down. To format a number as a currency the shortcut is CRTL + SHIFT + 4.
Both the SHIFT and 4 keys seem random, but they're intentionally used because SHIFT + 4 is the dollar sign ($). Therefore if we want to format as a currency, it's simply: CTRL + ‘$' (where the dollar sign is SHIFT + 4). The same is true for formatting a number as a percent.
Excel Tip No. 3: Display Formulas with CTRL + `
When you're troubleshooting misbehaving numbers first look at the formulas. Display the formula used in a cell by hitting just two keys: Ctrl + ` (known as the acute accent key) – this key is furthest to the left on the row with the number keys. When shifted it is the tilde (~).
Excel Tip No. 4: Jump to the Start or End of a Column Keyboard Shortcut
You are thousands of rows deep into your data set and need to get to the first or last cell. Scrolling is OK but the quickest way is to use the keyboard shortcut CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell.
When you combine this shortcut with the SHIFT key, you'll select a continuous block of cells from your original starting point.
Excel Tip No. 5: Repeat a Formula to Multiple Cells
Never type out the same formula over and over in new cells again. This trick populates all of the cells in a column with the same formula, but adjusts to use the data specific to each row.
Create the formula you need in the first cell. Then move your cursor to the lower right corner of that cell and, when it turns into a plus sign, double click to copy that formula into the rest of the cells in that column. Each cell in the column will show the results of the formula using the data in that row.
Excel Tip No. 6: Add or Delete Columns Keyboard Shortcut
Managing columns and rows in your spreadsheet is an all-day task. Whether adding or deleting, you can save a little time when you use this keyboard shortcut. CTRL + ‘-‘ (minus key) will delete the column your cursor is in and CTRL + SHIFT + ‘=' (equal key) will add a new column. From an earlier tip, think about CTRL + ‘+' (plus sign).
Excel Tip No. 7: Adjust Width of One or Multiple Columns
It's easy to adjust a column to the width of its content and get rid of those useless ##### entries. Click on the column's header, move your cursor to the right side of the header and double click when it turns into a plus sign.
Excel Tip No. 8: Copy a Pattern of Numbers or Even Dates
Another amazing feature built into Excel is its ability to recognize a pattern in your data, and allow you to automatically copy it to other cells. Simply enter information in two rows which establish the pattern, highlight those rows and drag down for as many cells as you want to populate. This works with numbers, days of the week or months!
Excel Tip No. 9: Tab Between Worksheets
Jumping from worksheet to worksheet doesn't mean you have to move your hand off the keyboard with this cool shortcut. To change to the next worksheet to the right enter CTRL + PGDN. And conversely change to the worksheet to the left by entering CTRL + PGUP.
Excel Tip No. 10: Double Click Format Painter
Format Painter is a great tool which lets you duplicate a format in other cells with no more effort than a mouse click. Many Excel users (Outlook, Word and PowerPoint too) use this handy feature, but did you know you can double-click Format Painter to copy the format into multiple cells? It's quite a time-saver.
Excel formulas cheat sheet: 15 tips for calculations and common tasks
Many of us fell in love with Excel as we delved into its deep and sophisticated formula features. Because there are multiple ways to get results, you can decide which method works best for you. For example, there are several ways to enter formulas and calculate numbers in Excel.
Five ways to enter formulas
1. Manually enter Excel formulas:
Long Lists: =SUM(B4:B13)
Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.
2. Click the Insert Function button
Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:
=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).
=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).
3. Select a function from a group (Formulas tab)
Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.
=TODAY() Inserts today’s date.
4. The Recently Used button
Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.
=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
5. Auto functions under the AutoSum button
Auto functions are my editor's personal favorite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:
=MAX(B4:B13) returns the highest value in the list.
=MIN(B4:B13) returns the lowest value in the list.
Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.
Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.
Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.
Five handy formulas for common tasks
The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.
Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of cells (e.g., beginning date, ending date, etc.)
1. =DAYS
This is a handy formula to calculate the number of days between two dates (so there’s no worries about how many days are in each month of the range).
Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days
Formula: =DAYS(A30,A29)
2. =NETWORKDAYS
This similar formula calculates the number of workdays (i.e., a five-day workweek) within a specified timeframe. It also includes an option to subtract the holidays from the total, but this must be entered as a range of dates.
Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days
Formula: =NETWORKDAYS(A33,A34)
3. =TRIM
TRIM is a lifesaver if you’re always importing or pasting text into Excel (such as from a database, website, word processing software, or other text-based program). So often, the imported text is filled with extra spaces scattered throughout the list. TRIM removes the extra spaces in seconds. In this case, just enter the formula once, then copy it down to the end of the list.
Example: =TRIM plus the cell address inside parenthesis.
Formula: =TRIM(A39)
4. =CONCATENATE
This is another keeper if you import a lot of data into Excel. This formula joins (or merges) the contents of two or more fields/cells into one. For example: In databases; dates, times, phone numbers, and other multiple data records are often entered in separate fields, which is a real inconvenience. To add spaces between words or punctuation between fields, just surround this data with quotation marks.
Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where month, day, and year are cell addresses and the info inside the quotation marks is actually a space and a comma.
Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)
Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)
5. =DATEVALUE
DATEVALUE converts the above formula into an Excel date, which is necessary if you plan to use this date for calculations. This one is easy: Select DATEVALUE from the formula list. Click the Date_Text field in the dialog box, click the corresponding cell on the spreadsheet, then click OK, and copy down. The results are Excel serial numbers, so you must choose Format>Format Cells>Number>Date, and then select a format from the list.
Formula: =DATEVALUE(H33)
Three more formula tips
As you work with formulas more, keep these bonus tips in mind to avoid confusion:
Tip 1: You don’t need another formula to convert formulas to text or numbers. Just copy the range of formulas and then paste as Special>Values. Why bother to convert the formulas to values? Because you can’t move or manipulate the data until it’s converted. Those cells may look like phone numbers, but they’re actually formulas, which cannot be edited as numbers or text.
Tip 2: If you use Copy and Paste>Special>Values for dates, the result will be text and cannot be converted to a real date. Dates require the DATEVALUE formula to function as actual dates.
Tip 3: Formulas are always displayed in uppercase; however, if you type them in lowercase, Excel converts them to uppercase. Also notice there are no spaces in formulas. If your formula fails, check for spaces and remove them.
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to find out how to fix a formula or function in Excel that is broken or not returning the result that you expect.
Thankfully, there is a really great tool that will help you troubleshoot the formula and pin-point the problem. It is called Evaluate Formula and it will save you hours!
Below, I will give you a sample problem and show you, step-by-step, how to find and fix the error.
Here, we have what should be a Vlookup formula that is not returning what we expect. Let's take a look at the formula now:
This is one formula that contains three functions, the IF function, the IFERROR function, and the VLOOKUP function. Looking at this, though it may seem a bit complex, everything appears to be input exactly how it should be. All functions look OK and the IF statement check at the front, that requires cell A6 to equal yes, looks good to me, but the formula is not working.
Let's use the Evaluate Formula feature so that we dont waste any time troubleshooting this formula.
Select the cell that contains the formula or function that is not working and then go to the Formulas Tab and click the Evaluate Formula button, which is located in theFormula Auditing section of the menu.
The entire formula contents of the cell appear in the window. The Evaluate Formula feature will walk you through the execution of the entire formula and show you exactly what it sees.
Notice how A6 contains an underline beneath it? This is what is evaluated first within this formula. Click the Evaluate button at the bottom of the window to see how Excel evaluates this, in this case, what it will return for the cell reference A6.
The value of cell A6 has been returned into this formula and we can see how Excel is now comparing that value to the value yes to see if they are equal. Hit Evaluate again.
FALSE. We can see that the IF statement in the cell is preventing anything else from happening with this function because the condition in the IF statement has evaluated to FALSE. Hit the Evaluate button once more and you will see the final result, which is a blank.
Now, hit the Restart button that appeared where the Evaluate button used to be and you can start over in order to more closely analyze everything.
This, time, if you would like, you can hit the Step In button, which allows you, when possible, to enter a cell that is being referenced by the current formula. This feature is great for really complex formulas that are dependent upon many cells in Excel.
If you do that in this case, everything still looks OK for cell A6. So, hit Step Out and let's get back to the main formula. Now, the next step has already been evaluated for us so we can see what cell A6 returns to the formula. Now, take a close look!
CRAP!!!! There is a dirty stinking space after the yes in cell A6!!!!
Let's correct that by closing the Evaluate Formula window and going to cell A6 to remove the space from the end of "yes" and try our vlookup formula again to see what happens.
It finally works!
Now, we could have probably figured that out on our own after a while, but the Evaluate Formula feature showed very clearly, albeit, you must have been paying attention, that there was an extra space after the value coming from cell A6.
Believe it or not, extra spaces, which are by nature very difficult to find, cause many many many MANY hours of troubleshooting for people.
In another tutorial, I will show you a simple way to avoid some of the headaches associated with spaces.
I hope you enjoyed the tutorial! :)
Merging Two Excel Files using VLOOKUP Function
Merging of two Excel files using VLOOKUP function comes very handy if we're manually matching two Excel files, it is very time consuming if the size of data is large. Here this merging function will help us.
Here below is an Tutorial video From Danny Rocks that will help you in accomplish this task!
Here below is an Tutorial video From Danny Rocks that will help you in accomplish this task!
The 10 most useful Excel keyboard shortcuts
The problem with a list of hundreds of shortcut keys is that it is overwhelming. You cannot possibly absorb 233 new shortcut keys and start using them. The following sections cover some of my favorite shortcut keys. Try to incorporate one new shortcut key every week into your Excel routine.
1) Quickly move between worksheets
Ctrl+Page Down jumps to the next worksheet. Ctrl+Page Up jumps to the previous worksheet. Say that your workbook has 12 worksheets named Jan, Feb, Mar, . . . Dec. If you are currently on the Jan worksheet, hold down Ctrl and press Page Down five times to move to Jun.
2) Jump to the bottom of data with Ctrl+Arrow
Provided there are no blank cells in your data, press Ctrl+Down Arrow to move to the last row in the data set. Use Ctrl+Up Arrow to move to the first row in the data set.
Add the Shift key to select from the current cell to the bottom. If you have data in A2:J987654 and your cursor is in A2, you can hold down Ctrl+Shift while pressing the down arrow and then the right arrow to select all the data rows but exclude the headings in row 1.
3) Select the current region with Ctrl+*
Press Ctrl+* to select the current range. The current range is the whole dataset, in all directions from the current cell until Excel hits the edge of the worksheet or a completely blank row and column. On a desktop computer, pressing Ctrl and the asterisk on the numeric keypad does the trick.
4) Jump to the next corner of a selection
You’ve just selected A2:J987654 but you are staring at the bottom-right corner of your data. Press Ctrl+Period to move to the next corner of your data. Because you are at the bottom-right corner, it takes two presses of Ctrl+Period to move to the top-left corner. Although this moves the active cell, it does not undo your selection. Although I always use Ctrl+Period twice, I should probably learn Ctrl+Backspace to bring the active cell back into view. That will be my new trick for next week.
5) Pop open the right-click menu using Shift+F10
When I do my seminars, people always ask why I don’t use the right-click menus. I don’t use them because my hand is not on the mouse! Pressing Shift+F10 opens the right-click menu. Use the up/down arrow keys to move to various menu choices and the right arrow key to open a fly-out menu. When you get to the item you want, press Enter to select it.
6) Cross tasks off your list with Ctrl+5
I love to make lists, and I love to cross stuff off my list. It makes me feel like I’ve gotten stuff done. Select a cell and press Ctrl+5 to apply strikethrough to the cell.
7) Date-stamp or time-stamp using Ctrl+; or Ctrl+:
Here is an easy way to remember this shortcut. What time is it right now? It is 11:21 here. There is a colon in the time. Press Ctrl+Colon to enter the current time in the active cell.
Need the current date? Same keystroke, minus the Shift key. Pressing Ctrl+Semicolon enters the current time.
Note that this is not the same as using =NOW() or =TODAY(). Those functions change over time. These shortcuts mark the time or date that you pressed the key and the value does not change.
8) Repeat the last task with F4
Say that you just selected a cell and did Home, Delete, Delete Cells, Delete Entire Row, OK. You need to delete 24 more rows in various spots throughout your data set.
Select a cell in the next row to delete and press F4, which repeats the last command but on the currently selected cell.
Select a cell in the next row to delete and press F4. Before you know it, all the 24 rows are deleted without you having to click on Home, Delete, Delete Cells, Delete Entire Row, OK 24 times.
The F4 key works with 92% of the commands you will use. Try it. You’ll love it. It’ll be obvious when you try to use one of the unusual commands that cannot be redone with F4.
9) Add dollar signs to a reference with F4
That’s right — two of my favorites in a row use F4. When you are entering a formula and you need to change A1 to $A$1, click F4 while the insertion point is touching A1. You can press F4 again to freeze only the row with A$1. Press F4 again to freeze the column with $A1. Press again to toggle back to A1.
10) Find the one thing that takes you too much time
The shortcuts in this article are the ones I learned over the course of 20 years. They were all for tasks that I had to do repeatedly. In your job, watch for any tasks you are doing over and over, especially things that take several mouse clicks. When you identify one, try to find a shortcut key that will save you time.
When you perform commands with the mouse, do all the steps except the last one. Hover over the command until the tooltip appears. Many times, the tooltip tells you of the keyboard shortcut.
Subscribe to:
Posts (Atom)