Must have tools for every programmer

 The ability to focus while coding is incredibly important for developers.

To help you maintain your productivity levels high, here are the 18 best programmer productivity tools to try out.

Habitica

Apps that let you create to-do lists help you track the progress with your daily coding tasks, and Habitica takes this to a whole new level.

This app actually treats your daily workload as an RPG game where you can level up by completing your tasks. As you make progress with your work, you also find pets, collect coins and equipment, so there is a reward system to help motivate you to keep pushing forward.

Once you sign in, you first have to create a character. You can then indicate:

• daily tasks – the ones you have to finish every day
• to-dos – the tasks you only have to complete once
• habits – everyday activities you perform, either good or bad. If you pursue your good habits, you gain rewards. If you pursue your bad habits, you lose health and mana

Unless you finish your tasks in a given time period, your character suffers damage.

If you code in a team, you can try Habitica’s guilds and group quests. You can add your entire project activities in a group quest, and link team member’s tasks together. If you procrastinate and stop working on your own tasks, your team member’s characters also take damage, so you’ll learn to be more responsible with your share of the work.

musicForPrograming

The musicforProgramming website is an interesting concept for a music generator meant to increase productivity.

Its interface mimics a code editor; when going to the page to change the track, you’ll be more likely to keep focus on your coding activities, because you’ll visually link this website with your work.

Just, put your headphones on, select one of the 54 playlists available, and then click on “PLAY” to start it.

The playlists vary in length, and most of them are instrumental, which is great, when you consider that one Middle State Tennessee University experiment proved that instrumental music is best to help you focus on work.

Recommendation

Do you find that you work better with some music in the background? Then check out our new infographic that explains how music affects productivity and describes what music genres are best suited for certain types of work:

• How music affects productivity →

 

Clockify

Tracking the time you spend on various programming activities is vital, and there is no better way to do this than turning to efficient and free time tracking software.

Clockify is primarily a work hours tracker that helps you track time you spend on projects, get data that shows how productive you are (weekly, monthly, or yearly), as well as calculate billable hours and payroll.

Tracking time easy and can be done with one click. Just type in what you’re working on, and start the timer. When you click end, the timer adds your coding activities to a list of your time entries.

You can also add time manually and fill timesheet at the end of the day  so you know how much time you’ve spent on each project.

Later, you analyze how much time you’ve spent on each project in reports.

To make work more interesting (and more likely to hold your focus), Clockify can also help you gamify the process of coding. Just set estimates for each task, track time for them, and try to beat the estimated time in a race against the clock.

If you’re a Mac user, you can implement the Pomodoro technique and make use of Clockify’s Pomodoro timer, which you can choose in the apps’ preferences. With this timer, you can set break time (considered important by various studies) and work time, and let the system remind you when it’s time to focus on each.

F.lux

F.lux is a cross-platform that adjusts the color temperature of your display, to help your eyes rest, and ensure you can work on your programming tasks in a quality way, longer.

The program adjusts the colors of your computer screen based on your location and the time of day. According to default settings, when the program detects it’s nearing sunset in your location, the colors become warmer. As sunrise nears, the program returns the display’s colors to usual settings.

F.lux follows the premise that warmer colors help you slip into relax and sleep mode faster, and that light-blue colors improve alertness. To maintain healthy sleeping patterns and productivity at the right times, simply adjust the settings to fit your work routine.

For example, if you work mainly at night, you can adjust the program to apply warmer colors later, and disregard the sunrise/sunset times of your location.

F.lux works on Windows, Linux, Mac, and iOS devices.

Soundtrack.net

Considering that music helps stimulate the brain, listening to music while working is likely to improve your performance. There are also various studies supporting the premise that game soundtracks are perfect for helping you focus on your work, and you can find this type of soundtracks up on Soundtrack.net.

This website is a great resource for soundtracks you can listen to while programming, and it offers a large number of game soundtracks.

It also offers an extensive list of movie and TV show soundtracks (about 30,000 titles in total), and you can listen to music from newly released trailers and teasers.

The Silver Searcher

Considering that a lot of what people call “writing” code is actually “reading” code, or “searching” for a specific code, code-searching tools can help save a significant amount of time.

The Silver Searcher is a code-searching tool, one that stands out due to its speed. Also known as Ag, for short, it’s similar in features to the more famous Ack, but it is about 34x faster than Ack in displaying results.

Another useful feature is that Ag ignores files you indicate, meaning it reports more specific matches, which saves you time even more.

You can install it on Mac, Linux, and Windows.

Intellij IDEA

Intellij IDEA is a Java IDE that offers a number of functionalities meant to assist you in coding across various frameworks and languages.

It provides you with suggestions and quick fixes for code issues. You can make use of its keyboard shortcuts to navigate across items.

For even faster execution, this program offers smart code completion, which occurs after the system analyzes your already finished work.

Intellij IDEA also provides you with various tools and integrations, so you can make use of control systems such as Git, or build tools such as Gradle.

It can also analyze your code across various projects and languages, to find connections between them.

With this Java IDE, you can build tools, decompile, run tests, manage schemes, run queries, and much more.

It works on Linux, Mac, and Windows.

Rainy Mood

If you work in an open office, but find any kind of music distracting to listen to via your headphones, you can try a simple noise generator. Rainy Mood is a straightforward choice, one that offers the soothing sound of falling rain.

This noise generator will provide you with endless sound of rain and thunder, that’ll help you focus, and prevent the office chatter from distracting you. The website also offers a different musician every day you can listen to along with the falling rain, but you can ignore this option.

You can listen to it directly on the website, or try it on Android or iOs devices, which offer additional features. The phone app offers 4 rain scenes you can choose from: Countryside, Ocean, Café, and Classic scene.

MantisBT

Mantis is a web-based bug tracking system. Its main use is to track software defects and report them, but you can configure it so that it works like a project management tool and issue tracking system.

This bug tracking cross-platform sends updates to your team on issues, via email notifications. You can set what kind of issues you want MantisBT to notify you about, in terms of type and seriousness.

It records the changes made to issues, so you can easily track them down for analysis.

If you’re interested in whether issues in the software you’re developing are connected, Mantis provides you with graphs that show relationships between them.

Git

Git is a version control system meant to track changes in computer files, and it’s mainly used in source code management.

Each member of the developer team gets a local copy of the development history, and the system documents and copies all changes across repositories. It operates locally, and one of its main draws is its speed.

The Git feature that stands out the most is its branching model; you can use multiple branches for your ideas, test them, and easily backtrack if they don’t work.

You can use it on Windows, Linux, and Mac.

In addition, you can make use of the Git community, which is a rich resource of ideas, concepts, and useful patches.

Focusmate

If you’re a freelance developer worried that you’re prone to procrastinating during the time periods when you should be coding, you can try Focusmate. This is a coworking virtual community, where you get assigned with a partner who makes sure you focus on your tasks.

This community bases its premise on scientific research, that states there are certain behavioral triggers (such as social pressure and accountability) that ensure people avoid procrastination when working in the company of other people.

Once you sign in, set the time when you’ll want to work, which is, in essence, booking a session. You’ll get an email confirming your session, which is set to last 50 minutes.

When you get in touch with your assigned partner, disclose your task for the day. You can then work quietly, and hold each other accountable that you do stay focused on your tasks.

AutoHotKey

Being a keyboard wizard is essential for any developer, so learning how to use hotkeys for keyboard shortcuts is vital: it automates repetitive actions and saves you a lot of time.

If you’re a Windows user, you can turn to AutoHotKey, a scripting language that helps automate recurring actions you have to undertake in Windows applications.

With it, you can manage files, folders, and windows, and perform various other actions that aid developer productivity.

You can use this scripting language to script keystrokes, mouse clicks, menu selections, which can comprise a combination of actions you can use for Graphic user interface testing, or similar activities.

There are a lot of hotkeys available, but you can start with the basic commands, and work your way up, for even faster performance.

Sublime Text

Every developer aiming at productivity must work in a good code editor, and Sublime Text is a pleasing choice. It is a cross-platform code editor that supports various markup and program languages.

With Sublime Text, you can easily navigate through lines and files, make changes simultaneously to multiple areas of your code, and switch between projects in an instant.

This code editor even offers a distraction-free mode, so you no longer have to rely on your own willpower to avoid distractions when working. This mode shows your editor full screen, with all UI chrome features hidden.

It’s fast in execution, but simple in interface, and you can run it on Windows, Linux, and Mac.

LeechBlock NG

Website blockers are among the best software for developers wishing to avoid procrastination and distractions. This website blocker extension is similar in execution to Google Chrome’s extension StayFocused, only it’s for Firefox.

LeechBlock lets you block Social Media websites or any other time-consuming websites that distract you from your coding. You’ll be able to create up to 6 lists of blocked websites, with specifications on when you’ll want them blocked, and for how long.

You’ll also be able to determine how you’ll want these websites to be blocked:

• at fixed time periods (for example, from 8 am to 4 pm)
• after a certain time limit (for example, after every 15 minutes within a fixed time period)
• for a certain time, within a fixed time period (for example, for 15 minutes every two hours, from 8 am to 4 pm)

Codeanywhere

When developing software in a team, you’ll need a straightforward collaboration program, so you can work on various parts of the code simultaneously. Codeanywhere lets you do just that, as it is a code editor that allows developers to code together in real-time, and finish more in less time.

With this code editor, you can work directly in your browser. Just invite your collaborators, by sending them an URL link of the project; you can determine the level of permissions for each team member, and invite as many programmers as you want.

Choose one of the 75 programming languages, and you’ll get the corresponding syntax highlighting for it. You’ll be able to see the cursors of your colleagues as they move in the editor, so you’ll know where they are at any given time.

Apart from coding in your browser, you can also use Codeanywhere on iOS and Android. You can even link your Codeanywhere account with Dropbox and Google Drive, for an even faster sharing experience.

RescueTime

While Clockify is for tracking work hours, RescueTime is more for tracking personal productivity. It’s an automatic time tracker that measures how much time you spend on various software and websites, without you having to click anything. As such, it’s perfect if you’re interested in how, for how long, and on what websites you procrastinate.

Clockify pro tip

Did you know that procrastination may lead to poorer work performance and may cause health problems, stress, and anxiety?

You can learn more about procrastination, how to fix it, and what methods some of history’s most famous people used to fight it in Clockify’s procrastination guide and infographic:

• → Procrastination: why we love it and how to fix it

You just install it on your computer, and each time you switch from one website or software to another, it automatically starts recording the time you spend on it.

This time data is then stored in the cloud, and you get reports on how much time you spend on time-consuming websites such as Facebook, Twitter, or Instagram.

This app lets you set an alarm that goes off after you’ve spent your limit for the day on a certain website. For example, you can set it so that it warns you when you’ve already spent 2 hours on Instagram.

WakaTime

WakaTime is similar to RescueTime, only it automatically tracks the time you spend programming. Just focus on a code, and the program will track and record your start and stop times.

The program provides you with metrics on the time you spend on a particular file, operating system, branch, and language. You can also make use of the text editors and IDE plugins.

The Leaderboard option offers you a chance to compete with your programmer colleagues. Once you all enter the same Leaderboard, you’ll be able to compare your coding time results and see who has the highest score in terms of:

• Hours you’ve spent coding so far
• Daily average
• The languages you’ve coded in

Considering WakaTime is primarily a coding productivity tracker, and that Clockify is primarily a work hours tracker, you can combine the two, to streamline your coding business. Once you have your WakaTime productivity results, just use this data to calculate payroll and billable hours in Clockify.

Todo.txt

No “best productivity tools for programmers” list would be complete without some sort of task manager. Todo.txt is a simple open-source to-do tool where you can add tasks, view them, and mark as done when finished. It’s low-key and fast, and lets you manage your tasks from the command line or from Todo.txt apps.

This productivity tool doesn’t offer many options when it comes to check-boxes, reminders, date pickers, or drop-downs – but that’s its core asset. You’ll get to enjoy a minimal approach to handling to-do items, one that allows you to handle your tasks in a couple of clicks and keystrokes. In gist, you’ll be able to follow your tasks from creation to completion, highlight priorities, as well as handle projects, and incorporate them in the right context.

The shell script todo.sh allows you to manage and interact with Todo.txt directly from the command line – you’ll also be able to archive and then access completed tasks. Best of all, you’ll be able to browse the source code and make tweaks to this tool, to make it fit your needs better.

Read More

How to make your content go viral

It's not free method but very effective (and very cheap). If you are sharing something interesting on your page it can go viral. But how to start the snowball rolling? This is main problem - initial traffic (boost) for your blog or post. And there comes Social Bookmarking! 

But manual submitting can really be a time-consuming process with you being required to create an account in each site before submitting your page to them by yourself. It's better to outsource this task. What if you could submit your website to over 300 biggest social bookmarking websites for just $5? If your content is interesting your website will go viral easily. Submitting to Social Bookmarking Sites like Digg, Stumbleupon puts your blog/article out there in front of millions of people, increasing your visibility ten notches and getting you more branding. As more and more people read your bookmarked blog/post and vote for it, the more popular it becomes & the traffic to your site increases. Social bookmarking can also bring more additional backlinks to your page. Each of these popular Social Bookmarking sites see tremendous volumes of traffic on a daily basis, if your blog/website has good content on it, chances are that several of these users will link to your content, thus getting you relevant links which will ultimately lead to an increase in your rankings in the google. You can find many social bookmarking services on FIVERR for example:

Here are my traffic stats after purchasing one of FIVERR's social bookmarking gig:

 

So if you think your content is useful, share it with the wolrd and go viral. Sign up to fiverr.

Read More

Excel Formulas: 10 Formulas in excel

Excel Formulas You Should Definitely Know:
1. SUM
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula.

The above shows you different examples. You can have numbers in there separated by commas and it will add them together for you, you can have cell references and as long as there are numbers in those cells it will add them together for you, or you can have a range of cells with a colon in between the 2 cells, and it will add the numbers in all the cells in the range.

2. COUNT
Formula: =COUNT(A1:A10)

The count formula counts the number of cells in a range that have numbers in them.



This formula only works with numbers though:



It only counts the cells where there are numbers.

**Learn more about the COUNT function in this on-demand, online course. FREE preview**

3. COUNTA
Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.

The COUNTA Formula works with all data types.



It counts the number of non-empty cells no matter the data type.

4. LEN
Formula: =LEN(A1)

The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.

len

Notice the difference in the formula results: 10 characters without spaces in between the words, 12 with spaces between the words.

5. TRIM
Formula: =TRIM(A1)

Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.

trim-formula-screenshot (2)

I added in an extra space behind “I Love Excel”. The TRIM formula removes that extra space. Check out the character count difference with and without the TRIM formula.

6. RIGHT, LEFT, MID
Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)

These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.



I used the LEFT formula to get the first word. I had it look in cell A1 and grab only the 1st character from the left. This gave us the word “I” from “I love Excel”

I used the MID formula to get the middle word. I had it look in cell A1, start at character 3, and grab 5 characters after that. This gives us just the word “love” from “I love Excel”

I used the RIGHT formula to get the last word. I had it look at cell A1 and grab the first 6 characters from the right. This gives us “Excel” from “I love Excel”

7. VLOOKUP
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”. (See the full explanation of VLOOKUP) Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).

Note: If at all possible use a number for the lookup_value. This makes it a lot easier to make sure the data you are getting back is a correct match.

If it finds a match of the “lookup_value” in the left column of the “table_array” it will return the value in the column you specify using the “index_num”. The “index_num” is relative to the left most column. So, if you have the table_index look in column A and you want what is returned to be what’s in column B the “index_num” would be 2 because the leftmost column, column A in this case, is the 1st column in the table array and column B is the 2nd column (hence the 2 for the index number). If you want what is in column C to be returned you’d put 3 for the index_num. The “range_lookup” is a TRUE or FALSE value. If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match. I only use FALSE when using the VLOOKUP formula.

Example:

You have 2 lists: 1 with a sales person’s ID and the sales revenue for the quarter. Another with the sales person’s ID and the sales person’s name. You want to match up the sales person’s name to the sales person’s revenue numbers for the quarter. They are all jumbled around so to manually match this, even for a small number of salesmen would leave room for a high margin of error and take a lot of time.

The first list goes from A1 to B13. The 2nd list goes from D1 to E25.

In cell C1 I would put the formula =VLOOKUP(B18, $A$1:$B$13, 2, FALSE)

B18 = the lookup_value (the sales person’s ID. This is a number that appears on both lists.)

$A$1:$B$13 = the “table_array”. This is the area I want the formula to search the leftmost column (column E in this case) for the “lookup_value”. I went to F because if it finds match in column E, I want it to return what’s in column F. (The money signs are there so that the table_array will stay the same no matter where the formula is moved or copied to. This is called an absolute reference.)

2 = the index_num. This tells the formula the number of columns away from the left most column to return in case of match. So, if you find a match between the lookup_value and the leftmost column of the table array, return what’s in the same row in the 2nd column of the table (the 1st column is always the leftmost column. It starts at 1, not 0).

FALSE= tells the formula I want it to only return the value if it’s an exact match.

I would then copy and paste that formula along all the cells in column C next to the first list. This would give me a perfectly aligned list with the sales person’s ID, sales person’s revenue for the quarter, and the sales person’s name.



In order to get a nice neat list of Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each other I used the VLOOKUP formula to compare 1 list to another.

This is a complicated formula, but an extremely useful one. Check out some other examples: Vlookup Example, Microsoft’s Official Example.

**Learn more about the VLOOKUP function in this on-demand, online course. FREE preview**

8. IF Statements
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.

Continuing with the sales example: Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota” (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially when you get to more complicated things like Nested IF Statements in Excel).

It would look like this:

In the example with the VLOOKUP we had the revenue in column B and the person’s name in column C (brought in with the VLOOKUP). We could put their quota in column D and then we’d put the following formula in cell E1:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.



Having the result right there from the IF statement is a lot easier than manually figuring this out.

9. SUMIF, COUNTIF, AVERAGEIF
Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.



I use these formulas in our example to see the average revenue (AVERAGEIF) if a person met their quota, Total revenue (SUMIF) for the just the sales people who met their quota, and the count of sales people who met their quota (COUNTIF)

10. CONCATENATE
A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in cell A1 and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Steve Quatrani”



Finding The Right Excel Formulas For The Job

There are 316 built in functions in Excel. You’re not going to sit there and memorize what all of them do (or at least I hope not!). Luckily Excel has a built in wizard that helps you find the correct formula for what you’re looking to do (if there is one).

Click the “fx” next to the formula bar in Excel



This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the correct excel formula:



I typed in “remove extra spaces” and it returned the TRIM formula that we went over earlier.

More Excel Formulas
There is so much more that I use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR, DAY, etc.), other formulas like AND and OR, along with many others.

The real power comes in combining these functions into complicated excel formulas.

Learn Excel Formulas Today!

Breaking Down Complicated Excel Formulas
=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)), “”)

Do you see formulas like the one above and run away screaming AHHHHHHHHHHHHHHHHHHHHHHHHH!!!
Well I don’t blame ya!

This can be a really intimidating formula even for the most seasoned Excel user.

I have a solution for you that makes it easy to both break down, and build up complicated combinations of Excel formulas but first check out this short video by fellow Udemy trainer, Mynda Treacy, that will reveal tips on how to get inside the mind of an Excel formula:



So What’s Going on With This Excel Formula?
Let’s break down all the Excel functions in it:

IFERROR

TRIM

IF

LEN

VLOOKUP

SUBSTITUTE

I’m not going to go in depth into each formula, but you’ll get the point.

Let’s start with the innermost formula. This is the end result that you are trying to accomplish.

We want to take a phrase (that we get with a VLOOKUP) that has a space in it and SUBSTITUTE that space with nothing. So it will take a phrase like “Excel Formula” and make it “ExcelFormula”.

SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”)

We use the VLOOKUP formula to get the actual phrase we want from a different sheet in the Excel Workbook if it matches what’s in cell F7.

VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)

We use an IF statement to say IF the number of characters (LEN) of what’s returned from the VLOOKUP is above 0, then run the VLOOKUP, otherwise, put nothing here. This way if the VLOOKUP returns nothing, then nothing will happen and this will prevent most errors.

IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)

We then wrap it with the TRIM function which gets rid of all extra spaces, besides 1 space in between words. Sometimes when you’re getting data from a database extra spaces can be added after a word. This can make it really hard to compare data from different sources, so the TRIM function comes in handy a lot!

TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)

Finally we wrap it in the IFERROR formula. This formula will be triggered if the other formulas wind up giving you an error. It could look something like #N/A and that’s pretty ugly. This can happen for legitimate reasons, but you don’t want to hand your boss a sheet full of #N/A symbols.

What happens is IF the combined formulas return any excel error, it will make the cell blank, otherwise it will show the results of the formula.

=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)), “”)

There you have it, this long complicated formula broken down into simple step by step functions.

You can use the same method for building up a long formula

Our goal is to SUBSTITUTE the spaces for nothing so we start with that. We then have to pull it in from another sheet so we use VLOOKUP for that, etc., etc.

You always start with the innermost formula, the end result that you are looking for. Then you use other formulas to help you deal with the different situations you’ll have to deal with working with large datasets.

Resources to Become an Excel Expert:
This article is the tip of the iceberg.  Check out these other resources for more tips.

Resources For Showing Data:
Excel Formulas will help you deal with data, how you present that data is a whole other animal. Check out this awesome Excel Dashboard course if you’re interested in using Excel for reporting.

Resources For Excel Help:
Mr. Excel

No matter how good you get at Excel there is too much for one person to be great at everything Excel can do. The Mr. Excel forums are the place to go for any questions. They have a very active community around that site and you can probably get your excel questions answered very quickly.

OzGrid

There are a ton of examples for how to do just about anything in Excel or VBA on Ozgrid.

Learning Excel Macros and VBA
Formulas are awesome by themselves. Using VBA to create Excel Macros in combination with Excel Formulas is how you become a true Excel expert.

I completely automated a business analyst job I had at a fortune 500 company using these methods.

How I Became the “Excel Expert”
It was my 3rd year in college and I went to intern for an options trading firm. There were some brilliant people at this place and even the janitors knew Excel better than I did. The intern in there before me was a software engineer who had basically written a ton of macros that automated almost every daily task I had to do. Great! I thought, I’d have the easiest time doing the daily boring tasks and I could spend time learning how to be a trader.

Well, the week after he left, EVERYTHING BROKE. None of the macros were working and they all depended on each other. “I was never trained on this” I thought, because the software engineer had only told me what buttons to push and when, never how to do it manually. I started giving out wrong information, needing to ask busy people to take time to help me, and was just generally helpless. I did the best I could, but I didn’t even know most excel formulas, much  less how to read and write VBA code and create Excel Macros.

Necessity, the Mother of Invention
Then one day I was taken aside and my manager said, “I know the kid before you did all this stuff, and you weren’t brought here to write code, but if you can’t fix it and start giving us the correct information on time, we’re going to have to go in a different direction.” WHAT?! I thought. I hadn’t been prepared or trained for this, and code that another person wrote is not working, and I’m going to get fired for it? I knew he meant it. I had seen interns get fired from here, it wasn’t pretty. It was an open trading floor, you didn’t get pulled aside into someone’s office, you were unceremoniously escorted off the floor and everyone knew what happened.

So, I had a goal to meet, and I had to meet it quickly. I set off to learn VBA, Excel Formulas, and how to get all these Excel macros working. After about 2 weeks of waking up at 5:30am, struggling through the day with the help of others, then teaching myself Excel until about 2am, I had learned enough to read the code that was written. After about a month I became proficient at it. After about 6 months I knew enough to write any Excel formula I wanted, read & write VBA code, and solve any Excel problem I had without doing manual work in Excel.

Learning it was a pain in the butt! There was no single resource to learn what I needed to learn. I had to go all over the web to find things or ask questions on Mr. Excel. That’s why I created a course on learning Excel. I put all my knowledge gained over the last 5 years, all my tips & tricks into this Excel VBA tutorial.

About the Author:


Steve Quatrani is an excel expert with years of experience using and teaching Excel. He has created advanced systems using VBA code, advanced dashboards using Excel and other business intelligence software, and he continues to build new ways of viewing and analyzing data in Excel and automating those processes. He teaches Microsoft Excel VBA Video Tutorial on Udemy.

From Steve:

I was astounded by how little some people knew about Excel and they use it every day! I decided to start teaching people how to use Excel and utilize the true power of this program to make their lives easier. Throughout my video courses I teach you everything you need to know to become the Excel expert, to spend less time doing stuff manually Excel, and to make your life a whole lot easier all using Excel.

Read More

make money with yllix ads

join by clicking here
 

Make Money With Yllix.com. Yllix.com is one of the best advertising media site like as Google AdSense. Yllix.com is familiar by the name of YLLIX Media. This site is very new, but looks very professional, any one can use this site for money making online. Use Yllix.com site and boost up your online earning very quickly. Actually this site is the best alternative of google Adsence. Yllix.com inaugurate there advertising business in October 2012. You can use this site as a publisher and also Advertiser.

Yllix Media Review:

Yllix Media is an another advertising option as publisher or advertiser like as Google Adsense. You can use this advertising media site with all kind of standard Advertising setups like as Banner Ad, PopUp Ad, Layer Ad, Redirect Ad, Prepop Ad, Full page Ad in size like as 300x250, 728x90, 468x60, 120x600, 160x600. The great news for Publisher and advertiser isyllix.com or Yllix Media site allow any kind of website, Blog, forum or any other .

Yllix media publisher review. Yllix.com publisher area is very popular for website, Blog owner. Any website or blog owner can use this site totally free and make money very simply to publishing there ads. Yllix media is a legit publisher network in this world, Personally I use this site and make some handsome cash in several time. Yllix Media functions are very impressive for publishers who approved to show Yllix Premium advertising which has over $0.10 CPM price for all kind of small or big publishers with free hosted blog or Websites.

How much Pay Yllix media per visitor:

Yllix media pay per visitor is not fixed, It's totally fluctuated and depends on visitors country. If you have quality traffic like as USA or European country based traffic then you can better earning. 

Yllix.com is Legit or Scam:

Yllix.com is a legit site, no doubt about this. I personally use this site, so I confirm you 100% this is trustworthy and reliable site. Any one can use this site with 100% confidence.

 

Yllix.com Payment System:

Yllix.com support very popular and useful payment system like as Paypal and Payza system, I personally use Payza for withdrawing cash. You can cash out this when your income reach in $1 USD by Paypal or Payza.

Read More