Welcome to another blog post which is mostly just me documenting things I want to remember later. Here are a few useful keyboard shortcuts in Excel. I’m maybe a little old-fashioned and like to keep my hands on the keyboard as much as possible, so I like using these keyboard shortcuts instead of right clicking or using a button on the ribbon (menu). I will return to this post to add others as I think of them.

Forgive listing these: Ctrl+C – copy, Ctrl+X – cut, Ctrl+V – paste

Navigation:

• Home – move to the first column of the current row
• Ctrl+Home – move to the upper-left corner (A1)
• End – move to the last column with data of the current row
• Ctrl_End – you guessed it, last row, last column of data
• Ctrl+arrow – move the start (left), end (right), top, or bottom of the data
• Adding Shift to any of these will select the range from the current cell to the destination. Ex. you are on cell G20 and press Ctrl+Home, it will select all the cells from column A to column G in rows 1 – 20

Other Handy Shortcuts

• F2 – Cell edit. Normally when you start typing it replaces what is in the cell. Press the F2 function key first and you can append/edit what is in the cell without clicking into the formula bar. Use with Home, End, and arrows to move the cursor within the existing contents.
• Esc – exit without changing. Oops, you hit a key and it’s replacing the contents of a cell, just click Esc to revert the cell contents back.
• Ctrl+; – enter today’s date

Bonus

• F2 also works for renaming files in Windows!

Flag if one of two strings is found in a cell.

=IF(AND(ISERROR(FIND(“fall 2020”,AB2)),ISERROR(FIND(“Fall 2020″,AB2))),”N”,”Y”)

IFS, Switch, Min
It was an Excel formula before it was a PS Query Expression

=IFS(X3>0,0,W3>0,SWITCH(V3,”NO NEED”,250,”HIGH”,500,”MODERATE”,500,”LOW”,500,”NO FAFSA”,0,0),W3<1,SWITCH(V3,”HIGH”,MIN(1200,S3),”MODERATE”,MIN(1000,S3),”LOW”,MIN(750,S3),”NO NEED”,MIN(500,S3),0)) 

Convert number (Student ID) into text to replace and keep leading zeros
=TEXT(C2,”0000000000″)

Does a cell contain one or more of these given strings
=IF(OR(ISNUMBER(SEARCH(“2021”,D3)),ISNUMBER(SEARCH(“2020”,D3)),ISNUMBER(SEARCH(“2019″,D3))),”Yes”,”No”)

Explained: SEARCH returns a number indicating which character position your given string (“2021”) starts at in the target cell (D3). If the string is not found it returns #N/A. So ISNUMBER is used to return True if SEARCH returns a number (and False if #N/A). Three of the ISNUMBER SEARCH pairs are grouped with the OR so that if any of them is True than True is returned to the IF statement.

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

A quick way to organize a data table in Excel is with Filters.  You can use filters to hide the rows of data that do not match the parameters you specify.  These parameters could be lists of values in one or multiple columns or greater-than less-than or ranges for number values.  You can also use filters for sorting.  To get started, select the column headers that you want included in the data table.  Click the Filter icon on the Home or Data menus.  Click the new icon next to the column header to see your options.  Once you have filtered a table you will see the icon change to a filter and the status bar at the bottom will show how many matches there were.  The example below shows “4 of 7 records found”.  You can re-select all of the items or click the filter clear icon to un-filter the list.

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

You may at times have duplicate values depending on the level of detail in your data.  For example, if you have class enrollments listed, you will have multiple rows for each student within each term.  That is an obvious situation.  But there may be other times where it is not obvious that you would have duplication, such as a student having multiple careers in the same year.  Excel delivers ways to quickly identify duplicate values and eliminate them if needed.

Identify Duplicates

1. Select a column or group of cells that you wish to identify duplicates among.
2. Click the Conditional Formatting button on the Home tab.
3. Select Highlight Cell Rules and click Duplicates Values…
4. Choose the color you prefer in the box that pops up and click ok.

Remove Duplicates

1. Select column with duplicates values you want to be removed.
2. Click Remove Duplicates from the Data tab.
3. You get options to expand the selection and choose other columns or just remove values
   from the selected column. Hit ok when you’ve made your choice

This is part of Tuesday Excel Tips series.

You probably already know that the real power of Excel is in using formulas.  But you may not know that Excel comes with a few handy formulas already defined to make them easy for you to use.

If you select the cell below or to the right of a series of number, you can use the quick formula button to get the Sum, Average, Count, Max, or Min of the series.  If you just click the Sigma button it will Sum the numbers.  If you click the little down arrow you can select one of the other functions.

You can also do like the example below and select a data series and then use the quick formula.

Check out the listing of all the Tuesday Excel Tips.

This is part of Tuesday Excel Tips series.

It’s fitting that I only have a small amount of time today to do this post.  Because this tip is all about getting information quickly.  If you highlight/select a group of cell in Excel it will give you some information about the data in those cell in the status area at the bottom of the window.  If the values are numeric Excel will show you the average, count, and sum of the values.  If the values are non-numeric then it will show you a count.  This is very hand if you just need to get one of those figures quickly and don’t need to keep the calculation on the sheet with the rest of the data.

Check out the listing of all the Tuesday Excel Tips.

This is part of the Tuesday Excel Tips series.

If your spreadsheet needs some kind of list or series of labels such as a count, days of the week, days, months, etc. Excel can help you out with that.  As shown in the example below, a count is being entered into the first column.  We only need to enter the first couple of values so that Excel can determine that we increment the value by 1 each time.  Select those values and then place your mouse pointer on the box in the lower right corner of the selection.  Drag the cursor down to the last row you’d like filled and Excel will fill in the values for you.  If you already have data populated in the adjoining rows, you can just double-click that box and it will fill in the series down to your last row of data.  I think you will be pleasantly surprised how often Excel will figure out what values you’d like filled in.  This also works with formulas.  If in cell A1 you have the formula “=B1+C1” you can copy that down to the following rows and Excel will automatically update the references to B2+C2, B3+C3, and so on.

Check out the listing of all the Tuesday Excel Tips.

This is part of the Tuesday Excel Tips series.

Adjusting Column Widths – When you first open Excel all of the columns are the same width.  If the entries in a column are longer than the column is wide you may want to adjust the column width to fit.  Or if you’re like me and have lots of columns, you may want to make some columns small to get more on the screen.

There are LOTS of ways to adjust the width of a column or a group of columns, here are some of them:

1. Put your mouse pointer between the columns and drag the divider to the right or left.
2. Double click the divider to automatically widen the column enough to fit your longest entry.
3. Right click on a column header, choose column width, and specify a value.
4. Click the upper left most corner (above 1 and left of A) to select the entire worksheet, then double click any column divider to adjust all columns to their longest entry.

Check out the listing of all the Tuesday Excel Tips.

This is part of the Tuesday Excel Tips series.

Format Painter allows you to apply the formatting of one cell to other cells.  You just select the cell that has the formatting, click on the Format Painter, then select the cells you want to format.  In the example below, cell A1 is bold and has a bottom boarder.  This formatting is applied to the other column titles using the Format Painter.

Check out the listing of all the Tuesday Excel Tips.

Every Tuesday for the next couple of months I am going to post a tip to help you be more proficient with Excel.  You can bookmark this page as I will add links to each of the posts as they are published.

Here are the posts that have already been published and some future topics:

2017-07-25:  Using Format Painter
2017-08-01:  Adjusting Column Width
2017-08-08:  Copy Down Series/Formulas
2017-08-15:  Highlighting Cells for Quick Info
2017-08-22:  Quick Formulas
2017-08-29:  Identifying/Removing Duplicates
2017-09-05:  Filters

If you have any tips to share, I would love to hear them in the Comments section!

Cheers,

John