Menu Calendar Preview

I have been working, on and off, on a project that I think will appeal to those that like to plan out there meals.  My wife and I have come up with a menu calendar that will allow you to enter and look up your recipes, select them on any day of the month, get a list of ingredients you need for the month, and more.  Here is a sneak peek at what you will be getting.

Like I said, there are some great features and I haven’t mentioned them all.  I hope you enjoy this little preview.

More Charts

Here is a link to a tutorial that I thought was pretty cool. It shows you how to create a thermometer in Excel using a chart. It teaches you how to change the look of the chart to make it look more like at thermometer. I thought this was a great tutorial because it teaches you how to use the design features for charts. The tutorial and picture are brought to you by SpreadsheetShoppe.com.  Click on the image below to head right over to the tutorial.

Building a Grade Tracker

In the process of writing a quick post on my Facebook page last night I got some inspiration to do another project with all of you.  A spreadsheet is a great way to track data.  We don’t use every single column nor do we use every single row.  But, we can build some great things no matter how many rows or columns we use.  Today, I thought I would go through the process of building a grade tracker for you students.  You can make a spreadsheet for yourself or you can build a spreadsheet for an entire class.  In this tutorial we will focus on building a personal grade tracker.

First, let’s start by opening up Excel.  I will be using 2007, but you can do this in any version.  You can give your spreadsheet a header if you want.  We’ll name this one ‘My Grade Sheet’.  I will do this by selecting cells A1-G1 and merging the cells.  After selecting the cells I will go to the ‘Home’ tab and select the merge and center button.  After you do that you can type the name of your spreadsheet there.  Use the text editing tools to the right of the merge cells button to get the text to look the way you want it.

Once you have done that you can add titles to your assignments in the rows below.  There are usually three main types of assignments for a class: Homework, Projects, Tests.  So, let’s make three main sections.  Then we will add the number of projects under each main section.

Now it’s time to up our formulas in.  This might get a little complex but I will explain it to you.  First we want to put in the weight each assignment type has on the grade.  For example, all homework assignments have a total grade weight of 20%.

Now, we will make the formulas so this spreadsheet can start working for us.  We want to have each assignment reflect its individual weight on your grade.  So, next to each assignment we will divide the total section percentage by the number assignments in that section.  For example, there are 10 assignments in the ‘Homework’ section.  Divide 20% by 10 next to each assignment.  If you are wondering how and why the dollar signs are in the formula I pressed F4 after selecting cell C2.  This lets us copy and paste this formula into all the other homework cells so we don’t have to rewrite the formula.

Now that we have done that for all of our assignments it will look like this.  Make sure to change all of your cells to percentages by selecting all of them and clicking on the button with the “%” sign.

In columns E and J we are going to create another formula that lets us know how much weight our grade on that assignment is.  So, next to each assignment we are going to enter a formula that multiplies column C by column D and column H by column I.  For example, =C3*D3.  This is what we should get when we are done entering our formula in all the required cells.

Now, we are going to be using the sum function to get our totals for each section.  For example, type in cell C13 =SUM(C3:C12).  Copy and paste that formula in E13.  This is what your spreadsheet should look like when you are done with all sections.

Now we want to add all of our totals together.  To do this we will create a title called “Total” in cell G15.  Play around with the editing buttons on the homepage to get it to look the way you want.  After you do that you will enter a formula in cells H15 and J15.  For example, we will enter =C13+H6+H13 in cell H15.  Copy and paste this formula in cell J15.  This is what your spreadsheet will look like when you are done.

This spreadsheet helps you project what your grade will be by the end of the semester.  Just enter your assignment grades in columns D and I.  In the totals cells the grade is calculated automatically.  Here is an example of a projection.  I have used the home tab to play around with some formatting.  Most of the formatting was done using the buttons in the ‘Font’ group of the home tab.

You can format this spreadsheet to look however you want it.  This is a very basic spreadsheet build for me.  If you have any questions or comments please feel free to leave them here on this post and I will be happy to answer them.  Happy spreadsheeting everyone!

Writing a Macro

I have talked about Visual Basics for Applications in other posts.  I have shown some of my code.  I have also shown you how to record normal functions in your worksheet by using the Record Macro button at the bottom left corner of your Excel window.  I think we should “write” our own macro this time.  Let’s write a macro that posts data in the next row down every time we enter new data.

We will first start by opening our Visual Basic Editor by pressing Alt+F11.  We first want to name our macro.  Macros are like subroutines, they do an extra function created specifically for you.  So we first start out by typing in ‘Sub’, without the ‘ ‘ of course.  Then type a name right next to it.  Then, press ‘Enter’.  This is what your VBE space will look like now:

Sub MyFirstMacro()

End Sub

We will now want to write something in the space between ‘Sub’ and ‘End Sub’.  We want to declare the variable that we will be using to enter each line of data we type on a new line.  We do this by typing ‘Dim’, followed by the variable name.  We then want to define what kind of variable it is, so we will type ‘As’ and then the type.  Here is what it will look like.

Sub MyFirstMacro()
Dim row As Double

End Sub

When we call it ‘Double’ we are saying that the size of the variable ‘row’ can be really really big. The number of rows in Excel 2007 is 1,048,576. ‘Double’ will be enough to cover the last row in your worksheet.

After that you will tell the VBE what ‘row’ you want your macro to start on. Your macro will now look like this.

Sub MyFirstMacro()
Dim row As Double

row=5

End Sub

We want our data to post on a specific page. We also want to have our macro look for an empty row on that page. So, we are going to write a ‘Do Until’ loop to help us do that. We want a ‘Do Until’ loop because we want our macro to start on row 2 and keep looking at the next row ‘until’ there is an empty row.

Sub MyFirtMacro()
Dim row As Double

row=5
Do Until Sheets(“Sheet1″).Cells(row,1).Value = Empty
row = row + 1
Loop

End Sub

By writing row = row + 1 we are telling the macro to add 1 to the value of row for every row that is not empty.

Now the final part, getting your macro to post the data you just entered. We are going to make row 2 our data entry source. In cell A1 type in First Name and in cell B1 type Last Name. Go back to the VB Editor. We want to tell the macro to put the data we enter in cells A2 and B2 in the next empty row. So, here is how we do it.

Sub MyFirstMacro()
Dim row As Double

row = 5

Do Until Sheets(“Sheet1″).Cells(row,1).Value = Empty
row = row + 1
Loop

Sheets(“Sheet1″).Cells(row,1).Value = Range(“A2″)
Sheets(“Sheet1″).Cells(row,2).Value = Range(“B2″)

End Sub

Now, don’t forget to save your work. Close out of the VB editor and go to the Insert tab in the ribbon. Select a shape you would like to use to as a button. Add it to the page. Now right click on that shape and select ‘Assign Macro’. Select MyFirstMacro and click ‘OK’. Now enter a name in cells A2 and B2 and click on that image. Your macro should have placed that entry just below that entry. Enter another name and see what happens. If this does not work please feel free to comment and I will see if I can troubleshoot it for you. Happy spreadsheeting everyone!

Pivot Charts

Humans thrive on visual organization.  In other posts we shared some tools to help you do that in Excel.  One of the reasons we have these posts is to teach you to use Excel more efficiently.  There is a tool used a little less than others called pivot charts that helps with this visual organization.

Like pivot tables you can manipulate the way the data is presented with an easy-to-use drag and drop user interface.  It is a whole lot easier than going straight to Add Chart.  The main difference is that it changes your data into a pivot table.  You can select which data you want presented and the pivot chart automatically reflects what you have selected.  Let’s take a look at a video to learn how to use this awesome tool.

Feel free to check out the other videos he advertises in this video. This is a site that tries to recommend and use the best resources available on the internet. I hope you enjoyed this tutorial. Don’t forget to look at our other tutorials. Happy Spreadsheeting!

Conditional Formatting

It’s time for another amazing tutorial. In previous posts we have discussed a wide range of features available in Excel. It is time to jump into another great feature, conditional formatting. I am not talking about using formulas like ‘IF’. I am talking about giving the people using your spreadsheets more visual support.
I have a friend who owns a couple of small businesses. One of the spreadsheets he uses has conditional formatting. It’s a loan calculator that highlights the row showing the best loan option for the customer. It’s a pretty amazing spreadsheet that didn’t take a whole lot of time to create.
You can do a lot with conditional formatting to help point out important factors in whatever project you are working on. It saves you from constantly having to highlight and unhighlight cells manually, etc. Let’s look at how to build a spreadsheet using conditional formatting. Unfortunately, the author of the best video I found for this would not allow me to embed it on my site. So, I will give you the link to the video. http://www.youtube.com/watch?v=PpQ9d4cHVdk&feature=youtu.be
Here is another good video in the mean time.

I hope you found the information in this post very useful. I like conditional formatting because of its many useful applications. Check out more youtube videos to see what you can do with it. Happy spreadsheeting everyone!
Thanks to Microsoft for the image.

Another Project Completed

Hello Spreadsheet Wonders fans!

For the last few weeks I have been working on a project that had originally stumped me last year.  I took a different approach and this is what I have come up with.

TimeSheet Pro helps small businesses keep track of their employees hours.  All the employee has to do is clock in and clock out.  It is a very simple system to use.  Managers or business owners can easily keep track of employee information with the Add, Delete and Edit forms.  If your employee forgets to clock in or out you can take care of that too with the manager override feature.  Build a report for each individual working in your company with the reports building page. Just pick the employee and a date range and the worksheet does the rest.

Included in this post is a set of instructions.  You can view them before you buy TimeSheet Pro because everyone wants to know how it works before they buy it. so, here it is.  Instructions

If you like what you see then please purchase this amazing product at the SSWStore.

Want to buy this amazing product? Click on the ‘Purchase’ button below and get yours for $12.99. Happy Spreadsheeting everyone!
[button link="http://www.excelville.com/file/344/TimeSheet+Pro" size="medium" variation="blue" align="left"]Purchase[/button]

Pivot Tables

Most people in the corporate realm know what I am talking about when I say that pivot tables are incredibly valuable tools. In the time I have spent as an accountant I have used this tool probably the most out of all the tools available. It makes for very customizable reporting. In order to utilize this tool it takes a little bit of learning. It us unlike any tool you will normally use in Excel. Let’s take a look at this video to learn how to use it.

How many different ways did he show you how to report the data? A lot! It didn’t take a whole lot of time either. This is why people like accountants like this tool. If the manager doesn’t like the way the report is formatted you don’t have to spend a lot of time reformatting. Just move the fields around in the pivot table developer.
Businesses really like to see Excel experience on your resume, especially if you know how to build pivot tables. So, if you’re looking to build your resume then learn how to use this tool. Until next week, Happy Spreadsheeting!

Charts

Charts are a great way to give someone a visual representation of your data. Many people tend to have a better understanding of your data if it is presented in a chart or graph. If you are presenting a business plan to investors I find it to be a great idea to put in charts and graphs. Numbers make sense but when you show the growth of your income on a chart it is a lot easier to see the growth potential your company has. To learn more about business plans you can go to [fancy_link link="http://www.inc.com/tools/business-plan-outline.html" variation="blue"]Inc.com[/fancy_link]
or visit a nearby business development center. Let’s get to the video that explains charts. Feel free to copy what you see into a spreadsheet in Excel to “apply” what you are being taught.

Happy spreadsheeting everyone!

I Gotta Game For You

We here at Spreadsheet Wonders do our best to bring you the best tools available for learning spreadsheets. Sometimes our tutorials have videos that have people with exciting voices and music. Sometimes they have people with a kick back kind of personality and it is just a simple video. I’m sure some of you think it’s exciting and interesting no matter what style the video is presented in.
There have been a couple of posts that help you get familiar with the ribbon feature in Excel. However, I don’t think any of them are as exciting as the tool I am about to share with you. When I was told about a GAME that Microsoft developed for Office 2007 and 2010 I went nuts! Who knew you could have that much fun with office software?
Join Clippie on an amazing adventure while you learn the most used office software. Get the skills you need to give yourself the edge you need over the rest of the competition. And have FUN while you’re doing it.
Finally, here is the link where you can get your copy of this free game. [fancy_link link="http://www.ribbonhero.com/" variation="blue"]Ribbon Hero 2[/fancy_link]

Happy Spreadsheeting everyone!

From 2003 To 2007

We found a great tool for those who are used to Excel 2003 and are having a difficult time making the transition to 2007 and 2010. This awesome tool which has been provided by Microsoft is a great visual tool. Hover over any icon on the page and it will tell you where it is in 2007. Follow this link and it will take you right to the page.
Go to page

Basic Statistical Formulas

We are going to be looking at some of the basic statistical formulas that are available in Excel.  In order to do that we are going to do a review the SUM and COUNT functions in the video this week.  This video had the most simplified description of these functions.

The other functions we will demonstrating are AVERAGE, MEDIAN, MODE, STDEV, MIN and MAX.  These functions would be great for a teacher to use to get a quick glance at the performance of his or her class/classes. In each of these functions you will notice a commonality.  The only things you have to do is type “=”, the function and select the range you want to use.  Let’s take a look at these formulas in action.

In the next post we will learn about formating our spreadsheets so the look neat and organized.  Happy spreadsheeting!

SUM and COUNT

Last week we introduced you to Excel 2007. 2010 is not a whole lot different so these tutorials are pretty comparable. Now we will be looking into a couple of functions, SUM and COUNT. These are pretty simple and frequently used functions.
SUM allows you to add any range you want. You don’t have to enter things in like a calculator by using the “+” symbol. Just enter that formula in one cell, select the range you want to sum and there you go.
Count is also a great tool. You can use it to count the number of blank cells in a range, how many students got an “A” in your class, etc. The possibilities are only limited to the imagination.
These functions also have sister functions that work in a similar way. They require you to enter more specific criteria. They help you sum or count a specific set of data in the range instead of the whole range. They have a lot of practical use, especially in the business world.
Let’s take a look at some filmage to show you how these functions work.

I hope you find this useful. Next week we will be getting into a couple of other formulas. Until next time, Happy Spreadsheeting!

Intro To Excel

Last week I said we were going to launch some trainings.  These are really more like tutorials than trainings though.  So, to kick off our tutorials I would like to start out by introducing Excel to you. Most people are familiar with Excel 2003.  As you will find in my other posts I use Excel 2007.  Most businesses use the 2007 version or higher.  There are a lot of great things you can do in Excel.  Let’s get familiar with its basic features.

My recommendation to you after reading this post and watching the video is to play around in Excel.  Get used to its user interface.  Play around with its functions.  You can’t hurt anything by playing around in a regular workbook.

I hope you enjoyed this post.  Next week, we will be talking about some common functions that are used in Excel.  Have a great week and Happy Spreadsheeting everyone!

Prepare For The Trainings

One of the main reasons I started this blog was to open an online store featuring my products. I quickly got into haring little tutorials for those that had a pretty good understanding of Excel. Now I am working on a team to bring both of those ideas together. Get ready for Excel Your Office Experience by Spreadsheet Wonders.  Look for this logo to know you have the right trainings

 
This amazing set of trainings will teach you many of the important things you will need to know for the office. It will be taught by those with office experience, me and my team. We realize that there is a need for office employees to become more familiar with spreadsheet software. Employers need new employees with great spreadsheet skills. So, we want to help fill that need by providing awesome training videos and spreadsheets so that you can gain the advantage in the workforce and companies can increase efficiency and productivity with the right people.
Realize that this will take some time to put together. But, know that these trainings will be filled with extremely useful and easy-to-understand material. We’re going to have a lot of fun putting this together. Let the suspense begin.
Until then enjoy looking at some of the brief tutorials on this blog. Happy spreadsheeting everyone!

VLOOKUP Is My Friend

Do you know how I say, why build a macro when there is already a function built in excel for that?  Well, VLOOKUP is one of those functions.  It is a great query function.  I am actually using it in a project I am building right now.  All I have to do is fill in one cell and the rest of that spreadsheet is filled in with the data I need.

There are just 4 pieces of information you need to get this function to work properly.  You need the lookup value, range you want to lookup the value in, what column you want your result to come from, and if you want an exact or close to match.  Let’s take a look into this video to show you what I am talking about.  Some of the examples are a little funny but it gets the point across.

This function is great for when you are building an invoice template.  It has other great practical uses.  Like I said before spreadsheet apps are only limited by the imagination.  I hope you found this tutorial useful.  Until next time, happy spreadsheeting.

Building Your Code Library

While I work on the big projects I often get ideas for other projects in excel.  I noticed online that someone had built a very primitive workbook to store vba code.  I thought to myself, I could make this better and make it so people can build code right in the workbook.  So, this week I have been working on creating the SSW Code Builder Library.  Every new project I do I learn something new.  If you have been keeping up with the work that I do on this blog you will notice some things that are new on this one that aren’t on the others.  With Grocery Control Center it was the pop-up menus.  With this one it is being able to hide and show multiple form controls so I don’t have to create multiple pages for my forms.

I have to put a couple of finishing touches on it before I post it online, but here is how it works:

This is what the code library will look like when you open it.  If you already have saved code you can select any of the radio buttons to look at the categories of code you have saved.

When you click ‘Add’ this form will pop up.  Don’t forget to add a name and type.  Add your code and a description in the bottom boxes and click ‘Submit’.

You can now select the radio button for the type that the code is saved as.  Go to the drop-down men

You can highlight the code from here and insert it into your Visual Basic Editor.

Or, you can build code using any combination of codes that you have saved by selecting build code.  When you want to add the code you can click ‘Add Code’ and it will go into the Code Builder textbox.

Here is what the form looks like after you click the ‘Add Code’ button.  When you are done building you can highlight the text and put it in your Visual Basic Editor.  When you are done building you can click ‘Stop Build’ and those controls will go away.

If you need to edit or delete code you can click the ‘Edit Code’ button.  Select the code you want to edit/delete in the first drop-down menu and the other fields will automatically be populated.  Change the code type, code, and description to your liking and click ‘Submit’.

That is how it works.  It doesn’t have to be for code though.  You can use it for other things like building contracts.  Just let me know what suggestions you have and I can make the changes easily.  I had a lot of fun building this application this week.  I hope you all get some good use out of it.  Happy spreadsheeting everyone.  This will be posted on www.spreadsheetwonders.com soon.

Spreadsheet App Limits

I have come to learn more and more each day that spreadsheets are only limited to your imagination.  There are so many things that you can think to do.  And while you are working on those ideas you almost always learn something new that can be applied to other projects you have or will be working on. Let’s take my latest project as an example.

I wanted to make something that was geared towards people wanting to keep a better inventory of all there food.  they would be able to add, edit and change quantities of inventory.  The coolest feature this workbook has is that it will create a grocery list at the click of a button. Anyway, while working on that project I stumbled upon some code that creates a pop-up menu.  I thought it would be perfect for this project and other projects I am working on.  Go ahead and click on the image below.  It takes you to might official site.  Use the code GCC4ME when you go through the checkout process.

 

 

 

 

 

 

 

 

 

I have also geen trying to figure out how to use a pivot table to do some amazing financial statement stuff.  I plan on incorporating Visual Basics.  My ideas are pretty grand for my next project which has been hinted at in this paragraph.  I am supercharging my bookkeeping project I made in college and making some feature and design changes.

I can’t wait to learn more about spreadsheet stuff.  I hope you all enjoy this little bit of inspiration.  Happy spreadsheeting!

Linking Your Workbook

I have been working in a lot of spreadsheets that have all their pages linked to one another.  I have created quite a few of these spreadsheets for my job.  I created a workbook for communications analysis and sewer analysis for the city that I work for.  They use this concept of linking sheets in the workbook.  They have proven to be a very useful tool for analysis.  Because I have been working so much in workbooks like this it got me to thinking, why not talk about it on my blog.  So here we go.

Linking the pages in a workbook can save you a ton of time and makes it so you can find all of your material in one place.  For instance, if you are a business person making a proposal for a new piece of equipment for your company you know that all of your material relates to one piece or another.  Everything comes into play on the one spreadsheet your boss will be looking at.  Let’s take a look at a video explaining the awesome power of linking your pages in a workbook.

This is surprisingly easy to do compared to all of the other posts I have been talking about.  Like I have said in the past, you need to make spreadsheet programs work for you.  You do the initial set up in the beginning but it is clear sailing with how efficient the workbook works from then on.

I hope you enjoy this entry from me.  Happy spreadsheeting everyone!

Google Docs

I’ll bet that no one ever thought that I would talk about Google Docs because of the way I talk about Excel.  Google Docs is a great online platform with a lot to offer.  It even has it’s own macros developer.  I haven’t played around with it too much though.  I have played around with their online forms, which are great tracking tools.  You can even have the information people input on the forms put in a spreadsheet.  Let’s take a look at some of the things you can do.  These are tutorials I found because my recording software is buggy right now.

[youtube http://www.youtube.com/watch?v=YNfTCXBh9W0&w=420&h=315]

[youtube http://www.youtube.com/watch?v=B7KgxWMTZK0&w=560&h=315]

Google Docs has helped me out in a few cases in my life.  Which brings me to a very good point: don’t limit yourself on the office programs you know.

Take a chance and learn Google Docs on the fly sometime.  You will find that it has it’s uses for your personal and business life.  You can check out my form on interactive spreadsheets.  It’s not super great but it gives you an idea on what you can do with Google Docs.  Good luck and happy spreadsheeting.

User Forms

Its time for another post from Spreadsheet Wonders. As promised, this week we will be discussing user forms.  A user form is kind of like a pop-up window with data fields, buttons, drop-down fields and more.  All of this is used to make the data entry or spreadsheet building process easier.  Some spreadsheets may have need of a user form.  It all depends on the kind of application you are building.  A lot of accounting spreadsheets have good use for them.  When I was in college I developed an accounting spreadsheets system for a class assignment.  We developed three user forms that simplified the heck out of a lot of the bookkeeping processes.  The whole point in using these tools is to make spreadsheets work for you.

Here is a video that demonstrates what user forms can do for you.  It is the accounting project I mentioned.  I’ll even give you a peek at all the code that went into it.

Unfortunately, there is a lot of explanation behind building a user form and the VBA code behind it.  I might bore you or confuse you with my explanation and I want to keep this blog interesting.  My suggestion is to take a look at these sites:

http://www.excel-vba-easy.com/vba-userform-excel-vba.html

http://www.fontstuff.com/ebooks/free/fsUserForms.pdf

I hope you find this useful and that you continue down the road of spreadsheet wisdom.  Until next time, happy spreadsheeting!

Building Your Resume

Nothing helps you land a job better than competitive edge.  I am certain that is one of the reasons I landed my current job.  The rest was blessings from heaven.  My accounting experience was definitely good, but my spreadsheet knowledge definitely put me ahead of the rest of the competition.  If you hope to get that great office job then do all you can to gain as much experience in office software like Microsoft Office Suite and other programs as you can.  That is what companies are looking for.
Knowing how to work in spreadsheet software is not good enough.  Knowing how to make spreadsheets work for you and your company will make all the difference in the world.  That is what my computer professors in college kept stressing and it rings true in my life.
So, my advice to you is take advantage of the many user manuals and tutorials that are out there to help you learn to make spreadsheets work for you.  Take some courses at your local college.  Those professors have wealth of knowledge backed with real world training.  Some of the greatest things I have learned have also come from exploring the programs I work in.  I have accidentally come across some pretty awesome things that way.
Don’t forget, if you can’t figure out how to do it check and see if I already have a spreadsheet for that or ask me a question in the comment section on any page of this blog. I will be happy to answer.
Next week will be a discussion on userforms, one of the greatest tools in Excel.  Until then, happy spreadsheeting.

Data Validation

Data validation is another very useful tool available in many spreadsheet programs.  Since I am an Excel geek I will discuss its application there.  I want to talk more specifically about the list option in data validation.  In my TimeSheet example below you can see that the selected cell has a dropdown menu button.  This is the list option in data validation.  It makes it so all entries regarding the same item are uniform and it also makes it more time effiecient.  Of course most entries are bigger than the ones shown in my example.  This is a really useful tool to have especially if you don’t want to spend a bunch of time writing Visual Basics code.  Like I have stated in previous posts: why spend the time writing the code when there is already a tool in Excel that does it?

Let’s go through a step by step process to use the data validation list tool.  If you want to you can create a list of items you want to have show up in the dropdown list, like above.  What I did was make a list on the current sheet.  Then I hid the column it was on.  Anyway, click on the Data tab.  Then, click Data Validation.  A menu will pop up.  Go to the dropdown menu and select list.  Then, click on the icon next to the Source field.  Then, select the range of cells (your list) that you want to use.  Click on that icon again and then click OK.  You just created an dropdown list using data validation in Excel!  Congratulations!!!

You will probably spend some time developing a spreadsheet doing things to it like data validation but in the long run you will be saving yourself a lot of time if it is something you use frequently.  I hope this helps you in your quest to develop your spreadsheets.  Happy spreadsheeting everyone!

Online Interactive Spreadsheets

Nothing gives your audience a better taste of what your spreadsheets can do than putting an interactive one on your webpage.  Spreadsheet Wonders has been up for about a month now and has failed to deliver on this fact until now.  Yeah, it is pretty cool to ‘say’ you can build a great spreadsheet.  It’s even pretty cool to see that you have cool pictures of spreadsheets on your site.  It is pretty much awesome being able to put an interactive spreadsheet on your website. There are multiple sites that can help you make this happen.  You don’t even have to know how to write code.  You just have to know where to put it on your pages.  Like I said, there are multiple websites that can help you make your spreadsheet online interactive.  Google Docs is one of those places.  You can also use Microsoft Web Apps to hook your website up or even Zoho.com.  There is a plethera of ways to get it up there.  I have used all three.  All have there advantages and disadvantages.  In this case I will use Microsoft Web Apps as Microsoft Office is the software I use the most. So, here it is: the long awaited online interactive spreadsheet!  Hope this gives you a good taste of what Spreadsheet Wonders does before you make that crucial decision to download a Spreadsheet Wonders application.
https://r.office.microsoft.com/r/rlidExcelEmbed?su=6765399564575223141&Fi=SD5DE3870F3216AD65!109&ak=t%3d0%26s%3d0%26v%3d!AGE79Vw2F1ZUroI&kip=1&wdAllowInteractivity=True&AllowTyping=True&wdHideGridlines=True&wdHideHeaders=True

ContactManager 2.0 Lite

To show some versatility in what I can do in Excel I have developed a contact manager that is an add-in.  It is a tab in the Excel ribbon.  There are three forms to this tab: Add Contact, Find Contact and Edit Contact.  You will notice that there is a really simple way to search for your contacts.  If your contact has a website there is a dedicated button in the Find Contact form that will send you directly to their site.  It is pretty simple and was designed for those that spend most of their time in Excel, like me.  I hope you like what you see.  Click on the picture to download this add-in from Excelville.com.

 

 

 

 

 

 

 

After you download it from Excelville.com you will want to save it in a safe place.  Open Excel, click on Windows icon, click Excel Options, click Add-Ins.  Near the bottom of the list of installed addins is a drop down bar that says ‘Manage’ next to it.  Click the ‘Go’ button.   If ‘Contactmanager Addin is not available then click browse and find  the folder you saved the file in.  Add it to the list.  The last step is to check it on the list and and click ‘Ok’.  Your contact manager tab is now installed.  Whew!  That was long winded.

Visual Basics for Applications in Excel

Excel is a very useful tool. There are so many things that you can do with it. Automating your spreadsheets makes things a whole lot easier too. I have been able to automate quite a few spreadsheets without the use of Visual Basics for Applications (VBA). For some, the processes are so complex that it does require VBA.

Some of you might be thinking: what is VBA? VBA is code that is used to write macros which preform tasks in Excel or another Microsoft application. Depending on the spreadsheet that you are working with, sometimes you will see buttons on them. These buttons have code that execute tasks that would normally take much longer to perform. These are macros.

You can actually write it yourself with no prior knowledge of code writing. Down at the bottom left side of the window is an icon next to the word ‘Ready’. Click on that icon. Excel will then ask you what you want to name your macro. Name it and click OK. Perform whatever task you want to automate. When you are finished just click on the icon next to the word ‘Ready’ again. You can add a button on the page by going to the Developer Tab and clicking insert. You will be given a selection of objects to choose from. I would suggest choosing ActiveX command button. You will then need to click on Visual Basic in the Developer tab and pick the worksheet the button was added in. A window opens in the VBA module. Just above that are 2 dropdown bars. Click the left one and select the command button. In between the sub and end sub lines type in the name of your macro. Save your workbook as .xlsm file and you’re done! If this doesn’t make complete sense that’s ok. I thought about doing a video but I found an excellent tutorial on youtube.

I hope you found this useful.  Visual Basics is a really great tool to help increase efficiency.  Until next time, happy spreadsheeting everyone!

Tables

A table is an incredibly useful tool when putting together reports, or analysis of your data.  They are incredibly easy to put together, too.  All you have to do is enter your data on a spreadsheet in Excel, highlight the data, go to the insert tab and click on table (located on left side of ribbon).  There you go! You have your own table complete with filters to manipulate your data.  That is why you will find tables in many of my workbooks.  Why does anyone need to write a bunch of extra programming to create an Excel function that already exists?

Bill Pay Project

For the past week I have been working on a bill pay system.  I know there are a lot of bill pay systems out there and there are companies that provide the service for business.  I wanted to come up with something that I could use as an individual or in a small business setting.  I believe that it will be useful to others as well.
I have been putting in an hour or two here and there to put this thing together.  It is currently a work in progress.  There are still some things I need to work on but I know when I am done it will be a quality product.  I am using suggestions from other blogs that talk about color and design to bring you something that not functions properly but is also easy on the eyes.  
It is going to take a little while to complete this project but keep checking my blog weekly for posts.  Next week I will be discussing tables in Excel.  Because not only do I want to come up with spreadsheets for everyone, but I also want my subscribers to get the most out of their spreadsheet experience.
No sneak peeks yet but, like I said, keep checking the blog because there will be shortly.  Until then, happy spreadsheeting!

Contact Manager Project.

So, I am still working on my math tutor idea.  I wanted to let you all know that I am very close to finishing something else I’ve been working on. Contact Manager for Excel.  Here is a sneak peek at what one of the forms will look like.

There will be some features that I hope you find great and useful. Keep watching for progress on this and many more projects in the future.  Until then check out my other spreadsheets works of art.

Update 3/12/12

So, I completed the ContactManager.  It is a very simple application that I created in Excel.  This is the simple version so remember that.  I want to show to all you users out there how easy it is to put something together in Excel and manage the data you put in it.

What you will find quite easy to use is the tables that the contacts are stored in.  When you create a table in Excel each column has filters.  You can find your contact by any column.  Check out my video to get a quick tutorial on the tables and on ContactManager in general.

I had a fairly easy time putting it together.  Very simple cut and paste programming. I would say about 90% of my time was spent designing the forms and spreadsheets.  I try to get as symmetrical as possible when designing a lot of my workbooks.  There are some great features I want to add to this application in the future but until that time you all can enjoy this free and simple version.  Download it!

Welcome to Spreadsheet Wonders

Welcome to Spreadsheet Wonders! We are very excited to bring you a site that gives you useful spreadsheet tools. We offer mostly financial spreadsheets and tools, but are always looking for more suggestions. We hope you enjoy the tools offered here. They are reasonably priced and save you lots of time so you can do the important things in life.

MSQuery

I apologize for not making many posts in the last couple of months. I have been working at a manufacturing facility making spreadsheets and databases. Which is why I am putting together this post. I have made some great discoveries when it comes to querying a database. When working in your MRP or ERP system you have the opportunity to work with a lot of reports. However, those systems will not have all the reports you want. For that problem, Microsoft created MSQuery.

This is the most effective way to get the data you need and create reports from that data. I have been able to build all the extra reports my boss needs. Excel 2007 and 2010 may differ a little when it comes to communication with MSQuery, but here is a GREAT site that changed my life when it comes to Excel. Excel_MS_Query

When you get to the part where you can create parameter input boxes you will be even more amazed.  Not only can you go in and create a query from you database, butt you create input boxes in MSQuery so you don’t have to go back into MSQuery to change your parameters.  You can set them when you refresh your query table in your spreadsheet.  You can go even further than that though.  But, that will have to wait til next week so I can create a special tutorial for that.  Until next time remember to look at this website.  Excel_MS_Query.  Happy spreadsheeting everyone

Using Conditional Formatting To Create Charts

A discussion on Gantt charts came up the other day. First, let’s talk about what a Gantt chart is. The ever so favorite Wikipedia says that, “A Gantt chart is a type of bar chart, developed by Henry Gantt, that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project. Terminal elements and summary elements comprise the work breakdown structure of the project.” Hopefully this makes sense to you.
After this discussion I did a little research on the good old YouTube to get some knowledge on developing them in Excel. What I found were a couple of different ways to create Gantt charts. Here is a video on creating Gantt charts using conditional formatting. This one is brought to you from the YouTube user ExcelIsFun. This is definitely one of the best tutorials on conditional formatting I’ve seen so far.

Upon discovering this tutorial I also discovered how I was going to take on a project that I have recently been thinking about. So, get ready for a fun spreadsheet in the near future. I think you will all enjoy it. Until next time, Happy Spreadsheeting!

VLOOKUP On Steroids

I found this tutorial posted on LinkedIn.com.  If you are an advanced user and you have come across a complex issue such as the one explained in this tutorial then use this solution.  I found it to be fascinating.  Here is a sneak-peek from www.myonlinetraininghub.com.  Thank you Mynda Treacy for maki g this tutorial available.

“This week I had a question from Diedre asking if she can use VLOOKUP to check multiple sheets…. 17 different sheets in fact.
The idea being that if VLOOKUP doesn’t find a match on the first sheet, it will check the next sheet and so on.
The good news is we can, the bad news is it’s a bit complicated….but if you’ve only got a few sheets I‘ll show you an easier formula at the end.
Below is our table that we want to populate by looking up the Product Code in column A and return the Product Description and Price.” Read more…

I hope you enjoyed this tutorial.  Don’t forget, if you need help building a spreadsheet my services are always available.  As you can tell I like to keep myself up-to-date on this stuff. Happy Spreadsheeting everyone!

Nested Excel Functions

Here is another cool tutorial I found on spreadsheets.about.com.  It tells you about nesting excel functions inside of other excel functions.  They use several pages to explain how to do this.  One thing I have not come across until now is the fact that you can have up to 64 nested if functions in one cell of your spreadsheet.  That is a spreadsheet wonder!

Please make sure you follow all the links at the end of this page (spreadsheets.about.com).  These pages show you how to set up all of your nesting.  This can apply to other functions as well.  The IF statement is the best example to use to describe nesting.  I hope you enjoy this new tutorial.

If you find anything related to spreadsheets in your online search please feel free to either post a comment about it or join the spreadsheet network as a specialist or expert and start sharing your own content.  Remember to give credit where credit is due.

(tutorial and image supplied by Ted French and About.com)

220 Excel Tips

null

Here is something I came across in my browsing for more tutorial ideas. This guy has gone out of his way to make tons of tips for people to become awesome in Excel. Some of the tips are formulas that are already explained in other Spreadsheet Wonders tutorials. But, I think this is a very resourceful site worth mentioning. Here is the link.. Thanks to chandoo.org for the resources.
[fancy_link link="http://chandoo.org/wp/2011/01/20/220-ms-excel-tips-resources/" variation="blue" target="blank"]220 EXCEL TIPS[/fancy_link]

New Free Spreadsheet

While working on the Menu Calendar I thought it would be a good idea to create a calendar template.  This is a real download-able preview of the Menu Calendar.  The great thing about this spreadsheet is that you don’t have to worry about what day of the week the first day of each month starts on.  All you have to do is choose a month and year and the spreadsheet does the rest.  Then, you can put anything you want in each day.  This is a 20 year calendar starting with November 2012 and ends with December 2032.

[button link="http://spreadsheetwonders.com/wp-content/uploads/2012/11/Calendar-Template.xlsx" size="large" variation="deepblue" bgColor="#000000" textColor="#fafafa" align="left"]Download[/button]