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...
The =CONCATENATE(x) function is one of the LEAST heard of functions in all of Excel. It allows you to combine multiple cells into one cell. There is no limit to how many can be combined, so =CONCATENATE(A1," ",B1," ") will combinie A1 with B1, and place a blank space in between both cells - if they are words that require a space in between. Concatenate Away!
Thursday Excel Magic: If you have a data-set that is formatted incorrectly with capitalization, such as ALL CAPS, all lowercase, or MiXeD - You can use the =Proper(x), =UPPER(x) or =lower(x) to correct ANY capitalization issues - instead of manually re-typing
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...
If you are in a profession where you must analyze data to turn it to information; or if you do such at home or at school Excel is the application for you. But sometime trying to learn Excel on your own, even with books, can be difficult. That's why an experienced tutor can be valuable, by offering you real world examples of using Excel, while providing basic and intermediate lessons on the application features.
Understanding the Excel Object Model is fundamental to understanding Excel’s structure and the relationship of these objects to each other. But understanding the tabs/ribbons are key to understanding and knowing where to find and how to apply features, setting, properties to Excel objects.
Formulas are what make spreadsheets worth-while. Formulas and built-in Functions bring Excel alive. When Excel is alive with formulas, functions, and code there are wonders to be observed while creating information from raw data. That is the real purpose of Excel, to store...
You already have some income but it is not enough. So you decide that you need some part time work to supplement your income. How many hours of part time work do you need if they pay you $35.0 an hour? What if your part time work only pays you $20.0? In my two hour specialized Excel class, I will show you power of Excel Data Tools to help you with above scenario and other help that Excel can offer. If there is additional time, we can go over Excel features such as Chart, etc. If you are not part timer, you can still use Excel to help you with personal finance.
Working out your accounting homework in Excel can be frustrating and tedious, especially for students just starting off their accounting coursework. You might think "hey, I can do this faster on paper with my calculator." But if you plan on getting past Financial Accounting, you're going to need to master this essential program, and homework is the best way to practice. Besides that, your professors will prefer it and in the long run your grade with thank you. Here's why:
1.) End of Term Projects
Just about every accounting class I've ever taken has had a hefty term project, usually a set of financial statements, and its always due in Excel/spreadsheet format. While you may be able to get through most of the semester turning in homework problems done in pencil, you don't want to be struggling with both excel and complex calculations when the deadline for your term project is looming and you are crunched for time. Learn it now, and spare yourself the stress...
From business school to being in the corporate world for over 15 years, I've observed that majority of people 'feel' that they should be better with Microsoft Excel but are not. Nevertheless, they never take the time to learn even the basics of this software and are struggling for hours with some simple work that should take them a couple of minutes to complete.
For most people, all it takes is some simple demonstration of the ease of the program and I've seen dramatic improvements in a very short amount of time. If you have ever felt this way, there are ways to get better and more comfortable. I welcome comments and questions regarding getting more comfortable with Excel.
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
If you are taking an online course, or have homework assignments that are on your computer, I have a method where I can view your computer, with your permission, while you are online with me, and see and even control your desktop from my mouse, at no cost to you other than my time.
We can talk over the phone, share desktops, as I can show you things from my desktop too, and get the session moving forward regardless of the distance between us. So if you are taking a vacation or a weekend trip and need some assistance, call me and I'll call you back for a free call, and we can work in this manner. I have done this many times in business and other categories. I haven't done it yet over an iPhone but, that's next I think.
Technology is great isn't it?