Search

Blogs Blogs

Excel Spreadsheet Blogs

Newest Most Active

The accounting profession, as well as the entire educational community has dramatically changed over the past decade. No longer do university students take home text books to read and pour endless hours of continuous studying. Now all secondary and post-secondary education are on-line based with virtual text books. While printing out your reading materials is admirable, it’s not inexpensive when considering the cost of ink cartridges. Education is no longer viewed as a tangible, physical experience with personal relationships. When I was educated in accounting, we purchased textbooks, brought them back to our dormitories, and read our assigned texts multiple times. Today, students have virtual keys to unlock a database at the publisher’s website and lose retrieval rights at each semester’s end! My generation had professors who used chalk boards and engaged in question & answers with the students. The professors cared because the class’ size were smaller. Today’s... read more

Many students are intimidated from using Excel to enhance their accounting homework. The everyday stress of student life & juggling the limited free time available sometimes leaves less than adequate time to learn the basics of your laptop, recall your passwords, memorize the campus layout, restore laptop connectivity, and yes: become even remotely familiar with Excel software! Frankly, I have had one student claim their professor forbid them from using Excel as a tool! I can’t really understand this because once our students graduate, they will be expected to use Excel on a moment-by-moment basis for their employer’s benefit. Excel based problem solving allows the student to concentrate on the theory behind his/her problem solving and how to sharpen his/her approach as opposed to old fashion pencil pushing - which is so susceptible to unintentional math errors and dull pencils will always need to be re-sharpened! Please contact me for a one-hour... read more

OK, so you’ve been asked to do a Pivot Table for the first time? And just like everyone else, you feel somewhat intimidated; “what’s going to happen to my data if I screw up my Pivot Table?”; “Can I undo my changes?”; “Can I delete my Pivot Table?”; “How long does it take to create a Pivot Table?”; “What is a Pivot Table in the first place?” Let’s give the short answers first: “Nothing”, “Yes”, “Yes”, “30 seconds to an hour or more”, “A summarized view of your data grouped by selected fields”.   And yes, as mentioned in other posts, most job interviews for admin, accounting or office positions where Excel is used heavily will include a question on Pivot Tables – so be prepared! The first thing you need prior to creating a Pivot Table is a data set in rows and columns, and preferably a normalized data set; by normalized, I mean a clean data set where individual records are populated in rows and unique field headers are in columns. Once you start working... read more

Here are a short list of CTRL shortcuts you can use in Excel to speed up your navigation.     CTRL A = SELECT ALL RANGE OR TABLE CTRL C = COPY CTRL X = CUT CTRL V = PASTE CTRL O = OPEN CTRL W = CLOSE CTRL HOME = GOES TO CELL A1 (HOME) CTRL END = GOES TO END (LAST EDITED ROW AND COLUMN of WORKSHEET) CTRL Z = UNDO LAST CTRL Y = REDO LAST CTRL S = SAVE CTRL T = CREATES TABLE FROM RANGE CTRL : = INSERT TIME (NOW) CTRL ; = INSERT DATE (TODAY) CTRL SPACEBAR = SELECT ENTIRE COLUMN SHIFT SPACEBAR = SELECT ENTIRE ROW CTRL PAGEUP = GOES TO PREVIOUS WORKSHEET CTRL PAGEDOWN = GOES TO NEXT WORKSHEET     Do you have one which is not listed? post it below in the comment section :)

While in many tutorial exercises as well as in job interviews, you may be asked to create a VLOOKUP formula, in the real world you will quickly find that INDEX/MATCH is a far better solution. To understand why, let's look at the syntax of each: VLOOKUP(lookup_value, array, col-num, lookup_type) VLOOKUP(what?, where?, which column? , approximate search: yes or no?), just like HLOOKUP, refers to a specific column number (or row) starting from the first column (or row) of your index search (the “where”). Why the first column (or row)? Because this is where Excel will look for your "what" !! This means you always have to include at the very least all columns (or rows) starting from the "what" column (or row) all the way to the column_index (or row_index) number in the "where" range. For example, if a customer discount is in column P, and customer name is in column C, your VLOOKUP function will look something like... read more

If you are working in a Microsoft environment, Excel or Word and on a laptop, you may have been frustrated having to select the Fn key together with the primary F1-F12 keys to run the desired command. While the fix is easy on a Mac, it is not so on a PC. Here are the instructions on how to do this on your PC (Windows 10) 1) In your Search Windows Box, type “UEFI”; this will bring a pop-up option “Change Advanced Startup Options”. 2) Select this option, in the next window select “Advanced Startup” and click on the button “Restart Now” (this will restart your computer) 3) Upon restart select “Advanced Options / UEFI Firmware settings” 4) Click on RESTART 5) Once the computer restarts, you will find a menu with several drop-down lists; a. select “POST behavior - Fn Lock Options”. 6) The default is Lock Mode Disabled / Standard. a. Select “Lock Mode Enable / Secondary” That’s it!! Confirm and Exit and your settings are... read more

Ever had to figure out the total amounts received from all of your eBay sales in a year? Or when you should stock up your inventory for a list of product based on a 90-day cycle? Or simply converting an Excel date serial number to its appropriate date form? Here are a few useful formulas for the next time you encounter these and other similar challenges.   First of all, imagine that you have a list of dates that you want to copy from another document into an Excel spreadsheet. You do your usual right-click + copy (or Ctrl + C), shift over to Excel, and right-click + paste (or Ctrl + V); and behold...a list of serial numbers came into view.   But I copied a list of dates, not serial numbers. You thought to yourself. How do I fix this?   The answer to this is quite simple: Highlight the column where the data resides (i.e. Click on the column header "C" if the serial numbers are pasted into column C), go to the Home... read more

Hi everyone!   I have tutored students who take this class many times.  It is a very confusing class and teaches old concepts rarely used in business anymore, as the internet has changed how we use Excel and other data management tools. Further, it does not teach definitions of terms so it becomes difficult to look for information online.   I wanted to give some definitions here that will help you at least search for help online: In Excel, things that start with = are called "formulas". A bunch of characters together are called a "string". Strings can have spaces and punctuation. If you cut off a piece of a string (which is done using the LEFT, RIGHT, and MID formulas), that is called "parsing a string". An "array" is a list of numbers or cells. Arrays have have a : in them, and mean STARTING CELL:ENDING CELL (like A1:A20). Each formula has a name, and starts with = and the name, then has... read more

In this Blog, you'll learn five Excel tips to improve speed and accuracy: Excel Help Tip #1. Keyboard shortcuts: why breaking up with your mouse might be a good thing to do Excel Help Tip #2. How to wrangle worksheets without frustration Excel Help Tip #3. Ways to type less and get more done with AutoFill, AutoCorrect and Custom Lists Excel Help Tip #4. How to repeat success every time using Templates Excel Help Tip #5. Ways to troubleshoot and dissect formulas to understand what's really going on With this Microsoft Excel Help summary, you can improve your skills, boost productivity and advance your career-no matter which version of Excel you use or your level of experience. I hope this help you, and if you have any question please contact me for more Tips.

The following VBA code can help you to get data from all worksheets of active workbook together into a new single worksheet at the same time.  IMPORTANT:  All of the worksheets must have the same field structure, same column headings and same column order. Here are the steps: 1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the following code in the Module Window. Sub Combine() Dim J As Integer On Error Resume Next Sheets(1).Select Worksheets.Add Sheets(1).Name = "Combined" Sheets(2).Activate Range("A1").EntireRow.Select Selection.Copy Destination:=Sheets(1).Range("A1") For J = 2 To Sheets.Count Sheets(J).Activate Range("A1").Select Selection.CurrentRegion.Select Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select Selection.Copy Destination:=Sheets(1).Range("A65536")... read more

Excel Spreadsheet Blogs RSS feed