Excel & Office 365 Tips and Tricks

“Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.”  — Eric Rosenberg

Sometimes you want users to enter data in a certain format such as a 9-digit Social Security Number as 123-123-1234 or a 10-digit Phone Number as (555) 555-5555. You can add an Input Mask to a cell to accomplish this.

  1. Select the range of cells you wish to apply the format to.
  2. Select the HOME tab, expand the Number group by selecting the tiny arrow in that section. Or you can right click your selection, then Format Cells.
  3. In the Category box, select Special.
  4. In the Type list, select Phone Number. Or you can type this code in the Type box as a Custom format: ([<=9999999]###-####;(###) ###-#### ).

Whenever you want to give users instructions on entering data, instead of using the comments functionality in Excel, you can use the Data Validation feature. This way you can keep your spreadsheet clear of all those pesky comments and only display a message whenever a user selects the appropriate cell.

  1. Select the cell(s) you want to give your user instructions for.
  2. In the Data tab select Data Validation.
  3. In the Data Validation dialog box, select the Input Message box.
  4. Enter a Title and/or your Input message, then select OK.
  excel data entry popup

Ever needed an easier way to distinguish data in a spreadsheet faster? It's possible to sort data by color. Let's say that you wish to have data that is color coded by Quarter.

To sort data by color, execute the following steps.

  1. Click any single cell inside a data set.
  2. On the Data tab, click Sort. The Sort dialog box appears.
  3. Sort by Last Name (or any other column), sort on Cell Color (you can also sort on Font Color and Cell Icon), and select the green color for the first level.
  4. Click 'Copy Level' two times and select the other colors.
  5. Click OK.
  6. See how much easier it is to group like data together?

    color coded columns

This example shows you how to use data validation to prevent users from entering duplicate data into a column.

  1. Select the range of cells you do not want duplicate values in.
  2. On the Data tab, select the Data Validation drop down.
  3. In the Data Validation dialog box, on the Settings tab, under Validation criteria, select Custom from the Allow drop down.
  4. In the Formula field, enter the formula example and select OK:
    =COUNTIF($A$2:$A$20,A2:A20)=1Explanation:
    The COUNTIF function takes two arguments.
    =COUNTIF($A$2:$A:$20,A2) counts the number of values in the range A2:A20 that are equal to the value in cells A2:A20. The =1 tells you that the value may only occur once.
  5. To add you own error message, on the Data Validation dialog box, select the Error Alert tab.
  6. Enter a Title and short message.
  7. Uncheck the Show input message when cell is selected checkbox, then OK.

Excel comes with many built in functions like COUNT, CONCATENATE, DAYS, most of them you will probably never use. But what if there was a calculation that you needed to perform on a regular basis but each time you needed to use it you had to ask a co-worker what the calculation is or look it up somewhere. You could make it available outside of Excel, say in a document, text file or even a kb article. What if you could easily add it to your spreadsheet without having to remember or ask? Well in Excel you can create your own custom function with all the calculations built in and all you have to do is enter the data. There are some prerequisites of course, so be sure to get these out of the way first.

  1. Activate the Developer tab. The Developer tab contains many features that allow you to customize Excel, such as creating macros, adding/creating custom Add-ins, use Form Controls, and many more.
    1. Select the File tab, then the Options link to open the Excel Options dialog box.
    2. Select the Customize Ribbon link
    3. Under the Main Tabs section on the right, select the Developer option, then select OK
  2. First plan your calculation.
    1. >Write it down on paper.
    2. Ask what variables you need to work with. Variables are the data that changes with each calculation that you capture in your spreadsheet.
    3. Have an example ready that you can test your formula with.
Now that you 've planned it all out, you are ready to create your formula.
    1. Open the spreadsheet you want to add the custom formula to.
    2. Click on the Developer tab, then click the Visual Basic icon in the ribbon.
    3. Right click on the name of your workbook.
    4. Click on the Insert tab, then select Module to create a new Function (or formula). (Modules are like a folder that stores code within your workbook.)

    5. visual basic module

    6. This opens up a blank sheet where you can enter your formula.
    7. You first need to declare your variables. Variables are just another way to name things, like what data you are expecting the user to enter.
    8. Your variable will be in this format:
      • the word Function,
      • then the name of your function followed by open and closed parenthesis,
      • inside the parenthesis you would put your VariableName(s) (what you are calculating, no spaces, use capital letters for each word),
      • then the word As followed by the Data Type (you have to tell what kind of data the final output is to be, such as Date, text or currency).
    9. If your formula is: TravelCost=(TravelMiles * FuelCost) / MPG, you first need to describe the data type of the items you're using in your calculations.
    10. So your first line might look like this:Function TravelCost(TravelMiles As Integer, MPG as Integer, FuelCost as Currency) As Currency.
    11. Then you need to write your formula like this: TravelCost = (TravelMiles * FuelCost) / MPG
    12. Be sure that End Function is listed on the last line. So your function should like something like this:

    13. vb function
    14. Close and Save your module, and then Save your workbook.
    15. Now place your cursor in the cell where you want to add your formula. Then start typing in the formula bar: =FormulaName until you see the formula you just created.

    16. formula name
    17. Double click on the name to select. You should see the start of your formula. =TravelCost(
    18. Place your cursor after the opening parenthesis, then click on the fx button next to the formula bar to bring up the dialog box where you can enter/select your variables. You can either manually enter the data or you can select the cell that contains your data.
    19. function arguments
    20. Once you're done entering your data, click OK.
    21. Another option to getting your attributes in the formula line is to start typing your formula name (=travel) then press the TAB key to complete your formula name. After you see the open parenthesis, press Control-Shift-A. Then your variables will be listed, the first one to complete is highlighted. You can double click the attribute name and either type in your data or select the appropriate cell that contains your data. When you're done, select Enter.
    You have just created a custom formula.

Sometimes you may have a spreadsheet that you've created as a template and have provided that template to users to enter data.  In this scenario, you only want users to be able to change certain parts of the spreadsheet and not others. You can protect the spreadsheet and "unlock" certain cells within the spreadsheet by following the steps outlined below. Locking or protecting a spreadsheet is not common practice so it's best to let your users know that the spreadsheet is locked and how they are expected to modify or add data to the spreadsheet. Otherwise they may think that there's something wrong with the file and get frustrated. I usually add an instruction page as the first worksheet and name it READ ME and provide general information such as the purpose of the spreadsheet, as well as instructions on how to update the data.

  1. Highlight the cells you want users to edit. This is to make it easier to identify since users will not be able select any cells that are locked.
  2. Select entire spreadsheet, then Ctrl+Shift-F to bring up the Format Cells dialog box.
  3. Select the Protection tab, then select the Locked checkbox if it isn't already checked, then OK.
  4. Now to unlock the editable cells, select the cells you want users to be able to edit. You can use the Control key to select multiple cells.
  5. Then Ctrl+Shift-F to bring up the Format Cells dialog box.
  6. Select the Protection tab, then un-select the Locked checkbox.
  7. Select the Allow Users to Edit Ranges option in the Review tab.
  8. Select on the Protect Sheet button.
  9. Check the Protect worksheet and contents of locked cells at the top of the Protect Sheet dialog box.
  10. Under the Allow all users of this worksheet to: section select/unselect as appropriate. I recommend selecting the Select unlocked cells checkbox and unselecting all the others. This way users can only place their cursor on and/or tab to editable cells.
  11. Click OK

Now your spreadsheet is protected and users are only allowed to enter/edit data where you want them to.

If you have a long number (i.e. 1,234,567) and want to change it to a short decimal number (i.e. 1.23 million) use this formula: =IF(A1/10^6<100,ROUND(A1/10^6,2) & "million",ROUND(A1/10^9,2) & "billion")

Original Number Shortened decimal
1,234,567.00 1.23 million
1,234,567.50 1.23 million
Or if you don't want to include the words, just: =IF(A1/10^6<100,ROUND(A1/10^6,2))
Original Number Shortened decimal
1,234,567.00 1.23
1,234,567.50 1.23

If you have a spreadsheet that has a column of links and you want to display the actual link text there are a couple of ways you can do this. Manually or with VBA code.

Manual

If you are not able to work with Macro enabled files you can do this manually following the steps outlined below.
  1. Highlight the cell with a hyperlink.
  2. Select Ctrl-K. This opens the hyperlink dialog box. Once it opens your cursor is already on the Address field.
  3. Select Ctrl-A to highlight the entire URL.
  4. Select Ctrl-C to copy.
  5. Select Esc or Enter key to close the hyperlink dialog box.
  6. Select  Ctrl-V to paste the URL in a new cell.

VBA

If you are able to use a VBA solution you can create a VBA module and add the following code.

  1. Open the VBA Editor by pressing Alt+F11.
  2. Select Insert > Module to insert a new module.


  3. Copy the code below and paste the Excel user defined function below.
    Function GETURL(rng As Range) As String
    On Error Resume Next
    GetURL = rng.Hyperlinks(1).Address
    > End Function

  4. Press Alt+Q to save and exit the module.
  5. Back in your spreadsheet put your cursor into the cell you wish to convert to text.
  6. Use this syntax for this custom Excel function:  =GETURL(cell) and press Enter.


  7. You may need to click on the text in the function that it's referencing to get it to convert. Meaning in the formula bar where it has =GETURL(A1), put your cursor on the "A1" then press Enter.

What is a pivot table?

"A pivot table is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns and trends in your data." --Microsoft support

What is the difference between a pivot table and a pivot chart?

A pivot chart is an interactive graphical representation of the data in a pivot table. Pivot tables allow quick analysis of a subset of data. For example, imagine you have a spreadsheet listing dogs available for adoption at an animal shelter. This spreadsheet lists the dog's name, breed, age, weight, color, location, cat/children friendly and whether they're house trained. You can use the pivot table feature in Excel to quickly assess which locations have a specific breed of dog, or which ones have friendly dogs.

How to create a pivot table

  1. Select all of the data in your spreadsheet. The data needs to be in a "table" format with headers in the top row.
  2. Select Insert > Pivot Table
  3. Click the OK button in the Create Pivot Table window.
  4. In the PivotTable Fields pane, for the fields to be considered in the analysis, drag the field names to the Filters, Columns, Rows, and Values windows as desired.
Example:


Sam wants to know how many of each breed of dog are:
  • Kid friendly and house trained
  • Cat friendly and house trained
  • Cat, kid friendly and house trained
  • Neither cat or kid friendly and house trained
To do this:
  1. Select all of the data by >pressing Control+A
  2. Select> Insert > Pivot Table.



  3. Click on the OK button in the Create Pivot Table window.


  4. In the Pivot Table Fields pane, drag the field names to the appropriate boxes to be considered in the analysis.
  5. The Rows of results should be by Breed.
  6. The Columns are for the different values for the Friendly attributes.
  7. The Value in each cell should be the Count of the Friendly attributes for that breed.
  8. All of the data should be filtered by the House Trained attribute.



  9. Click on the Row Label and Column Label fields to deselect Blank because that is not one of the attribute values you want to show.



  10. Click on the House Trained drop down and select HT so only house trained dogs are counted.


RESULT:



This is just one example of how you can use Pivot Tables. Visit the Microsoft Office Support site for more info.