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...
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...
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”
Confirm and Exit and your settings are...
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
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...
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.
Dim J As Integer
On Error Resume Next
Sheets(1).Name = "Combined"
For J = 2 To Sheets.Count
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select