As well as filters, I really would like to have seen mention of slicers. Especially in connection to charts. Thanks for this very easy to understand tutorial. I am now a convert as I have been embarrassed by not having my pivot tables update with new rows. Nice blog right here! Also your web site so much up fast! What web host are you the use of?
Can I get your associate link to your host? I want my site loaded up as fast as yours lol. How will the structured reference know which column to do the lookup? And, the sign tells the formula to work with the data in the same row as the formula, what if I need to lock the reference absolute, not relative , how do I do that? Well done. I like to believe that I am a power user. It is. One question about create unique list.
Hi Jon, Kindly tell us… How can hide the formula with continue working formula,I meant can not show to other but formulas should work accordingly.. Good morning Jon, Great info on tables. Great job on the presentation. I think I might start to like them now. You are the bomb!
The 100+ Excel Shortcuts You Need to Know, Updated for Windows and Mac
Jon I love your presentations. Much appreciated. Keep up the good work. The 10 Excel Pro Tips Workbook was excellent. I like the way the 10th tip ended. I finally found that cell I kept thinking about from Tip 6. Excellent presentation. Hai Jon Thanks for providing such a useful videos with relevant practice materials..
Introduction to VBA/Excel Programming
Thank you soo much for your hard work to extend our excel knowledge. What a informative video? And small tips shared to avoid simple mistakes one makes while dealing with the data are very helpful. I am working as a financial controller in private sector.
The reports which is usually asked for financial analysis prepared on excel sheet attached with it dashboard summarizing whole information in one simple sheet. My main queries how to convert financial information on the excel sheet prepared to A raw data which helps me out later on preparing creation a pivot table which finally helps me prepare a dashboard to the to management. As you know, financial analysis report prepared in a way that representing the on the column header the year or years multiple periods and on the left side the criteria and core is the financial information figures for a balance sheet and income statement.
I will be so grateful if you send A case study file with editing ability to study on my personal email stated. Hi Raed, Financial statements can be created with pivot tables. I do have a free video series on pivot tables and dashboards that you might enjoy. This allows for more customization in your layout. Wished I had looked at this sooner. Great stuff question do you have anything on tables using VBA code? Thanks Paul! They are a great tool to use with VBA because they create a dynamic range within a sheet that is easy to work with and reference in VBA.
I will try to create some additional training videos on Tables and VBA in the future. I have an Excel workbook with 2 tables. One of the tables contains a vlookup formula. If I try to change the formula, instead of recalculating Excel just displays the formula in the cell that I tried to update. I thought this would then update the full column. Automatic calculation is turned on. Even if I try to manually force the calculation, it still will not update the column. Any advice? Hi Richard, The only thing I can think of is that you have an apostrophe in front of the equal sign.
That will cause the formula text to be displayed in the cell and the formula will not calculate.
Excel Training Courses Online | Excel Exposure | Excel TutorialsExcel Exposure
I hope that helps. Jon: The videos are really helpful and you do a nice job explaining everything with clear and concise direction. However I keep running into this problem when I try to replicate the vlookup example you demonstrate. This changed starting in Excel to use the symbol instead. Sorry, I forgot to mention that in the video. Brilliant video simple informative and practical. Well done I am using all the learning to make life easier and help my work colleagues with data analysis. When should I be using tables vs named ranges in Excel?
Manually Recording a Macro in Excel on Mac
I think tables are a great feature, but I think for large amounts of data k rows , it can be hard to work with in excel. I think it would be beneficial to have a blog post regarding working with large amounts of data in excel. I have been using tables for some time now but I have been manually entering new weekly data from two csv files. I want to build a new set of tables, different layout, using the csv files. It is possible to block copy table data into a structured reference array.
Questions, Is it possible to start with such an array before creating the table? What does the syntax look like to build the array? Hello, I enjoyed your 3 part video on creating a dashboard in excel. I understand slicers and how they can be used to select various pieces of data with one or more of the items selected. But is there a simple way to create a slicer that allows you to input a range of values, either with a text input or a slider you can drag similar to a timeline slicer?
Hi Forrest, One approach is to add an additional column field to your source data that contains the groupings. You can create the results of this column with a formula. Checkout my article on grouping times for some different ways to group the numbers or text.
Once you have a new column with the grouping, you can just create a slicer based on that field. Great question! You can use a IF function to create a formula for that. The main issue is that the header cells store the values as text. We also need to a special trick to make the start and end date cell references absolute with the structured table references.
The formula gets a little long, but is definitely possible. I created a quick sample file that you can download and checkout. Here are links to some other articles that will help. Thank you so much your sample is exactly what I needed. I had got as far as realizing the Date Headers were considered as Text Fields but then gave up and came up with a workaround which was not ideal as it left only half my Table as a table and the rest just data.
I am happy to hear it helped. Thanks for letting me know Jeanette! Spread the word! I have been expanding my skills with tables and this video has been very helpful for periodically re-watching. I am also using pivot tables at work and confused a bit on how to expand the pivot table when copying in new data from one spreadsheet and have the data update in the pivot table. Awesome video Jon. I have learned some tips and tricks today. Time saving and once again thanks for another handy video. Thanks Tom! This tip is for iOS developers who want to reduce the time taken while searching iOS documentation using Xcode.
Documentation under the Xcode organiser window by default shows information on OS X as well. If you are not a Mac developer then you can turn off the documentation related with OS X. Step 1: Launch […]. Good Weather is a neat and simple iPhone app from Fried Cookie that shows weather forecast of a location.
This app also lets users to play little game depending upon the current forecast for that location. The Settings screen allows you to add other places apart from your current location. Also you can […]. Outlook for Mac has a feature that lets user to take backup of the contacts for the configured mail accounts.
Listed below are the step by step instruction for doing the export of the contacts to a text file. Step 1: Launch Outlook for Mac. Step 2: Click File menu and select Export […]. This tutorial is about the steps required increase or decrease auto recover time in Excel for Mac. Auto Recovery Auto Recovery is a feature will automatically try to recover unsaved work during unexpected or accidental shutdown or after crash of the application.