Welcome to JobShuk. This is your first post. Edit or delete it, then start blogging!
Microsoft Excel demonstration – now available on-line, free
As per my post on February 23rd, here are a couple of links to an Excel-based client record keeping demonstration:
To view it through the Easy Data Solutions website: http://www.easydatasolutions.co.il/demos.htm and select the "Client information database" demonstration.
To view it through the JobShuk website: http://jobshuk.com/uploads/assets/easydatasolutions/ClientRecordsDemo.xls
2 Important Notes:
1) It is probably best to save the file to your computer first. If you would prefer the file sent to your email, feel free to email me and I will do so.
2) To view this demonstration you must set your macros security settings in Excel to “Medium”. This only takes a few seconds. Steps to do so:
-
Open Microsoft Excel
-
On menu bar click “Tools”
-
Select “Macros”
-
Select “Security”
-
Select “Medium” and press “OK”
-
Close Excel and then open the file through one of the above links
-
When prompted, select “Enable Macros”
Please note that this is a demonstration with limited functionality and is intended simply to give you an idea of what is possible. It is an example of how I would potentially use the design for managing clients for my own business, Easy Data Solutions. Try imagining how it could be useful for your business and what features you would incorporate. The design is very flexible, anything is possible!
Known Issues:
-
In this demonstration you can only enter one record per client.
-
Reports generated are very basic. Much more sophisticated reports can be designed, as well as statistics and analysis.
Enjoy! If you would like to discuss how to use this in your business, feel free to contact me at ra**@******************co.il to arrange a free, no commitment consultation.
Free Excel Demonstration – You can harness the power!
Over the past year or so I have posted a few blogs every now-and-then about the importance of understanding the information generated by your business. I have also posted some handy tips on using various Microsoft Excel functions to assist you in understanding and manipulating your business data. Obviously, Excel is a great tool for managing data and when used efficiently and correctly, it can give you greater insight into your business on all different levels
The average Excel user is capable of designing basic spreadsheets with various functions and calculations. But Excel is much much more than a giant calculator. It is a powerful program that can do a lot more than most people realise and becomes even more powerful when it is coupled with the functionality that Visual Basics for Applications (VBA) provides.
Not many non-programmers know what VBA is in relation to Microsoft Excel. In short, VBA allows you to write code that sits at “the back” of Excel and integrates into “the front” of Excel, that is, the spreadsheet you see on your screen. VBA is so powerful that it can turn your Excel spreadsheet from a screen full of grid-lines, cells and awkward calculations into a professional computer program with buttons, drop-down lists, check boxes and much more.
I have recently designed a template using VBA and Excel which allows you to enter client information into a form, stores the information in hidden sheets, recalls the data when prompted and generates client reports. I guess it is a bit like a database with Excel functionality. The beauty about this program is that it is adaptable to almost any business you could imagine. It is totally customizable. I am currently using the foundations of this program to design a model for a prestigious health clinic in Jerusalem, who need to manage patient details, medical histories and operation schedules.
Whether you are a health professional running a clinic, an accountant, a business consultant, or a personal trainer, the program can be tailored to your needs, exactly to your specifications. And, you don’t need to buy any CDs or install any software to run it. If you have Microsoft Excel (preferably version 2003 or later), you can run it!
If you would like to see a FREE SAMPLE of this Excel program to get an idea of what it can do, please feel free to contact me at ra**@******************co.il and put "JobShuk" in the subject line.
It is important to note that this sample is a very very basic demonstration designed simply to give you an idea of what is possible. It is in no way a complete program. It is just "the tip of the iceberg". It is your business requirements and your imagination that will allow us to design the perfect Excel based program for you. The sky is the limit!
If, after viewing the demonstration, it strikes you as something you would like to integrate into your business, I will be more than happy to organise a free consultation with you to see how we can adapt it to meet your needs. Looking forward to hearing from you!
For more information about Easy Data Solutions, visit www.easydatasolutions.co.il or email in**@******************co.il.
Excel Tips #6: Macros – Taking spreadsheet customization to the next level
This blog was authored by Y. Karp.
Not knowing how to program in VBA (Visual Basic for Applications) should not stop you from adding some “whiz-bangery” to your Excel spreadsheets. Out of all of the non-core Excel features, the macro recorder is one of the most handy.
A macro is just a fancy term for a series of steps or a sequence expressed as VBA code in the “back” of Excel (for example, go to worksheet 2, select cell C24, underline the word in that cell, italicize it and color it red). The macro (or code) can be five lines or five hundred lines, depending on what operations you need Excel to perform for you. But using the macro recorder means you never even have to look at the VBA code and can still design your own macros.
You don’t need to be a master programmer or Excel expert to use this feature. Fire up the macro recorder from the “Tools” drop-down on the menu-bar and Excel will write its own code as you go along. Every action you take whilst the recorder is on is noted by VBA. You can then stop the recorder when you have completed your series of actions and save the macro under a meaningful name. Each time you call upon that macro, the recorded sequence is “played back”, repeating the recorded steps to perform the operations you have designed.
Macros are useful for all sorts of things, from simple navigation in a workbook to performing complex procedures. If you are a more advanced user or feeling a bit adventurous, you can manipulate the underlying code in the VBA editor to tweak the macro so that it works just right. This is also a good way of teaching yourself the basics of VBA and understanding how it works.
Like many other technologies, macros can be used by hackers for nefarious purposes. Your Excel security settings should be set appropriately to protect you from damaging macros that may be contained in Excel files you receive from unknown sources. But if your security is set to “High” you will not be able to run your own macros when opening your own files. I apply the “Medium” macro security setting which alerts me to the fact a spreadsheet contains macros and then gives me the option to run them (enable macros) or not (disable macros).
Macros can really add a new dimension to your spreadsheet. Automate functions, zip from sheet to sheet, run all sorts of calculations at the click of a button – it can all be done. Harness the power of macros and get Excel to work for you.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models.
Website: www.easydatasolutions.co.il
Email: in**@******************co.il
Excel Tips #5: Freeze, split and hide
This blog was authored by Y. Karp.
Excel 2003 is limited to 65,536 rows and 256 columns. That might sound like a lot, but there are often situations where a spreadsheet you have designed can go right up to the limit. In fact, Microsoft increased these limits in Excel 2007 (1,048,576 rows and 16,384 columns).
For most of us, 65,536 rows and 256 columns may be more than enough. However, it is very common that your spreadsheet will extend beyond your screen real-estate, requiring you to scroll along or down the worksheet to view the data that initially appears off the page. When you do this, you can no longer see column or row headings, which can really make it difficult to know what you are looking at.
The answer: Freeze, Split and Hide (sounds like a law firm, doesn’t it?)
Freeze: Freezing an area of the worksheet allows you to view data on one part of the worksheet while scrolling through the rest of the worksheet. For example, if the top of your spreadsheet contains column headings, freeze Row 1 so that as you scroll down the sheet, Row 1 remains visible. You can do the same for row headings by freezing columns. You can freeze one row or column or multiple rows or columns. For instance, you can make sure that as you scroll through your worksheet, columns A:C always remains visible. You can also freeze both a row and a column so that they always show as you scroll up and down or left and right.
Split: Splitting a worksheet allows you to to work on one part of the spreadsheet whilst still viewing a second part of the spreadsheet. Let’s say that you want to view columns AX:AZ while working on columns CP:CS: You can split the worksheet into two so that separate scroll bars appear below the two parts, as if they are in separate windows. The term “split” is not entirely accurate because you don’t create two spreadsheets from one. You only create a duplicate view. Think of it like a split-screen on your television, showing opposite ends of the same football field. As players on both screens run towards the center, the two sides of the screen end up showing the same picture because it is the same football field. So if you split the spreadsheet, you can still scroll along the entire length and breadth of the sheet on either of the split areas. Any changes made in one will immediately effect changes made in the other because they are really the same spreadsheet.
Hide: You can also hide columns to make them sort of invisible, although they are really still there. You will know that the row or column is hidden because the row numbers or the column headers will no longer be sequential, e.g.: 1,2,5,6,7. You can unhide the rows or columns at will to display them. This is particularly useful if you have rows or columns that are there for calculation or functions, but do not add value to the data itself. Hiding rows and columns can reduce the amount of screen space the spreadsheet uses, making it easier to work with.
So you have a choice of how to solve the problem of too much data and too little screen real-estate by choosing any or all of these solutions.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets, business information models and professional PowerPoint presentations.
Website: www.easydatasolutions.co.il Email: in**@******************co.il
Information is power
It’s been a while since I have posted a blog to Jobshuk, but there is good reason as to why it has been so long. I have just completed 6 months of army service but I have now settled back into civilian life and I have re-opened my business, Easy Data Solutions. I had the privilege of serving in the Combat Engineering core in which I was part of a unit that served way down South on the Egyptian border, about one hour North of Eilat. Our unit was responsible for guarding 32km of the border which included patrols, guarding from guard towers and camping out overnight in strategic areas along the border waiting in ambush for arms smugglers and any terrorist activity.
It was relatively quiet in our territory during my service and for good reason. The army command received daily updates on potential terrorist activity that was likely to occur in its area and, accordingly, designed its strategy to prevent the activity from occurring. For example, information was received on one occasion that pointed to an arms smuggler who was expected, at a very particular location along the border, to make a “transaction” in the coming week. In response, our unit increased the number of patrols around that location and set up ambushes in the event that the “transaction” occurred.
Well, the “transaction” never occurred, and it was because both parties, that is, the army and the would-be arms-smugglers, used the power of information. The army acted on its tip-off in the correct fashion and ultimately prevented any arms being smuggled into the country during that week. It achieved its desired goal, although the smuggler was not detained. The smuggler, however, also collected information and used it to his benefit. Having identified that there was increased activity at his chosen location where he was to carry out his “transaction”, he aborted his mission. Although he did not succeed in achieving his end goal, he was certainly in a better position than if he had attempted to make the “transaction”. The information he gathered saved his merchandise and probably his life too.
Information is power, but only if you use it correctly and to your advantage. Every business has some type of data feeding into it and going out of it. Customer information, sales data, staff performance, profits and losses, cost ratios, economic factors etc. The list goes on. But this information has no value if those involved in making decisions about the business do not use it correctly. Your business information tells a story about your business and you can only write the script of where you are headed if you analyse and understand the information your business is providing you. Ignoring relevant data will not help you achieve the best outcomes for your business, rather it may prevent you from achieving your goals. Successful businesses act on the information they have.
During the many arduous hours of guard duty way down in the middle of the desert, I had time to ponder my own business. It soon became evident to me that I could not wait to get back into it and to really get it up and running. I recalled that during the short time that my business was in operation before I enlisted in the army, I was able to gather important information. The information pointed to the fact that there is real opportunity to make the business a success, and if I harness this information correctly, anything is possible. I hope that this becomes a reality.
For assistance in building business and financial data management models to help you better understand your business information, or for assistance in improving existing spreadsheets, reports or presentations that you are already using, visit the Easy Data Solutions website, or email in**@******************co.il to arrange a free consultation.
Excel Tips #4: Filtering your business data for efficient analysis
This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
One of the easiest ways to find pertinent information in your Excel spreadsheet is to use the sort function. Sorting allows you to display the data in alphabetical or numerical order. While this is an easy solution for some tasks, it is clumsy for most. For example, what if you want to find all transactions in September? If you sort by the month column, you then have to scroll down (or up) to find the data for September. Not very efficient.
But there is a simpler way: filtering.
The filtering feature allows you to sort and group the data in a column to only show that which is relevant to your data analysis. When you click on the down-arrow that appears at the top of the column (when a filter is available), a list appears showing all unique values in that column. You can then select an entry on the list and the column will then display only those records. This is a filter.
Using the above example, let’s say you have a spreadsheet showing bank transactions during the last year and you only want to show data for September. Simply apply an auto filter to your range of data, click on the down arrow at the top of the month column and select September. Only data from that month will display. You can easily show all records again by clicking on “All”.
At the basic level, filtering is handy, but limited in functionality. While you can combine filters from more than one column to even further limit the data displayed, it is difficult to select multiple entries from one filter list like you can in a Pivot Table. To do this using the filter function you may need to set a custom filter (which can be found as an option in the auto filter drop-downs), or even an advanced filter which is a more complex task for more advanced users. Also, it is difficult to tell when your data is filtered or when it is showing all records. Some versions of Excel will color the arrow of the filtered column in blue to indicate when the data is filtered, but this is not always easily visible especially when you have many columns and headings in your range. So remember to select the “All” option to show all records when you are done with the filter, so that when you return to the data all of your business information is immediately available.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il Email: in**@******************co.il
Excel Tips #3: Formatting, Conditionally
This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
Let’s say that you have a very complicated spreadsheet, or that you rely on your spreadsheet to give you information that will determine what steps you take next. Now let’s say that the rows and rows of numbers don’t actually make it easy for you to see the information that you really need to see. Why not use Conditional Formatting to highlight problem areas or important data?
Conditional Formatting. It is just that. Tell Excel to format cells in your spreadsheet according to the conditions you set forth.
If your spreadsheet is dynamic, that is, the data is constantly changing or being updated, you can set it to highlight the interesting bits with colors or other types of text formatting (bold, underline, font size etc). The cell range covered by the conditions you set will update automatically as your data changes and meets the conditional criteria. For example, if my spreadsheet tracks inventory and I want to know when the number of items left in a particular line of stock reaches, say, 100 units, I can set Conditional Formatting to color that cell in red. This is a particular handy trick if you need to find many types of the same information in a large spreadsheet (e.g.: highlight all cells where the value is zero; where the salesman is Joe; or where the customer is more than 90 days past due on his payments). The function is flexible in that it not only allows for conditional formatting based on the actual cell value, but also based on the formula contained within the cell. Another ‘plus’ is that the interface for this function is user friendly and easy even for beginners to understand.
Conditional Formatting is a very useful feature but is also limited to the choices provided by Excel. For instance, in most of the earlier versions of Excel, the number of Conditional Formatting arguments you can create is restricted. Further, Conditional Formatting is not effective when two arguments overlap. For example, if you ask Excel to color all cells with the value 1 in blue and then create another Conditional Formatting argument to color all cells with a value less than 3 in green, Excel will apply the first argument to the cells containing a number 1 and it will apply the second argument to cells containing the number 2. The first argument will always prevail. Another limitation lies in the fact that Excel has a list of conditions you can set and, although they meet most requirements most of the time, there may be occasions when it just doesn’t do what you would want it to do.
Despite all this, Conditional Formatting is a great way to easily highlight important information in your spreadsheet and can actually be a very useful tool.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il
Email: in**@******************co.il
Excel Tips #2: Easy Navigation in Excel Workbooks
This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
Excel provides the user with the option to create an almost unlimited number of tabs in a workbook. This is great for enabling the user to spread out the data among different sheets, allowing for a more organized workbook. In a simple example, one sheet might contain the raw data, another might contain a chart or a graph, another might contain a series of lists, and so on and so forth.
The beauty of this system is that all your information is not concentrated on one sheet, making it much easier to identify different parts of the information you have stored in the workbook. You can even rename the tabs to suitably represent what is contained within.
The downside of having multiple tabs, though, is that you might find yourself searching the tab labels each time you want to switch from one tab to another. Lets say you updated your source data and now you want to check your pivot table to see if it looks correct. You might just end up scrolling from side to side searching for the Pivot Table tab. As the creator of this workbook, you may know where to find the relevant tab, but your user (or more importantly, your boss) may end up frustrated at not being able to find what he wants.
Fear not, a solution is at hand. There are a number of ways to make your Excel workbook more navigable. You could start by renaming your spreadsheet tabs to something that appropriately represents what is in each sheet, or you can rearrange the tabs so that related tabs appear next to each other. In Excel 2003 and later versions it is even possible to color-code the tabs to make it easier to find what you are looking for. Additionally, right clicking on the arrows to the left of the tabs displays a list of all of your tabs giving you yet another way to easily navigate to the desired worksheet in your workbook. But there is a more elegant method. Read on.
A nice feature Excel provides is the ability to make your workbook look more like a computer program than just a series of spreadsheets lumped together in one place. You can create buttons and hyperlinks that will zap you across from one spreadsheet in your workbook to another. You can even create links to other Excel spreadsheets or, in fact, any other document or web site that takes your fancy.
Taking this whole thing one step further, why not create a "home page" in your Excel workbook containing buttons and links to all of the relevant pages in the workbook. This way you can make the workbook behave like a computer program, allowing you to click buttons and skip from one page to another. Not only is it impressive, but it is also an efficient method of finding your way around. Another advantage of creating a system of buttons and links is that you can ensure that users only view the pages that they should see. You probably don’t want them viewing the masses of raw data or your pages that are still under development. Utilize the "hide tabs" feature and you can really make sure that the user is directed to only the pages you want them to see.
Think of your spreadsheet as an end-user tool. Create buttons and links to build a real package that enables you to more easily find what you are looking for.
If you would like to know what the real status of your business is and what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il
Email in**@******************co.il
Excel Tips #1: Pivot Tables – Feel the Power of Automation!
This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
Most people, even true beginners, know the very basics of how to type in data into an Excel spreadsheet. After all, an Excel spreadsheet is really just an electronic version of the old-fashioned bookkeeping grid.
While the horizontal and vertical lines may appear familiar, Excel’s automated features give that grid a whole new meaning – a power beyond simply adding, subtracting and multiplying numbers. The Pivot Table is one such example and is today’s topic of discussion.
The Pivot Table is probably one of the easiest ways to automate the presentation of ranges of numbers in an Excel spreadsheet. Put simply, the Pivot Table is an automated summary of whatever information appears elsewhere in the Excel Workbook. For example, let’s say that you have used Excel* to track sales figures for any number of products over the last financial year. The massive amount of data might be impressive, but it is probably very difficult to get to the bottom-line figures of how much was sold per product per month. A Pivot Table would be a perfect way to automatically boil it all down to a single bottom-line figure.
(*Just so you know, there are also ways to extract data from your existing MS Access databases into Excel or other database platforms like SQL, so that you can use Excel’s number-crunching expertise (such as Pivot Tables) to figure out the statistics you need to know).
By default, Pivot Tables are presented in a grid, with row and/or column headings around the edges and the data in the middle. The default setting is also to display row and column totals, although this, like most things, can be changed. Most of the way the Pivot Table looks is customizable, although there are certain limitations to what you can actually do.
The type of data that the Pivot Table presents all depends on what information you need to squeeze out of your spreadsheet. For example, you could get the Pivot Table to show you a count of items (e.g.: how many of a product was sold); you could get the Pivot Table to show you a sum of items (e.g.: the total value of the products sold in a particular month); or, say, the average sales per month. Of course, Excel gives you many more options of what type of data you can view and many ways in which to view it.
As with most Excel features, the good-old Pivot Table has its plusses and minuses (pun intended). Pivot Tables give you an easy way to view the data, but because it is an automated feature, there are limitations on what you can do with it. For example, a Pivot Table likes its space. It won’t shift over if the Pivot Table next to it wants to expand, resulting in an error message. Also, when the raw data changes, the Pivot Table doesn’t update itself. You have to refresh it manually – unless, of course, you are running a macro or a script that will update it for you on the fly. On the whole, though, I think that the Pivot Table is a great way to really see what your business is up to – drill down to the information you want to find the answers you need to keep your business on track.
If you would like to know what the real status of your business is and what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il Email in**@******************co.il