How to create a custom balance Sheet in Excel 2007
http://www.youtube.com/watch?v=kRYUHKfUjkw&hl=en
Showing posts with label Create. Show all posts
Showing posts with label Create. Show all posts
Create a timeline in Microsoft Excel
Show your students how events relate over time by laying out the information along a timeline. Timelines can tell stories, show progress, support themes and ideas, summarize details, and help students visualize trends and understand the big picture. Step-by-step tips here: www.microsoft.com
http://www.youtube.com/watch?v=qoKi6VeIRHQ&hl=en
http://www.youtube.com/watch?v=qoKi6VeIRHQ&hl=en
Create Excel Dashboard to Summarize Key Data
When you create an Excel workbook to share information with co-workers, or company management, you can add a dashboard to summarize the data. Excel files can have many sheets full of data, which is too much to absorb by simply reading through it.
A file can contain several years of sales data, or thousands of survey results. Even if you create charts from the data, people might still have trouble wading through the sheets, looking for the specific data and charts that they need.
To make the vast sea of data easier to understand, you can create an Excel dashboard sheet, and include a few key numbers, small charts to summarize the data, and formatting to highlight the positive and negative results.
For example, an Excel dashboard could display key revenue numbers, with a small line chart of the past year's data. At a glance, anyone reading your Excel dashboard can see how things are going, without digging through years of detailed data.
Link to Key Data
To pull key numbers to the Excel dashboard, you can use links. For example, at the top of a worksheet named SalesData, you have a formula that calculates the total amount for the sales in column D. The total is in cell F2, and the formula in that cell is =SUM(D:D)
On the Excel dashboard sheet, you can use an IF formula, to show the sales total, and to make the cell appear empty if there's no total on the SalesData sheet. In this example, the formula in cell D4 on the dashboard worksheet is:
=IF(SalesData!F2="","",SalesData!F2)
Label the Key Data
When you pull key numbers to the Excel dashboard, it's very important to label all the numbers clearly. To create the labels, you can manually type a label in the cell next to a key number. Or, if the source sheet has clear, concise labels, you can use formulas to link to those labels.
Add Simple Charts
The goal of adding an Excel dashboard is to make the data easy to understand. So, when you add charts to the dashboard, keep the charts simple. Show one set of numbers per chart, such as total quarterly sales for the previous four quarters.
Use a simple line chart, with only the key features, and minimal formatting. The ideal charts would each have a single line, with a limited number of labels on each axis. There should be nothing else to distract the reader, to ensure that the message is clear in each chart.
Make the Charts Small
When you make the charts on the Excel dashboard very clear and simple, you can reduce their size. The information will be easy to understand, even though the charts can be reduced to only about an inch wide. With small charts, clearly labeled and titled, you can arrange many charts on the Excel dashboard, without overwhelming the reader.
Highlight the Good News and the Bad News
With conditional formatting, you can use color to highlight numbers on the Excel dashboard that are well below or above average. For example, you could highlight a cell if it contains an amount greater than 5000. To add conditional formatting in Excel 2007:
Select the cell that you want to highlight
On the Ribbon, click the Home tab, then click Conditional Formatting
Click Highlight Cells Rules, then click Greater Than.
This will open a dialog box, where you can set the minimum amount (5000 in this example), and select the formatting that will highlight the cell. You could format the cell with a bright red fill color if it's over the set amount.
Keep It Simple
The most important thing to remember when creating an Excel dashboard is to keep it simple. Select only the most important data to display, and use one or two color to highlight the top and bottom results. Small, simple charts will add visual impact, and help clarify the data.
Access Tip - Create A User Defined Filter Or Search Form
Many times it is necessary to provide your users with an easy way to filter or search for data in large table of information. This tip will show you one quick and easy way to solve the problem by giving the user a field to enter a search string and updating the form with those fields that fit the search pattern. It's quick and easy, so give it a try.
For this example I used a table of keywords and how often they were found on the internet in the last month. You can use any table you want, just make sure you adjust the code to reflect the right fields in the SQL string. So, if in this example I wanted to know how many times "Access" was used in a search string I can enter Access in the search field and the form shows me all the search strings containing the field "access" and then totals it for me.
Preparation
Create a table named "keywords" with these fields and settings: kwid - autonumber kwcount - number kword - text
Note: you can use any field names you wish, just adjust the code below accordingly.
1. Create a new form using the "keywords" table as the data source.
2. Put all the fields from the table on the form.
3. Arrange them all in a line and add a form detail and set the form type to continuous. I like to put the field headers above the fields in the form header section and place the fields in the detail section.
4. Next create an unbound text box and name it "srch" by adding a text field with no data source.
5. Define the after update event so that after your user enters a string in the srch field the form redisplays with those records that have that string in it.
Private Sub srch_AfterUpdate()
Dim srchstrng As String
srchstrng = Me.srch.Value
sql = " SELECT Keywords.kwid, Keywords.kwcount, Keywords.kword FROM Keywords WHERE (((Keywords.kword) Like '*" & srchstrng & "*'));"
Form.RecordSource = sql
DoCmd.RunCommand acCmdRefreshPage
End Sub
6. To get a total of the count of the keywords you need to add another unbound box in the form footer and make it's data source:
=Sum([kwcount])
Save the form and give it a try.
Access Application Idea: License Compliance solved with Access Database
This customer has to track as many as fifteen different medical licenses and credential verifications for over 3000 employees in the home health care field. They can not have an employee attend to a patient that does not have the correct licenses and credentials due to safety regulations enforced by the state. These licenses all have to be renewed and they have different periods and start dates. The only way that they are able to enforce the license renewals is through payroll. Another words, get your licenses renewals up to date or we can no longer send you to our patients. With payroll weekly, this became a huge problem just tracking the expiration dates and sending out notifications so that they would not loose good employees. This process previously required an average of twenty man hours per week to complete and due to the rush often had mistakes allowing unlicensed and or uncredentialed care givers visit patients. Our customer could have been censored or even lost their license over a violation like this, so they had to have a better way of tracking compliance.
Solution An automated human resources license tracking tool built with MS Access that checks license renewal dates for all licenses and credentials that every care giver employee is required to have in order to see patients. The tool polls the employee data each payroll and checks the renewal dates for all licenses listed in the employee user profile and generates a letter that goes to the employee designating which licenses are nearing renewal dates and or are expired. The system also flags all employees with expired licenses and prevents them from being scheduled for patient care for the specific license if needed.
Benefits The license tracking process now requires only a few minutes per week to complete. The company has virtually eliminated problems with license compliance. Employees license compliance has gone to near 100% and those employees that don't have licenses are no longer sent to the wrong patients.
Feel free as well to let us know if this is helpful or to send ideas for the types of subjects you would like to see covered in the future.
How to Create Video Using PowerPoint - Part 2
In part one of "How to Make a Video Using PowerPoint", we covered the functional aspects of creating a video using PowerPoint and Windows Movie Maker. Today we'll cover the creative aspects of adding transitions, effects, and music to your video.
So we now have our pages on the storyboard in the order we wish them to appear.
Make sure that your "Task" pane is selected so that the menu appears on the left side of your screen
Click on the "View Video Effects" link in section 2 of the menu and you'll see all of the possible options which include;
Antiquing
Fading
Speeding up or slowing down
Rotating pages
You'll see a star appear on the lower left hand corner of the slide. This will confirm your effect is in place. Do this for each slide in your video.
You'll notice a box with a chevron pointing to the right between each slide. Drag your transition choice from the box of options and drop it into this box. I recommend testing each transition as you add them to your video to see its effect.
Now, let the music play!
Find the song you want to accompany your video and save it on your PC in your "My Music" folder.
There are many places to find music on the internet such as ITunes, Emusic, and Rhapsody. However, I recently came across a blog article written by Buttermouth entitled 10 Legal Ways to Find Free Music on the Internet. If you search for the title + Buttermouth I'm sure you'll find it.
The music you will download will most likely be in MP3 format. However, Daniusoft offers an awesome free MP3 to.WAV music converter at http://www.wmatomp3-converter.com/mp3-wav-converter.html
Once you have downloaded and converted your music, return to the menu on the left, select "Import Audio or Music", and set the path to return to the location of your saved converted file, and import it into your video.The file, which will have a music symbol, will show up as the first item in your collection.
In order to add the newly imported file to your video you must first click the "Show Timeline" button on the white bar direftly above your story board.Once you've switched the view you can then drag and drop your music into the middle "Audio/Music" area.Based on the number of slides you have and the length of your music track you can then manipulate the timing between each slide transition and the length of time each slide remain for viewing to synchronize the music to the video.Stay tuned for part 3 of the series that will go into synchronizing the music in more detail as well as putting the finishing touches on your video.
As always, if you have any questions or need real-time support, please feel free to reach out.
Subscribe to:
Posts (Atom)
