SYM Tips (MS Office & Beyond) - SourceYourMeeting.com

1 Personalization Tips (In MS Office & beyond ) Contact Us: (Page 1 of 9) (Last Edited: 04/13/2016) Important: Hold the Ctrl key on your keyboard while clicking the F key to search this document. Microsoft Excel is a powerful tool. However it does have limitations. It benefits us all to be aware of shortcuts that will allow us to perform more complex tasks in as few clicks as possible. Below we have compiled crowdsourced tips from our clients that will save you time, not only with the reports you use in but any time you use Excel. If you really want to reduce your manual tasks even more and are willing to overcome doing it the old way to gain process efficiencies consider using the SYM Guest User functionality. This will allow you to have your internal or external client view online grid reports for the projects/meetings you share with them.

2 Contents Do you have Tips to Share? .. 1 Tip 1. Excel: Customizing Reports in Excel .. 2 Tip 2. Excel: Switch (transpose) columns and rows .. 2 Tip 3. Excel: Date Reformatting of an entire 3 Tip 4. Excel: Cell Values Starting With 0 (Zip Codes) .. 5 Tip 5. Excel: Other valuables Excel items to consider .. 5 Tip 6. Outlook: Automate filing of emails .. 6 Tip 7. Other MS Office Tips .. 8 Tip 8. Other Tools to consider adding to your tool belt .. 8 Do you have Tips to Share? Excel has A LOT of functionality. These are some of the most common frustrations we've heard from talking with meeting planners over the years and working in Excel ourselves, but maybe there's a secret or lesser known function or feature you know of. Please don't hold back! We'd love to hear about it in the comments. Email and put Excel Tip in the Subject line.

3 Short and sweet is fine, we can pretty it up. Personalization Tips (In MS Office & beyond ) Contact Us: (Page 2 of 9) (Last Edited: 04/13/2016) Tip 1. Excel: Customizing Reports in Excel 1. If you would like to reduce the amount of Excel editing you have to do considering using the Guest User functionality. This allows your customer to customize their own report and see the online grid for just the projects/meetings you share with them. 2. Create Macros when you have repeat actions. You know what they say about doing the same thing over and over again. 3. Once you create a template do Save As so you don t overwrite it by mistake. (SavesAs Template (.xlmt)) 4. Copy a Tab of data to another tab before manipulating the data so you have a starting point to go back to. 5. Hide columns you don t wish to display, (avoid Delete whenever possible) ( has a great tutorial on using the Protect Workbook as well, to protect hidden columns from other users.)

4 6. Widen columns by the width of the data (Careful not to expand all to the number of characters because comments fields have 1000+ characters) a. Double click on the vertical divider line between a columns (or multiple selected columns) b. Another function that is helpful is to Wrap Text in cells that have Comments c. Format (Horizontal Alignment) 7. Sort and Filter Here you will want to set your order or filter data a. Filters on a single Row or Multiple Rows using Filter on the Data Tab in Excel 8. Preparing to Distribute or Show a. Page Setup (Preview / Page view / Gridlines / Row and Column Headings) this is where you will want to: i. Change the orientation to Landscape ii. Adjust the Scaling to fit more on a page iii. Add a customer header and footer for Page 1 of # , Logo, Phone Number, etc) iv.

5 On Sheet Tab click Gridlines , Row and column headings v. Print Titles (Page Layout Tab) allows you to select a row or columns you d like to show on every page on reports that will print on multiple pages. b. Adjust formats of data with Format Painter Tip 2. Excel: Switch (transpose) columns and rows / Paste Special If data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other. 1. Before getting started we recommend doing a filter or sort on the SYM Online Grid before exporting to Excel. 2. Transpose: Personalization Tips (In MS Office & beyond ) Contact Us: (Page 3 of 9) (Last Edited: 04/13/2016) a. CTRL+SHIFT+SPACEBAR to select all the date in that worksheet b. CTRL+C c. Click + at the bottom to create a new tab (aka.)

6 New Sheet) d. Right click and select the Paste Special Transpose option (or pull down menu from Paste Menu) e. Click the top left cell (Cell A1) to deselect all but that cell. f. Double click the vertical divider line between columns A and B to widen column A to the widest text in that cell g. Select the columns to the right of column A that contain data i. Click and drag the vertical divider line between Column B & C and drag until you like the width of all the columns ii. Click Center Text icon to Center the Data in all those columns h. If you want more text to display in the Performance Comments , Why 1 , Why 2 , Concessions , Global Comments (Rows 32, 47, 48, 49, and 50) i. Highlight the rows and right click ii. Format Cells, Alignment Tab, Wrap Text Tip 3. Excel: Date Reformatting of an entire column 1.

7 Highlight the column 2. Hit ALT, a, e on your keyboard (or Go To Data on the ribbon, Select Text to Columns 3. At Step 1 hit Next 4. At Step 2 hit Next 5. At Step 3 click Date, then Finish Personalization Tips (In MS Office & beyond ) Contact Us: (Page 4 of 9) (Last Edited: 04/13/2016) 6. Highlight the column again 7. Hit CTRL + 1 on your keyboard (or Right click and select Format Cells) and select the Date format Personalization Tips (In MS Office & beyond ) Contact Us: (Page 5 of 9) (Last Edited: 04/13/2016) Printable Tip Card: Tip 4. Excel: Cell Values Starting With 0 (Zip Codes) Excel was primarily built for working with numbers and doesn't recognize 0 as having any value. If you enter an apostrophe before any number that begins with 0, Excel will retain it. Tip 5. Excel: Other valuables Excel items to consider 1.)

8 Group rows in a table a. 2. Filter by data in columns a. 3. Sort Data by multiple columns a. 4. Text to Columns there are a lot of creative uses for this feature (Starting in Excel 2013 see Flash Fill feature ( has a great tutorial). a. 5. Conditional Formatting ( has a great tutorial) 6. =Trim gets rid of trailing spaces ( has a great tutorial) 7. keyboard shortcuts 8. AutoCorrect Personalization Tips (In MS Office & beyond ) Contact Us: (Page 6 of 9) (Last Edited: 04/13/2016) 9. Comparing two documents 10. Hidden Data in your documents 11. Pin your documents 12. Removing duplicate data 13. Ctrl ~ to see formulas 14. Apply to All your tab Tip 6. Outlook: Automate filing of emails A rule automating the moving of emails from your Inbox to a specified folder is shown below. This is great for auto filing the eRFI Notification emails for instance.)

9 For the purpose of this instruction let assume our folder structure in Outlook resembles that in ( ). folders structure. 1. Create a subfolder form your Inbox called SYMN otifications 2. Go to the default Inbox folder and in the Outlook menu select Tools, Rules and Alerts. 3. Click the New rule button. 3. Select the Start from a blank rule option, then select the Check messages when they arrive option and click Next. 4. Check the Through the specified account option and click the specified link in the bottom field. 5. A dialog box including a list of all defined accounts will display. Select the account for this rule ( ). Personalization Tips (In MS Office & beyond ) Contact Us: (Page 7 of 9) (Last Edited: 04/13/2016) Selecting an email account used to receive messages. 6. Choose the Move it to the specified folder option and in the bottom window click the specified link.

10 In the folder browser indicate the folder to which the incoming messages will be moved. In this example it's the SYMN otifications folder you created in step 1 ( ). Click OK. Personalization Tips (In MS Office & beyond ) Contact Us: (Page 8 of 9) (Last Edited: 04/13/2016) Choosing the public folder where the messages will be redirected. 7. Click the Next button to go to the dialog window of exceptions. Here you may define the exceptions to which the rule will not be applied. 8. Click the Next button. The Summary dialog box will display in which you can also enter the name of the rule. 9. In order to save the entered rule, click the Finish button and then click OK. Tip 7. Other MS Office Tips 1. Outlook Calendar Multiple Time zones 2. Outlook LinkedIn Integration downloads your LinkedIn Contacts and keeps them current 3.