About
The focus of this blog is to help people improve their skills with Microsoft Excel. That being said this blog is probably more appropriate for beginning and intermediate level users. Advanced concepts will be covered, but as an extension of a prior foundation of knowledge.
I’m a long-time Windows user, yet Microsoft Excel is not limited to Windows. I now have a MacBook Pro and just started using Microsoft Excel Mac 2008. This will be a whole other type of learning curve for me that I will also share on this blog.
In addition, the spreadsheet experience is expanding to the cloud. Microsoft has entered this market with Office Web Apps so I’ll explore this realm as well.
My Excel Story
I began using spreadsheets in college, 1988 to 1992, where I graduated with a degree in Industrial Engineering. In 1994 I went into the consulting business to help companies improve productivity and reduce waste. Part of my job required using Excel to analyze financial and shop-floor data.
In 1997 I received my first organize training for the Microsoft Office family: Excel, Word, PowerPoint, and Access. It was an extensive set of eight-hour training classes covering beginner, intermediate, and advanced levels. This was when I finally realized the true power of Microsoft Excel.
In 1999 I began using Microsoft Query to pull data out of client systems into Microsoft Access to create productivity reports. I quickly realized that Pivot Tables were an entirely better solution for reporting.
Because of my Excel and Access skills, my job description changed and allowed me to work full-time with clients to find workable solutions for shop-floor productivity and waste reporting.
Needing the skills to create a stand-alone Excel reporting system drove me to learn Microsoft Visual Basic for Applications or VBA. I methodically consumed several books written by John Walkenback on Excel and VBA, which has served me very well. This knowledge has allowed me to expand the type of work I’ve done with Excel.
After years of working with many different clients, I’ve come to realize there are a great many people who use Excel inefficiently and could improve their skills dramatically with just a little bit of organized knowledge.
My goal with this blog is to provide people of all skill levels a resource for how to use Microsoft Excel in this online world. My name is Gregory Ropp and this is my Excel Semi-Pro blog.
{ 30 comments… read them below or add one }
I’M TRYING TO Calculate Hours Between Two Dates and Times in Excel 2007. EVERYTIME I TRY YOUR POSTED SOLUTION I GET AND ERROR AND THE FORMULA RETURNS A RESULT OF 0.
HELP.
THANKS
@Molly I’m not sure which posted solution you’re referring to, I’ve several on this site. Please give me some more info about the two dates and times you’re using to calculate the hours between and I’ll try and figure out what the problem is that you’re having. Knowing which article you are referring to would help as well.
I tried the Calculating hours between 2 dates and times. My dates and times are as follows:
Start Date 2/11/11 Start Time 3:00 p.m. End Date 2/12/11 End Time 8:50 a.m.
2/12/11 8:50 p.m. 2/14/11 10:03 a.m.
2/14/11 10:03 a.m. 2/15/11 3:40 p.m.
2/15/11 3:40 p.m. 2/16/11 3:40 p.m.
I work for a service company and we install equipment that we monitor and I have to calculate the number of hours each day that equipment is on site. So even though 2/13/11 is missing I will still need to add 24 hours for that day.
Please help me???
Assuming you have headings in row 1 and data below, the formula you want for cell E2 is =((C2+D2)-(A2+B2))*24 which takes the end date and time minus the start date and time then multiplies by 24 to get the number of hours.
Here’s a picture of the data with the formula showing in the formula bar.
If you have any trouble here’s a worksheet I created with your numbers that you can download.
Greg,
This is a GREAT blog. Thank you!
I’d like to consider myself to be an Excel whiz since I spent a couple years as an analyst at in investment bank in NYC. I just switched to a Mac and am losing my mind! So much for all those keystrokes that I once new. Can you refer me to a site that lists PC to to Mac Excel shortcuts?
For starters, how can you toggle through tabs quickly on a Mac (no longer Control + page up/down). And what about Control+home which takes you to cell A1?
Thanks for your help!
Saritha
To move between worksheets tabs use Option plus the left or right arrow.
I’m still working on the Home button. As a replacement I use the Control or Command with the up arrow, then the left arrow ( a two-step process).
Thanks for the spreadsheet it’s great!!!
Greg,
Thank you for your blog. It’s been a great help to have an Excel reference now that I’m switching from PC Excel to Mac Excel.
Jimbo
Thanks, I appreciate your comment. I’m just trying to fill the void with some useful information because Excel for Mac is so much different that Excel for Windows. It can be frustrating, perplexing, and downright irritating at times. And the lack of documentation is deplorable.
could I view the display in excel as well as tally
I’m not sure what you mean. The words “display” and “tally” aren’t typically used when describing Excel. If “view the display” means Print Preview and “Tally” means entering data in the spreadsheet, then the answer is no. Otherwise you would have to explain further for me to understand the question. Thanks.
Your posts on Mac for Mac 2011 are big-time helping me transition from Appleworks 6 with my B&B booking system.
Do you have any VBA posts on how to create a custom data forms for booking entry, booking search and tax reports? Would like to create VBA buttons to print a record, an invoice and a tax report. The tax report would be imported to Quicken or TurboTax.
Since there are only 40-60 B&B bookings per year, a high-power database in not required. Yet Bento 4 has too many limitations. Thus I’m working in Excel for Mac 2011 and moving to Lion OS X 10.7.
Have created in Excell 2011, a workbook with a booking & entry worksheet, a database worksheet (standard spreadsheet format), and a reference worksheet (default values, room rate array, bed tax defaults, package special array, etc.).
Since main booking agent is Peggy, my wife, and she does not want use normal Excel actions to enter a booking, create an invoice, or search for bookings, the goal is to develop a simple, standard form with some VBA buttons.
Then she can enter specified data in highlighted cells and with VBA buttons save booking to database worksheet, or do a search, an invoice, or a tax report.
There is a screen shot of the working B&B Booking Entry Form. It’s very similar to the Appleworks 6 booking form—thus reducing her learning curve.
With VBA back in Mac Excel I’m in a steep relearning curve. With a 71 year old brain most of my learning curves are steep now. I was only a very novice VBA user 5-6 years ago.
Since Excel’s data-form apparently requires a physical column selection. This is a complication not desired.
Have you done any posts on using VBA buttons for similar purposes?
Are there any online references, PDFs or e-books?
Since I’ have diminished eyesight, reading physical VBA books or Excel 2011 bibles is not an option.
You’re undertaking sounds ambitious. I have not written any posts using VBA buttons for any similar purposes. The best reference I can think of is Execl 2010 Bible by John Walkenback, which you can buy by visiting my Books on Excel page.
Even though this book is written for the Windows version, it would have most everything you need. Of course any button with VBA in the Mac version can not be the ActiveX variety. So discount anything he has to say with regard to ActiveX controls. The book has a CD which carries the PDF version of the full book.
The regular data entry form in Excel 2011 could be used to enter data, search for records, and filter records. You can record some VBA code to select the data table and bring up the form, then create a form button and link that to the macro. Since you’ve given me any idea for this I’ll write a post on how to do this in the next couple of weeks.
As far as creating a Form from within VBA, this gets more difficult. That’s where the book would come in very handy.
Creating an invoice or a report brings another level of difficulty. Each one would have to be done separately, then automated with VBA, and linked to a button on the form.
Done one step at a time, this is very doable given enough persistence.
Good luck, I hope this helps.
Thank you for this blog! It is really well-done!!
I appreciate the comment.
Greg, I’m a 67 year old owner of a construction company but my background is financial and accounting. I switched to Mac’s in 1988 and never looked back (we have 20+). Excel has been my spreadsheet of choice and, upon upgrading to 2011, found that macros were back! I have spent several hours this weekend automating some reports exported from Quickbooks (for Mac) and google led me to your magnificent Blog!!
Your Blog will be a required bookmark for all my folks who use Excel.
Thank you for the time I saved this weekend because of your clear, concise and intuitive instructions!!!!
It’s always nice to receive positive feedback. Thanks for your encouraging words.
Hi Greg,
Thank you for your Blog, it is great
Just a question, cant we just create something that would subsitute all mac excel shortcuts by the pc shortcuts, like a one time thing and taraaaaa
I am having tough times to google the shortcuts everytime i need one…
My Salutes from Lebanon.
What you suggest would be wonderful, but I’m assuming it only happens in a parallel universe. No rest for us in the real world.
Will you tell me if you have written a book for excel 2011 for mac. I really like excel but am very limited in my knowledge. I have a need for some conditional formatting using colors and letter or punctuation i.e. x or ? If you have book available I would be interested in purchasing it.
Thanks,
Aaron
I have thought frequently about writing a book on Excel, but haven’t the time available, so the answer is no. Sorry!
Hi Gregory!
Congrats for the site and your efforts. It’s a real help.
I am just a beginner in excel, and I really need an advice: I have a app made for filling some postal forms ( here in Romania we have a lot of paperwork to do when we use postal services, especially COD) .
I made this app, and now I need to build a data base off it ; Actually, I need each time I fill the data in my entry spreadsheet, this data to be stored in another sheet, in a new row in the actual table. I know this can be done, but it’s beyond my knowledge, and I could use a little help.
Thanks a lot for your answer, and God Bless
Dan
What you are looking for is some VBA code that does something very specific in Excel. Not something I can impart in a comment. I would recommend that you learn some Visual Basic for Applications (VBA) or what ever language you will be using in your app.
Thanks a lot for your reply
I appreciate you took the time for answering me.
Have a good one
Dan
Hi, I’m just a beginner in excel. I work for a not-for-profit organisation in Sydney Australia helping small businesses network and grow their businesses. We have breakfasts for people to meet each other. There are a minimum of 10 tables (up to 15 tables) and 8 people at each table.
My job is to put people on tables (at the moment done manually) and make sure that:
1. people from the same company are never on the same table
2. every person on a table must be from a different industry and different company
3. One Director must sit on each table.
can you help me with a spreadsheet/formula that will help with this scenario?
Thanks so much! Kind regards, Kim
This sounds like a problem described in a class when I was getting my degree in Industrial Engineering. In fact I’ve heard this problem described more than once. However, I’m not sure what the solution is, nor would I be able to solve it with an Excel spreadsheet at this particular point in time. Sorry I couldn’t be of assistance.
Hi Gregory, I meant to mention that I am on Mac OSX
Hi Gregory,
thanks so much for your reply and letting me know that this problem that you have seen while getting your Industrial Engineering degree can’t be solved with an excel spreadsheet. If you ever find a formula or system that can help, please let me know.
Thanks again and I will be reading your blogs to improve my understanding of Excel!
Kind regards, Kim
Hi Gregory,
I am so glad I found this blog… I use Excel on Windows every day at work with very limited use of the mouse. I never knew the power of excel until I started working for my current employer. They love excel and access and utilize it every hour of our work day. One thing I was told the first day… no mouse use. A year later, I think I am learning to utilize that power myself. Just a few days ago my husband bought Mac Book Pro and I started to work in excel. I was ready to cry, I was so inefficient. And then the “delete” key…!!!! No fun at all to delete cell after cell after cell for hundreds of rows. This is how I found your blog. I will now be visiting it every day!
Thank you,
Anna
Glad you like my blog.