Must-Know Excel Tips & Tricks to Help You Save Time
While it's possible to do virtually everything in Excel with your mouse, it generally takes much longer. Keep reading to discover a more efficient approach to working with Excel that combines your mouse and a few shortcut keystrokes.
Even if you don't have much experience working with Excel, you probably know that pressing the Enter key will move your cursor down one cell. However, in today's fast-paced business world, you'll need to know more than that to stay ahead of the competition.
Watch our latest video to learn more about how you can save valuable time when working with Excel:
What Are the Top Microsoft Excel Tips & Tricks You Need to Know to Work With Excel Data Like a Pro?
- Navigating in Excel: Another basic keyboard shortcut is using the arrow keys to move up down left or right one cell at a time. But how can you move a great distance or select a large area with just a few keystrokes? Here's a list of some shortcut keystrokes to move around quickly in Excel:
-
- Go to the last row in a column (that has data) End Down Arrow.
- Go to the last column in a row (that has data) End Right Arrow.
- Go to cell A1 from anywhere Ctrl + Home.
- Select all data Ctrl + Shift +End.
- Select the entire sheet Ctrl + A.
- Copy what's highlighted Ctrl + C.
- Cut what's highlighted Ctrl + X.
- Paste Ctrl + V.
- Find something Ctrl + F.
- Find and replace Ctrl + H.
- Print Ctrl+ P.
- Save workbook Ctrl + S.
- Open another workbook Ctrl + O.
- Move to the next sheet (tab) Ctrl + PgDn.
- Move to the previous sheet Ctrl + PgUp.
- Entering Dates and Times In Excel: Entering dates and times can be done in many different ways in Excel. You only need to know whether the value is volatile (changes every time the workbook is updated) or non-volatile, as well as the appropriate format recognized by Excel. Let's take a look at the various ways you can enter dates in Excel:
-
- Volatile Dates: One way of entering the date in Excel is by migrating to a cell and typing =today. However, if you open up the spreadsheet the following day, the cell will display that day's date.
- Volatile Date and Time: Navigate to a cell and type =now. In addition to the number of days since January 1st, 1900, this also displays the current time as a percentage of 24 hours. Each time the sheet is recalculated, the time is changed.
- Non-Volatile Dates: If you simply go to a cell and key in the date, Excel will store it as a serial number representing the number of days since January 1st, 1900 to that date. So how can you enter a date that doesn't change from day to day and shows up as a date? You can do this by pressing Ctrl and the Semicolon key. This creates a date that will be displayed in the formula bar at the top as well.
- Entering Dates Using (.) instead of (/): While this may look like a date, Excel stores it as a value, for example, 805.2020.
- Entering Time Using the 12/24 Hour Format: Let's take an example of 5 am. Although this looks like a time, it's not. In Excel, the time could either be in the 24-hour format, for example, 16:00 for four o'clock, or 4 00 PM in the 12-hour format. When using the 12-hour format, ensure to leave a space between the time and the AM/PM. Excel will recognize both of these as times and store them purely as percentages of 24 hours, and they won't be connected to any date, for example, 0.67.
- Entering Repeating Data: Very often, when using Excel, you might want to have an ordered list, for example, January-December or 1-10. Not only does excel have a function for that, but you can also develop your own custom lists. Here's how you can save time when entering repeating data in Excel:
-
- Navigate to the cell containing your formula, for example, a date.
- To apply this formula to the cells below it, simply hover above the cell’s bottom right corner.
- Double click once the cursor has turned into a dark, bold cross. This will fill all the cells below it up to the last filled cell of the previous column with concurrent dates.
- But what if you want to fill down a series? Here's what you need to do: highlight the first two or three items that define the series, for example, 1,2... or January, February...
- Highlight the cells containing the first items of the series and hover above the bottom right corner.
- Once your cursor has turned into a dark, bold cross, double click and drag down as far as necessary.
- Entering Formulas: In Excel, all formulas begin with = or +. You can write a simple formula using the normal mathematical operators, for example, =5+3. When you press enter in the cell, the result will be 8. For division, you can write =8/2, which results in 4.
- However, there's another operator for patching characters together. You can use the ampersand (&) to concatenate the numbers together to make a string.
- Excel also has built-in functions such as SUM, AVERAGE COUNT, STDEV, and many more broken into categories.
- Let's say you have values from A30 to B41 and would like to sum up each row. To do this, head to E30 and type in =B30+A30. Excel interprets this as adding the cell one column to the right to the cell two columns to the right.
- Next, navigate to cell C30, where the result is displayed. To fill this formula down, simply hover above the bottom right corner of C30 until the cursor turns dark and double click. This will automatically produce the right answer for each row.
Looking to Partner With the Top Microsoft Solutions Provider In Western New York?
Our experienced IT professionals at Globalquest Solutions are here to answer any questions you may have to become more productive in Excel.
Contact Us Now to get started | Call us at (877) 440-3061.