EXCEL: Did You Know?….. Part 1

2 Comments

Using Ctrl+Enter to place information into multiple cell simultaneously!

If you need to enter the same data into multiple cells, Excel offers a fabulous shortcut.

  1. Select all the cells that you need to change.
  2. Enter the value, text, or formula.
  3. Press Ctrl+Enter.
  4. The same information is inserted into each in the selection.

Using a form for data entry!

When you have a list of information in a table, the column names do more than describe what needs to be typed in that column but can be used in a form.  If the table has many columns, it may become an inconvenience to scroll back and forth. One way is to use Freeze Pane to keep the main column(s) and row(s) from not moving. I recommend using the Form command which will become a data entry where it will display the column headings as fields for input.

  1. Place in the Quick Access Bar, the Form command.
  2. Type out the column headings. For example: First Name, Last Name, Address, City, State, Zip Code, and Salary.
  3. Click on Insert Table and select the headings.
  4. Check in the dialogue box “My table has headings”
  5. A blank row appears in the table.
  6. Format each empty cell under each heading to accommodate the information. For example: place the Accounting format for the Salary column.
  7. Click on the Form icon from the Quick Access Bar
  8. A data entry form appears.
  9. Enter the information in each field and hit Enter to enter the information into the table.
  10. This is  will definitely expedite entering the information.

Entering the current date or time into a cell using the keyboard!

  • Current date:  Ctrl+;
  • Current time:  Ctrl+Shift+;

Formatting numbers using the keyboard!

  • Ctrl+Shift+~  —> General format.
  • Ctrl+Shift+$   —> Currency format with two decimal places.
  • Ctrl+Shift+%  —> Percentage format with no decimal places.
  • Ctrl+Shift+!   —> Two decimal places, thousands separator, and a hyphen for a negative values.

 Using the keyboard to enable the Cells dialogue box!

  • Ctrl+1

Using the keyboard to insert a blank or a new workbook!

  • New workbook:   Ctrl+N
  • New worksheet:  Shift+F11

Selecting cells without using the MOUSE!

  • To start the the highlighting process, simply hit F8 and the arrow keys. Hit F8 to turn it off.
    OR
  • Hold down the shift and use the arrow keys.

To change formats on multiple sheets at the same cells at the same time!

  1. Activate the first worksheet.
  2. Press down on the Ctrl and click on the second worksheet.
  3. Notice that the workbook window’s title bar displays [Group] to remind you that you have selected a group of sheets and that you are in Group mode.
  4. Apply the formats.
  5. The worksheets have now been formatted at the same time.
  6. Click on another sheet outside the group to ungroup the selected worksheets.

KEEP AN EYE OUT FOR PART 2!!!!!

Advertisements

EXCEL: MINIF, MAXIF, DMIN, and DMAX

3 Comments

There is a way where you can find the minimum value and a maximum value for a specific product from a large table of data.  For example,the table below is a list of different fruits and their prices.  We can  find the lowest and the highest prices for Apples. I set up an Excel sheet showing formulas using MINIF and MAXIF and an alternative way using DMIN and DMAX.

minif and maxif

 

Click on the picture to enlarge the view.

In the first scenario, place the name of the fruit in a cell D2. I created the MINIF formula in E4 to find the lowest price for apples. You do not include the field name of the table, just the data.  The same for MAXIF in E5.  The “IF” in the MIN and MAX formula tells Excel to look only if the fruit is equal to apples and find the minmum price among that group. The MINIF and MAXIF are array formulas and you need to NOT hit Enter but to click on CTRL+SHIFT+ENTER in order to enter it into the cell.

=MIN(IF((A2:A17=D2)*(B2:B17),(B2:B17)))
=MAX(IF((A2:A17=D2)*(B2:B17),(B2:B17)))

 

In the second scernario, which is mucher simplier, is to use the DMIN and DMAX.  The “D” represents the database which includes the fieldnames (fruit and price). The formula needs the database, the name of the field in double quotes, and  the criteria area where you enter any of the fruit category (located in D12 and D13).

=DMIN(A1:B17,”Price”,D12:D13)
=DMAX(A1:B17,”Price”,D12:D13)

Have fun!

EXCEL: Keeping Format Painter Active

3 Comments

The Format Painter button offers a great service. It copies the formatting from your selected cell or data range and pastes it onto the next data range you select.

But if you need to use the Format Painter to add your copied formatting to more than one non-contiguous data range, you’ll find yourself going back and forth using the Format Painter icon. You probably select the cell with the desired formatting, click the Format Painter button, select the data range to acqire that formatting style, go back to the original cell, click on Format Painter button again, highlight new cells that needs to be formatted, and so on and son on.

But here’s an insider tip: You don’t have to go through this time-consuming, back-and-forth process.

  • First add formats to a cell.
  • Simply Double Click on the Format Painter button and apply to another set of cells.
  • The paintbrush cursor stays visable even after you apply the formatting to the first set of cells.
  • Keep on selecting cells and the non-contiguous cell.
  • Click on the Format Painter button again to deactive it.

WORD: Creating a Table of Contents for a Newsletter in Word

Leave a comment

If you are creating a newsletter in Word, the norm is to put the Table of Contents in the bottom first column.  Strategically, the reader will scan the first page from left to right and diagonally down back to the left forming a Z. It is a good idea to place a Table of Contents in order to tell the reader where the topic can be found on the designated page.  I suggest the you Insert a Text Box.  Here are the simple steps:

  1. Place a Heading 1 or Heading 2 style on each topic name.
  2. Leave ample space in the bottom of the first column.
  3. Click on the Insert tab and click on the down arrow on the Text Box icon and choose Draw Text Box
  4. The cross will help you draw the box in the open space.
  5. Click on the References Tab and click on the Table of Contents icon.

If the Table of Contents pages are not displayed, it is because the margin on the ruler surpassed the column of the text box. The text box has its own margin and you need to drag the right tab marker and the margin icon to display the page numbers.

To understand styles and Table of Contents, please read my blog: WORD: Creating a Table of Contents AUTOMATICALLY!  in the June 2012 blogs.

brochure thumbnail

EXCEL: Using Two Validation Lists Where One Choice From One List Affects The Options in Another List

5 Comments

 

My husband asked me awhile back how to choose one option from a validation list and only show the options for that choice in another list. In order words, he would like to change a list’s values based on what is selected in another list. I was stumped for a brief moment. Then I remembered that Chandoo.org may help. And sure enough the answer came to us from Chandoo.  This is a great site to help you when you a faced with a difficult task to perform in Excel.

  • The following is a list that contains areas with associated projects.
  • Marketing has Horizon2010, Bingo 19x, Cosmo, and Spring2009 projects and the other areas are designated to other projects.
  • Creating validation lists are easy but you want to control what one person enters in one list determines what is shown in the second list.  For example, if one employee is in the Ops area, then that person can be on one of the three projects (Kayak, Karmic On, or Chain2Customer) and you want the second list ONLY to display those three projects.
  • When you create a validation list to display the 4 areas, Excel will display a drop down arrow to the right of the cell to display the 4 areas that you typed in the validation rule as shown below in the Select Area column. You then can autofill this cell down to the end of the table to perform this validation rule.

ggg

  • Go to data validation (menu > data > validation). Here is what the validation menu looks like:pic3

 

  • Then, I want Excel to show me in the Select Project column ONLY the Ops choices for the project that person is working on.  I created the Area and Project list and placed it near the table to make it easier for you to understand. In reality, I would have typed this information away from my main table on the SAME sheet.  You need to created a validation rule in order for the Select Project to display only the infomation pertaining to the Select Area.
  • Use OFFSET and MATCH to determine which range to use. Assuming the list of projects for each department is in a range G4:G16 with column B having the Area name and column C having the Project name and the list is sorted on column G, we can use OFFSET() and MATCH() combination along with COUNTIF to determine which range to use for project cell drop-down.
    • For the department cell, we can use simple list validation with values as “Marketing, Ops, Sales, IT”
    • For project cell, go to data validation (menu > data > validation) and specify a formula like this:
      =OFFSET($H$3,MATCH($B4,$G$4:$G$16,0),0,COUNTIF($G$4:$G$16,$B4),1)
    • (you must type the above formula in the Source location as seen below…it does not show the entire formula)
    • What is above formula doing? It is fetching a sub-range from the by finding where the first entry for the selected department is, returning x number of rows from that point, where x = no. of projects in that department.
    • Autofill this formula and since there is a Mixed Addressing (Relative and Absolute together) it makes it easier to copy down.

solution 2solution

 

  • Notice that choosing Ops ONLY shows you the three projects for that area!. Now it is easier to select both Area and Project! That is all. You now have a list drop-down that changes values based on what is selected in an earlier cell.
  • The following diagram show how easy it is to pick and choose.

soluton4