Search

Blogs Blogs

Excel Spreadsheet Blogs

Newest Most Active

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.

Excel Spreadsheet Blogs RSS feed