background preloader

The Excel Magician: 70+ Excel Tips and Shortcuts to help you make Excel Magic

The Excel Magician: 70+ Excel Tips and Shortcuts to help you make Excel Magic
Posted by nitzan on Wednesday, November 28th, 2007 Are you working with Excel and want take your Excel skills to the next level? Or do you want to learn Excel and don’t know where to start? Check out these 70+ tips and shortcuts that will help you make Excel Magic. Online tutorials & videos The following online tutorials are mostly free and will teach you quite a bit about Excel. Online introduction to Excel: If you are just starting to use excel, this is the perfect resource for you. Books In order to harness the full power of Excel, shell out a couple of book bucks. General Excel Books Excel Bible 2003 / 2007 version: The “Excel Bible” was written by the renowned Excel expert, John Walkenbach. Excel Tips and case studies Excel case studies: While not for the beginner, this book contains valuable, real-world advice on how to make Fxcel do what you want it to do. Specialized Excel books Forums, News Groups and Mailing Lists Mailing Lists: Wow. Excel Experts Excel Blogs and Tip Sites Excel Tools

220 Excel Tips, Tutorials, Templates & Resources for You Posted on January 20th, 2011 in Charts and Graphs , Learn Excel - 14 comments I have an exciting news & massive post for you. As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates. These 220 tips are broken down in to following areas. Formulas [52 tips] IF Formula & 5 Tips Learn 5 tips on how to use IF formula, CHOOSE Formula in Excel. COUNTIF & SUMIF Formulas Find out how you can sum or count values that meet a criteria. VLOOKUP Formula Lookup formulas are famous for a reason. INDEX + MATCH Formulas While VLOOKUP is awesome, it can-not go left. OFFSET Formula OFFSET formula becomes very important if you ever plan to make an excel dashboard. SUMPRODUCT Formula I just love SUMPRODUCT formula. SUMIFS & COUNTIFS Formulas SUMIFS & COUNTIFS formulas are like Swiss army knifes. ROWS & COLUMNS Formulas Formula for Moving Average Between Formula in Excel 6 VLOOKUP Tips Thank you

Conditional formatting tricks: Sum values in Excel by cell color When you think VBA code is your only hope, try combing built-it conditional formatting tools. You might just find an easier solution than writing code. Conditional formatting is a popular feature and it's powerful when combined with other features. For instance, you can't easily sum values in a range based on a format. For your convenience, a demo Excel worksheet you can use to test this Office tip is provided. Conditional formatting setup We'll apply a conditional format and a filter to a single column of values. The sheet shown below uses a conditional format to highlight any value greater than 4. Select the data range, A2:A7. To filter by the conditional format, you'll need to add a filter to the data range as follows: Select the data and header cell, A1:A7. Before we experiment with the filtering, use any method you like to add a SUM() function to A9. Filters Now, let's filter the data by the conditional format and see what happens to the SUM() function: Also read:

Find & Remove Duplicates - Dedupe Excel Tables / Lists - Exc ( more ... ) Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact Find & Remove Duplicates - Dedupe Excel Tables / Lists Match two tables (lists), compare by columns, find or exclude the matched Example #1 - I have 2 lists of emails. Example #2 - I have 2 lists of mailing addresses. Filter duplicates within one table (list), by key columns or more complex rules Example #3 - I have a list of emails. Example #4 - I have a list of mailing addresses. Example #5 - I have a list of customer transactions, need to find the transaction by each customer. Extract unique values from a random range or areas Example #6 - I need to quickly extract a list of the unique values from one or more selected ranges. Find & Remove Duplicates ( dedupe ) - Example #1 I have 2 lists of emails. - dedupe-email-list-demo.xls (16k) Go to List1. Wizard Step 1 - List1 is automatically selected. Wizard Step 2 - Select List2. Click into the range selector Wizard Step 4 - Select what to show in result

Spreadtweet So, you work at a big corporate, huh? And you're not allowed to use Twitter... Wouldn't it be awesome if there were a Twitter tool that looked just like Excel? Welcome to Spreadtweet. It's Twitter, disguised as a spreadsheet. Sorry, Spreadtweet is currently not working due to the Twitter OAuth API. <p class="work"><table id="messageTable"><tr><td> This application requires the following be installed: <ol><li><a href=" AIR&#8482; Runtime</a></li><li><a href="sampleApplication.air">Sample Application</a></li></ol> Please click on each link in the order above to complete the installation process. You'll need The Adobe AIR runtime for this. I need to give you money for this. Is this legal? I dunno. Loads of stuff. You need a bigger screenshot?

What to do with your USB flash drive: Maintain Windows What do you use your USB flash drive for? How about maintaining a Windows computer? Parted Magic is a nifty partitioning tool that also includes a suite of other useful utilities. In addition to partitioning your hard disk, you can also clone disk partitions, reset your Windows administrator password, and more. In part two of our "What To Do With Your USB Flash Drive" series, we're going to show you how to install Parted Magic so you can run these utilities right from your USB flash drive. Install Parted Magic onto USB flash drive Before we get started, make sure you have an empty USB flash drive of at least 1GB in size Step 1: Download the ISO of Parted Magic and the UNetbootin utility. Step 2: After extracting the Parted Magic ISO from the zip file, run UNetbootin by double-clicking on unetbootin-win-549.exe. Step 3: Change the selection from "Distribution" to "Diskimage" and click the button to browse for the Parted Magic ISO. Run Parted Magic Applications There you go!

How to Create an Excel Dashboard - The Excel Charts Blog The Excel Charts Blog Excel dashboards and executive reports are powerful, fairly easy to design and a great way to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined. And, best of all, you may want to implement it yourself or consider it a prototype and ask IT to implement it. Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it: how to bring data into your spreadsheets;how to manage the data and link it to the dashboard objects, like charts and tables;how to design the dashboard report. Let’s take a look at each of them. How to bring data into your Excel dashboard Yes, Excel is a very flexible tool, but to create an Excel dashboard you can’t just paste some data and add a few charts, can you? Usually, the data should not be entered directly into the spreadsheet. Named Ranges Macros

Two ways to build dynamic charts in Excel Users will appreciate a chart that updates right before their eyes. In Excel 2007 and 2010 it's as easy as creating a table. In earlier versions, you'll need the formula method. If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The table method First, we'll use the table feature, available in Excel 2007 and 2010-you'll be amazed at how simple it is. Click the Insert tab.In the Tables group, click Table.Excel will display the selected range, which you can change. Any chart you build on the table will be dynamic. Select the table.Click the Insert tab.In the Charts group, choose the first 2-D column chart in the Chart dropdown. Now, update the chart by adding values for March and watch the chart update automatically. The dynamic formula method You won't always want to turn your data range into a table. Using our earlier sheet, you'll need five dynamic ranges: one for each series and one for the labels.

SUMIF() and SUMIFS() - The new function in Excel 2007 with Multi SUMIF() is used by many accountants who need to sum information based on a criteria. As an example and account number. The SUMIF() function will look for that specific account number and then sum the requested column. or the contents of an equivalent range of codes in another column, match a particular criteria. You could use SUMIF() to sum all the values in a column that are above a particular value (this example uses a criteria of >1000 which is directly entered. Or, if column A contains a list of dates, you could use SUMIF() to sum all the amounts in column B that are on or after a particular date in column A: This function was designed to work with only a single criteria so, there is a need to total all the amounts between two dates, a single SUMIF() formula wouldnot suffice and you would have to resort to using multiple SUMIF() formulae or an array formula. Which in turn has allowed us to move to Excel 2007 and the new SUMIFS() function. Our two range/criteria pairs are:

Mohawke's Best of the Best Free and Open Source Software Collection: Mac OS X and Windows software Collection Mohawke's Best of the Best Free and Open Source Software Collection from Dark Artistry :: Windows :: Macintosh :: Internet :: Operating Systems :: Games :: Web-Sites :: OSSWIN CD :: For search features you can simply use the search feature in your browser; Ctrl+F or Apple+F - Firefox, or use table toolsIf you need these pages translated check out FoxLingo(This page contains no ads or cookies, but does require a javascript enabled browser for sorting.) Access

Related:  excelvarious stuff 1ms-excel