Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased, or a down triangle if it decreased ... and stagger the markers for even greater grasp.

This lesson also covers how to handle non-American date formats, and optimize performance with a subquery.

If you like this video, please Like, Share, and Comment ~ thank you

1. Make a new query based on a table (MyData) with a date (TheDate) and a value (Price)

   - add date and value fields to the grid
   - sort in decending order by date

2. Add another copy of the table to the query

   - Access will '_1' to the end of the name of the copy at the top of the fieldlist to make it unique.
   - This table will represent the record for 'yesterday',  or whenever the previous value was recorded.
   - add date and value fields to the grid and give them aliases (for instance, PrevDate and PrevPrice) since field names have to be unique

3. Create a calculated field to show the difference

   - for instance --> Diff: CCur( MyData.Price - MyData_1.Price )
   - between a value in the reference record and the previous record
   - Wrap with function to convert to currency to ensure the result is the correct data type
   - the calculated field name (alias) is 'Diff' since it appears before the colon

4. Create a calculated field to show the Unicode symbol corresponding to a Circle or Triangle to graphically represent the difference

…

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formatting, chart type, titles and legend.

"A picture is worth a thousand words"

1. Make a query to show what you want on the X-axis (Category) and Y-axis/es (Values)

2. Create a Chart object on a form or report using the Chart tool

3. Follow wizard steps, but don't worry about the data

4. Set the RowSource for the chart object to be the data that you want

5. Resize and Format the chart -- and set/change other properties such as text for Title(s)

6. To progammatically modify the chart, watch the next video in this series, 'Manipulate Graphs in Microsoft Access using VBA'.

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final polish on the report, rename it, and add it to a menu form. Download the START and SOLUTION sample databases.

1. Download the START and SOLUTION sample databases (zip file)

SimpleGroupedReport_START_SOLUTION_D.zip

2. Open the START sample database

3. Create a Query to Line Up Data for Report

4. Use the Report Wizard to create a Grouped Report

Source is the query created in step 3

5. Group by year, month, day, then product category

6. Sort by product name then descending amount

7. Specify the amount to be summed

8. Choose Outline layout

9. Modify the Report Design

Add, delete, resize and move controls; show and use different report sections; spacing and boundaries; calculated controls;  formatting and properties; Report View and Print Preview.

10. Compare what you did to the SOLUTION database

This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When an Office module, such as MS Word, gives us the red squiggly underline signifying a misspelled word, yet it really is spelled correctly (such as a proper noun), we're all familiar with how to add it to the custom dictionary, that is, simply right-click the word and select Add to Dictionary. But suppose we type in a word that does not get the red squiggly underline, that is, Word thinks it is spelled correctly, yet we do not like that spelling, and we want Word (and other Office apps) to flag it as a misspelling. This video explains a Microsoft supported technique for achieving that.

1. Locate the ExcludeDictionary file

Exit all Office apps.

Using Windows/File Explorer (or whatever file manager you prefer), navigate to this file:

c:\Users\<username>\AppData\Roaming\Microsoft\UProof\ExcludeDictionaryEN0409.lex

Of course, <username> is your user name (in my video, it is Joe). The exact name of the file will vary depending on your language and Office version, but it will begin with ExcludeDictionary and have a LEX file extension.

2. Open the ExcludeDictionary file

Using Notepad (or whatever plain text editor you prefer), open the ExcludeDictionary file, which will be empty the first time you open it. If you do a File>Open, make sure that All Files is selected, since it is a LEX…

This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values where it is blank can be run.

This lesson shows the manual process to fill blanks, and the VBA code to implement it.

This is Part 2 in a 3-part series to discuss error handling in Excel.

Part 3 of this series will add errors to the code and then show how to fix them.  Part 1 of this Experts Exchange series suggested basic error handling code.

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Dimension Variables

   Declare variable names and data types that will be used in this procedure.

03. Initialize Variables

   Initialize the values of variables that will be used in this procedure.

04. Calculate Variables

   Calculate the values of variables that be determined.

05. Give User a Chance to Back Out

   Issue a message box to the user and allow them to stop the process.

06. Determine the Last Row

   Determine the last row on the worksheet that needs to be written to.

07. Loop through each Row of Data and Save or Write Value

   Loop through each row and either save the value that is there, or write the last value saved if a value is needed.

08. Continue Looping until Done

   Continue looping until all values in the specified column are written.

09. Exit Code

   After the procedure code, a line label for the exit code (Proc_Exit: ) is used to signify what happens at the end of the procedure. This is code to gracefully exit.

10. Error Handling Code

…

This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.

This lesson, Part 1, is the basics.  Whether you are writing VBA for Excel, Access, Word, or another Microsoft Office application,  basic error handling is the same.

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Exit Code

   After whatever your procedure does, a line label for the exit code (such as Proc_Exit: ) is used to signify what happens at the end of the procedure. This can be code to cleanup object variables, or simply code to gracefully exit.

03. Error Handling Code

   After the exit code, a line label for the error handling code (such as Proc_Err: ) is used to begin what happens if there is an error.
Books_START_ErrorHandling_CopyDownB.xlsm
Books_ErrorHandling_CopyDownBlanks_.xlsm

Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Personal. However, only one of them offers a free trial — Office 365 Home. This Experts Exchange video Micro Tutorial explains how to go through the process of obtaining the free, one-month trial for Office 365 Home, which includes the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. In a subsequent EE video Micro Tutorial, I show how to install the downloaded desktop versions of those Office 2016 modules in a Windows 7 system.

1. Visit the website for Office 365 Home

Visit the site with the only Office 365 edition that currently offers a free trial:
https://products.office.com/en-us/compare-microsoft-office-products

2. Request free trial

Click the "Try for free" button.

Click the "Try 1-month free" button.

3. Sign into your Microsoft account

Enter your email or phone for your Microsoft account, your password, and click the "Sign in" button.

4. Go through the payment process

Even though it is a free trial, you must provide a payment method and go through the payment process. So be prepared with a credit/debit card or a bank account or PayPal. If you are unwilling to provide a payment method, you cannot get the free trial.

5. Go through the install process

…

Microsoft Office Picture Manager is not included in Office 2013. This comes as quite a surprise to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This video explains how to correct this serious omission by the folks in Redmond and install (for free!) Microsoft Office Picture Manager 2010, which plays very nicely with Office 2013. This video Micro Tutorial is fully documented in my Experts Exchange article, How to Install Microsoft Office Picture Manager in Office 2013.

1. Determine the bit-level of your Office 2013.

Open any Word document (a new, blank one is fine).

To see if you have the 32-bit or 64-bit version of Office 2013, click the File menu, then Account, then About Word.

2. Download the Microsoft SharePoint Designer.

Download the matching bit-level for your Office 2013 from one of these links:

32-bit
http://www.microsoft.com/en-us/download/details.aspx?id=16573

64-bit
http://www.microsoft.com/en-us/download/details.aspx?id=24309

3. Run the downloaded SharePoint installer.

Using Windows/File Explorer (or whatever file manager you prefer), execute the downloaded installer and on the opening screen click the Customize button.

4. Mark all three program sections as Not Available.

Click the drop-down on Microsoft SharePoint Designer and select Not Available.

Click the drop-down on Office Shared Features and select Not Available.

Click the drop-down on Office Tools and select Not Available.

5. Select Picture Manager to install.

Click the plus sign for Office Tools to expand it.

Click the drop-down on Microsoft Office Picture Manager and select …

This Micro Tutorial will demonstrate how to analyze your website's back links using a network graph powered by a fusion table.

Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents.

01. Type out your initial header

02. Apply the Heading 1 style to it

03. Type out a secondary header

04. Apply the Heading 2 style to it

05. Type out a tertiary header

06. Apply the Heading 3 style to it

07. Generate your table of contents

Based on the headers you created. Place your cursor in the location you want the table of contents to be inserted

08. Select References from the toolbar

09. Select Table of Contents

10. Select Automatic Table 1

11. Done

Your Table of Contents will be inputted automatically using the heading styles you previously applied!

The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint.

1. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide

2. Create a blank slide in PowerPoint by going to File – new-then click create slide

3. Delete the text boxes (just easier to see what is going on)

4. You can insert the background anytime, but you may wish to do it at the beginning so you can see how everything looks together

5. The title comes next. You can put in word art here or use the smart object later.

6. Choose one of the smart objects to insert and use. In this case I am using one of the ones with pictures (although this does require you to make 2 links for every presentation.

7. Depending on how many speakers there will be, you may need to increase the number of boxes shown by pressing enter to get additional entries and/or by duplicating the object.

8. IMPORTANT: duplicate the object before making any further changes

9. Once you have the right number of objects, go back to the first object and enter the data you want there, including pictures

10. Note that these boxes will auto resize the text to get it to fit. So be aware that in some cases it may not be readable. You will either need to fix the box size or the title

11. To make the links and have them work correctly, you will need to ungroup each object twice. The first ungrouping, ungroups them so they are no longer smart objects, the second one separates them from each other. You may wish to regroup at least the picture with the text AFTER you have completed the hyperlinking.

12. To create a hyperlink, right click on an ungrouped object, and choose Hyperlink. To link to another presentation you should put the presentation in the same directory as the “launch” slide and then in the dialog box choose it as an “Existing file or Webpage” then click Okay.

13. To make this show a panel discussion I am going to delete the bottom three pictures and the bottom 2 objects in group three. I will then resize the object I didn’t delete so it aligns with the bottom of the other groups.

14. I have several objects that won’t be linked. I will change the coloring of those to make it more obvious. Also the top four going across will not actually give talks – they are session leaders and are there only to introduce the others. Therefore I will change the colors on those as well and separate them to make it more obvious.

15. Finally I will change the master slide (go to view – slide master) to include our logos. You could also include the background here.

16. I’ll move the objects a little to accommodate the logos

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculate the 5% Value at Risk of the investment from the results.

1. Modeling a Simple Investment: Type in the mean and standard deviation of possible returns

2. Label column A “Return” in cell A5

3. Enter =EXP(NORMINV(RAND(),$B$2,$B$3) into cell A6 and copy down to cell A15

4. Enter 1 into cell B5 to represent $1 invested

5. Enter =B5*A6 into cell B6 and copy down to cell B15

6. Enter =B15 into cell B20

7. Select A20:B519

8. Click Tools > SimTools > Simulation Table

9. Enter 1000 into cell B17 and label “Initial”

10. Enter =$B$17*B21 into cell C21 and copy down to cell C519

11. Enter =PERCENTILE(B20:B519,0.05) into cell B18 and label 5% VaR

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilities and outcomes with the =STDEVPR command.

1. Discrete Random Variables: Type in the set of outcomes and corresponding probabilities in cells A2:B6

2. Enter =SUM(B2:B6) into cell B7

3. Enter =DISCRINV(RAND(),A2:A6,B2:B6) into cell B9 and label “Profit”

4. Enter =B9 into cell B14

5. Select cells A14:B213

6. Click Tools > SimTools > Simulation Table

7. Enter =AVERAGE(B14:B213) into cell B11 and label “Mean”

8. Enter =STDEV(B14:B213) into cell B12 and label “StDev”

9. Enter =STDEVPR(A2:A6,B2:B6) into cell C12