It’s been quite a while since I had the time to share some tips with you, my dedicated readers. Tech news was a little slow this week so what we have here are some tips for Excel Power Users and future Excel Power Users.
So here we go – try these tips out and give Excel a workout.
1. Use a shortcut to create a table. Tables are among the most useful features in Excel for data that is in contiguous columns and rows. Tables make it easier to sort, filter and visualize, as well as add new rows that maintain the same formatting as the rows above them. In addition, if you make charts from your data, using a table means the chart will automatically update if you add new rows.
If you’ve been creating tables from your data by going to the Excel ribbon, clicking Insert and then Table, there’s an easy keyboard shortcut: After first selecting all your data with Ctrl-A (command-shift-spacebar for Mac), turn it into a table with Ctrl-T (command-T on Mac).
Bonus tip: Make sure to rename your table to something related to your specific data, instead of leaving the default titles Table1 or Table2. Your future self will thank you if you need to access that information from a new, more complex workbook.
2. Add a summary row to a table. You can add a summary row to a table in the Design ribbon on Windows or the Table ribbon on a Mac by checking “Total Row.” Although it’s called Total Row, you can select from a variety of summary statistics, not just a total sum: count, standard deviation, average and more.
While you could certainly insert this information into a spreadsheet manually with a formula, putting the info in a Total Row means it’s “attached” to your table but will stay in the bottom row regardless of how you then might choose to sort your table data. This can be quite handy if you’re doing a lot of data exploration.
Note that you’ll need to create a total row for each column individually; creating a sum for one column won’t automatically generate sums for the rest of your table (since not all columns may have the same type of data — a sum for a column of dates wouldn’t make much sense, for example).
3. Easily select columns and rows. If your data is in a table and you need to refer to an entire column in a new formula, click on the column name. That will give a reference to the full column by name — useful if you later add more rows to the table, because you won’t have to readjust a more specific reference such as B2:B194.
Note: It’s important to make sure your cursor looks like a down arrow before you click on the column name. If your cursor looks like a cross when you do so, you’ll get a reference to just that lone cell, not to the whole column.
If you want to select an entire column that’s not in a table with just the cells that have data in them, put your cursor in a column next to it, hit Ctrl-down arrow, use the right or left arrow key to move to your desired column, and then hit Ctrl-Shift-up (use command instead of Ctrl on a Mac). This can be handy if your data column is quite long.
4. Filter table data with slicers. Excel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering. However, trying to filter data with that small drop-down when you’ve got a large number of items can be somewhat cumbersome. Several of the presenters at the Data Insights Summit suggest using slicers instead.
5. Create a summary cell that changes when you filter a table. If you create a cell outside a table that summarizes data within a table — the sum of a column, for example — and you’d like that cell to display an updated sum if you filter the table by something, a basic SUM formula won’t work.
AGGREGATE requires three arguments: A function number, a desired option number and the range of cells you want to operate on. Type =AGGREGATE( in Excel for Windows and you’ll see the available functions and options; in Excel for Mac, you’ll have to click on the AGGREGATE help function in order to see available function and option numbers.
SUM is function number 9; ignore hidden rows is option 5. So, a cell with the following code:
gives you the sum of all visible rows only. If a filter changes which rows are visible, your sum will change accordingly.
6. Sort data in a pivot table. Sometimes you’d like to sort data by a specific column in a pivot table — just as with a regular table. But unlike regular tables, pivot tables don’t have dropdown menus on each column offering the ability to sort. However, if you choose the lone dropdown arrow on the first column, you’ll get a menu allowing you to sort by any column.
7. “Unpivot” data. Some call this reshaping data from “wide” to “long”. In the database world, it’s known as “fold”: Taking data from individual columns and moving them into rows. Basically, it’s the opposite of creating a pivot table — in a pivot table, you pull categories within one column up into their own columns.
To unpivot columns, you need to use the Query Editor in Excel 2016. Access the Query Editor via the Data ribbon: In the Get & Transform section, choose From Table.
Once the Query Editor comes up (if your data isn’t already in a table, you’ll be asked to confirm a data range first), select the columns you want to unpivot, click on the Transform tab and chose Unpivot Columns.
That will create two new columns at the right of your spreadsheet, Attribute and Value, with the columns you unpivoted. You can rename those columns to something that makes more sense, such as “Product” and “Price” or “Quarter” and “Revenue.”
To save your work, select File > Close & Load (to the default destination) or File > Close & Load To in order to be asked where you’d like to save your results. If you try to close without saving, you’ll be asked whether you want to keep your changes; say Yes and they’ll be saved on a new worksheet.
8. Make multiple pivot tables for one column of categories. If you have a pivot table and add a filter for one column that contains categories, you can generate copies of that pivot table, one for each category in your filter, by going to Analyze > Options > Show Report Filter Pages and then selecting the filter you want. This can be handier than having to click through each category in your filter manually.
(On Excel 2016 for Mac, go to the PivotTable Analyze tab on the Ribbon and choose Options > Show Report Filter Pages.)
9. Look up data with INDEX MATCH. While VLOOKUP is a popular way to find data in one Excel table and insert it into another, INDEX combined with MATCH can be more powerful and flexible. Here’s how to use them.
Let’s say you have a lookup table where column A has computer model names, column B has price information, and column D also the name of a computer model where you want to add price info. Create a formula using this format:
This is how/why INDEX MATCH works (if you don’t need to know, skip to the next tip): INDEX selects a specific cell by numerical location. You first give it a range of cells, either within a single column or a single row, and then tell it the specific number of the cell you want.
For example, you could pick the 6th item in column B with:
However, using INDEX alone isn’t much help if you want to find a value based on some condition in another column. That is, you don’t want the 6th item in your Price column B; you want the item in your Price column that matches something in column A, such as a certain computer model.
That’s where MATCH comes in. MATCH searches for a value in a range of cells and returns the location of what’s matched, using the following format:
(Match type can either be 0 for exactly equal, 1 for largest value less than or equal to what you’re searching for or -1 for the smallest value that is greater than or equal to your lookup value.)
So, if you wanted to find the location of a cell in column B that was exactly 999, you could use:
=MATCH(999, B2:B79, 0).
And, so the combination: MATCH, looking for a specific value based on a search term, returns a cell location; and INDEX needs a location as its second formula argument.
0. Watch a formula be evaluated step by step (for Windows only). Have a complicated formula? If you want to see how it gets evaluated, go to Formulas > Evaluate Formula to see the calculations run step by step.
11. Import and refresh data from the Web into Excel.This works best when you’ve got well-formatted HTML tables on a Web page; with more free-form text (or even poorly formatted tables), you’ll need to do a fair amount of additional editing to get your data into a form you can analyze.
With that warning in mind, if you want to pull an HTML table from the Web into Excel, head to the Data tab on Excel for Windows and select: New Query > From Other Sources > From Web
Enter the URL of the appropriate Web page. Excel will look for and list available HTML tables on that page. Click on a table to see a preview; when you find the one you want, click Load.
Why not just copy and paste a well-formatted HTML table into Excel? If the data updates frequently, you can easily refresh it by right-clicking in the table and selecting Refresh instead of having to copy and paste new data.
It’s been a little while since we shared some tech tips with you, our dedicated readers so we decided to share some Excel knowledge with you today.
Microsoft Excel is perhaps the greatest piece of software ever written. It is certainly the most widely used software wherever you go.
Here are a couple tips and tricks to help you look like the Excel Wizard you know you want to be.
If you want to show off let your co-workers watch you as you manually enter Excel formulas. Here are some cool tricks of the trade when working with formulas.
Manually Entering Formulas – the Long & Short of It
Long Lists: =SUM(B4:B13)
Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.
Getting to Know the Insert Function Button
Using the Insert Function button found under the Formulas tab, you can select a function from Excel’s menu list:
=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).
=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).
Saving Time with the Recently Used Button
Click the Recently Used button and, as the name suggests, it’ll show you functions you’ve used recently. It’s a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.
=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
Saving Even More Time with the AutoSum Button
What’s cool here is how fast the AutoSum function is.
You select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:
=MAX(B4:B13) returns the highest value in the list.
=MIN(B4:B13) returns the lowest value in the list.
If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.
Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.
Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.
Formulas to Help with Daily Tasks
1. = DAYS
This is a handy formula to calculate the number of days between two dates (so there’s no worries about how many days are in each month of the range).
Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days
2. = NETWORKDAYS
This similar formula calculates the number of workdays (i.e., a five-day workweek) within a specified timeframe. It also includes an option to subtract the holidays from the total, but this must be entered as a range of dates.
Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days
3. = TRIM
TRIM is a lifesaver if you’re always importing or pasting text into Excel (such as from a database, website, word processing software, or other text-based program). So often, the imported text is filled with extra spaces scattered throughout the list. TRIM removes the extra spaces in seconds. In this case, just enter the formula once, then copy it down to the end of the list.
Example: =TRIM plus the cell address inside parenthesis.
This is another keeper if you import a lot of data into Excel. This formula joins (or merges) the contents of two fields/cells into one. For example: In databases, dates, times, phone numbers, and other multiple data records are often entered in separate fields, which is a real inconvenience. To add spaces between words or punctuation between fields, just surround this data with quotation marks.
Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where month, day, and year are cell addresses and the info inside the quotation marks is actually a space and a comma.
Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)
Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)
DATEVALUE converts the above formula into an Excel date, which is necessary if you plan to use this date for calculations. This one is easy: Select DATEVALUE from the formula list. Click the Date_Text field in the dialog box, click the corresponding cell on the spreadsheet, then click OK, and copy down. The results are Excel serial numbers, so you must choose Format>Format Cells>Number>Date, and then select a format from the list.
Have you ever created two or more charts on a worksheet? If you have your probably know how tricky it can be to align them and make them all the same size. Having multiple charts on a report can confuse the reader and may emphize one chart over the other. This is not good, unless of course you intend to do this.
Here’s the easy way to balance your charts out. Click on the first chart to select it, then hold down the Ctrl key and click on the other charts that you want to align with each other. When all the charts you want to align are selected, right-click on any one of them and choose Size and Properties. This opens the Format Shape dialog, and the measurements that you enter in the dialogue will be applied to all the selected charts. After making the charts the same size, go to the Drawing Tools tab and click on Format. Use the Align dropdown menu on the ribbon to align the selected charts and to distribute them evenly either horizontally or vertically.
When sending Excel files to co-workers or associates you should be aware that the recipient will able to change the document. Even if you take the time to save the document with a password, the recipient can save the document with a new name, and then modify the document. Now, if you are aware of this that’s no problem. However if you want to protect the document, save the spreadsheet as a PDF. By doing this you will be protection our work from unauthorized changes.
A new feature of Microsoft Excel 2007 (with Microsoft Office Service Pack 2 installed) is the ability to create and mail Acrobat Reader PDF files. If you do not wish to install Microsoft Office SP2, you can install just the add-in. You can download it here : 2007 Microsoft Office Add-in: Microsoft Save as PDF
After the add-in is installed you can use the code below or do a manual Save As PDF. Office Button >Save As ….PDF Office Button >Send ….PDF
Note: In Excel 2010 the big round Office Button is replaced by File
Tips / warnings :
1) If you have also installed Acrobat Reader you can change OpenAfterPublish in the code to True to open the PDF file after you create it. 2) The mail code example is not working with Outlook Express or Windows Mail. 3) If you set OpenAfterPublish in the code to True then you can do a manual send in Acrobat Reader (also with Outlook Express or Windows Mail). 4) If there is no printer installed the add-in will not work. You only have to install a printer driver of one of the printers in the default printer list, you not need a real printer to use the add-in. 5) When you use a hyperlink to another place in the workbook or if you use the Hyperlink worksheet function the hyperlinks are not working in the PDF.
If your workstation still have Microsoft Office 2003 and you would like the upgrade to 2007 please create a Track-It work order requesting an upgrade.