Prep a Workbook to Save as a Template September 24, 2016Posted by computertrainer in Excel Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: Conditional Formatting, Excel template, GoTo Special
add a comment
This Blog is for a favorite non-profit in Manchester, NH …
You have an existing workbook that you need to replace the old data in all the cells with “constant numbers” – maybe it is daily, weekly, monthly, quarterly – frequency doesn’t matter. How did you do this in the past? Maybe you:
- Opened the last workbook and saved it with a new name
- Then you started typing in the new data
I did that years ago until one time when I was handed the new data to enter just as my boss was heading into the meeting where the workbook was needed. So I started entering the new data UNTIL the phone rang and I got distracted. I thought I was done so I saved the workbook, printed it on an overhead, and took it into the meeting for my boss. A few minutes later he came out asking WHY ARE LAST WEEKS NUMBER ON THE BOTTOM PART OF THIS REPORT?
Simple answer – I was distracted by the phone. So I promised I’d find a way so this would never happen again.
The SOLUTION – a Template. Below are steps you might want to do to prepare an existing workbook to be saved as a Template:
- Remove the data from the cells with Constant Numbers:
GoTo (Ctrl + G or F5 key)
Click Special Select Constants and remove the check mark in front of Text.
Press the Delete key on the keyboard
- Conditionally Format the selected cells to make it clear which cells will need data:
NOTE: Do NOT click in any cells in the worksheet or the selected cells will no longer be selected!
Click on Conditional Formatting in the Styles section of the HOME Ribbon
Click on New Rule…
Select Format only cells that contain
Either change Cell Value to Blank or change between to equal to then type =”” (=QuoteQuote with no spaces) in the line at the right
Click Format and on the Fill tab, select any color of your choice (I won’t matter the color because the color will be gone once the cell has data in it)
Click OK twice
- Click in the 1st cell you need to type in
- Excel 2007 – Click the Microsoft Logo in the upper right of Excel
Excel 2010 + – Click the File tab
- Select Save As
- Change the Save as type: to Excel Template
- Enter an appropriate name for the template
- Check to see if Excel automatically selected the Template folder for you to save the template into.
Excel 2013 will create a folder in Documents called Custom Office Templates and save the workbook there.
- Click Save and then close the workbook.
Excel 2013’s Start Screen March 23, 2016Posted by computertrainer in Excel Tips, Office 2013.
Tags: Excel 2013 Start, Excel 2013 Start Screen, Start screen
If you are like me, you will prefer that Excel 2013 opens to a blank new workbook, not the start screen:
Steps to make the blank new workbook your opening screen:
Insert an Empty Row Between Each Row of Data in Excel February 14, 2016Posted by computertrainer in Excel Tips, Office 2007 Tips, Office 2010 Tips, Office 2013, Uncategorized.
Tags: Everyother Row Empty Excel, Excel Insert Empty Rows
add a comment
My daughter phoned one day to ask how she could add an empty row between each row of data in Excel. This is a quick and easy solution:
- Insert a column to the left of Column A
- In the empty column enter “1” in the row with the 1st row of data
- AutoFill the number down
- At the bottom of the screen by the Column A, click on the AutoFill options box and select Fill Series
- The series is selected from 1 to whatever the last row would be numbered. Copy that selected series.
- Press Ctrl + down arrow to go to the bottom of the data and then go down 1 cell to the 1st empty cell below the series and Paste.
- You now have numbered the data from 1 to x and 1 to x.
- Sort the data by column A and then by column B
Tada! Every other row is blank.
HINT: Do not delete column A yet. Perhaps you would use the Group feature to hide it. When you want to resort so you do not have every other row empty, column A will be the “bridge” you need to sort all the data. Simply sort by any column other than column A.
Need more screen space for your Excel Dashboard? October 13, 2015Posted by computertrainer in Dashboard Tips, Excel Tips, Office 2010 Tips, Office 2013.
Tags: Declutter Excel screen for Dashboard, Excel Dashboard, Excel Window, More space on screen for Dashboard, Open up Excel screen
add a comment
With just a few setting changes, you can open the screen on your monitor to allow for more space for your Excel Dashboard:
On the View Ribbon, in the Show section, remove the check mark from:
- Formula Bar
Click the File tab, then Options. On the left select Advanced. Scroll to the section “Display options for this workbook”. Remove the check mark from:
- Show horizontal scroll bar
- Show vertical scroll bar
- Show sheet tabs [NOTE: if you need to move to another sheet, perhaps you have several worksheets with different dashboards, use CTRL + PgDn to move to the worksheet to the right and CTRL + PgUp to move to the worksheet to the left.]
Collapse the Ribbons by double clicking on a Ribbon tab.
You now have an uncluttered screen to display your Dashboard!
Excel PivotTable Issue: Create a Calculated Field that has a Field Title that uses Alt + Enter to Split the Field Title into Multiple Lines June 21, 2015Posted by computertrainer in Excel Tips, Office 2003 Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: Alt+Enter in a Field name, Calculated Field in PivotTable, Excel PivotTable
add a comment
One of the rules for working with an Excel database and preparing it for a PivotTable is that the raw list of worksheet data must contain a header (individual column headings) row and that row can only be one row deep.
Below is a workbook with titles on one row but that makes the columns too wide:
Below the title row is set to wrap text and the columns, when sized to fit, are narrower but do not break where I want:
Use Alt + Enter to split the titles into multiple lines within the cell. It this example, click before 1st and Press ALT + Enter. Repeat for 2nd, 3rd, 4th, and Year:
Now for the Issue of using ALT + Enter in a Field title:
I am creating a PivotTable with the above data (not the ideal format/layout but it will work) and want to add a Calculated Field that gives me the Commission for the Total Year based on a 25% commission rate.
The field titles have all been split by using Alt + Enter. Notice in the Formula: for the Insert Calculated Field dialog box (below) that I only see =‘Sales. The remaining part of the field name is out of sight.
- At the end of the field you see in the Formula section, press and hold the right arrow until it stops moving. It MIGHT show a small flashing dot where the field name ends on the line below what you can see. At that point, I could type the balance of the formula BUT I would have to TRUST that I entered the correct information because I will not see it.
- Cancel the Insert Calculated Field process and return to the data and remove the Alt + Enter by expanding the Formula Bar, click at the start of the 2nd line and press Backspace. The Field title is now on one line. (See below) You will need to Refresh the PivotTable and, perhaps, add the field back to the PivotTable.
Thank you to DB, DH, LW, & NW for pointing out this issue during our PivotTable class.
Tags: Display Ampersand not underscore
add a comment
It you need to use the ampersand (&) in a label or text box in a Microsoft Access database, it will display as an underscore (_).
For example, you type and want to display “Sales & Marketing” but you get “Sales _ Marketing”.
To display the & symbol:
- Double up the & – “Sales && Marketing”. The result will be “Sales & Marketing”
This is for you – Don, Chris, Marlana, Paul, Dan, & Noah!