# How do I plot data from excel file with legends picking the column heading?

I have my data on an excel file (with five columns and 15 rows) and would like to import the file into mathematica and then: (a). Plot column 1 against column 3 (b). Plot column 1 against column 2, 3, 4 and 5 (multiple curves) However, I would like the legend labels to be picked directly form the column heading (y1, y2, y3, y4). I would appreciate comments and suggestions.

To create an excel file with 5 columns and 15 rows:

headers = ToString /@ {y1, y2, y3, y4, y5};
values = SortBy[RandomInteger[99, {15, 5}], First];


Import the excel file and take its first part:

importeddata = Import["example.xlsx"][[1]];
Grid[importeddata]


ListPlot[importeddata[[2 ;;, {1, 3}]] , Joined -> True,
PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {3}]]]]


ListPlot[importeddata[[2 ;;, {1, #}]] & /@ {2, 3, 4, 5}, Joined -> True,
PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {2, 3, 4, 5}]]]]


Or define a function that takes as input a dataset and columns to plot:

lpF[data_, cols_] := ListPlot[data[[2 ;;, {1, #}]] & /@ cols, Joined -> True,
PlotMarkers -> Automatic, PlotLegends -> LineLegend[data[[1, cols]]]];

lpF[importeddata, {2, 3, 5}]


Update: As noted by @rcollyer in the comments, in version 10 and later versions, you can transform your data using

transformF[data_, cols_]:=<|Map[#[[1, 2]]->#[[2 ;;]]&]@ Map[data[[All, {1, #}]] &]@cols|>;


and get the same result without having to use the option PlotLegends:

ListPlot[transformF[importeddata, {2,3,5}], Joined->True, PlotMarkers->Automatic]

• There are several simplifications that can be had. Don't bother with LineLegend, it's the default, use data[[1, cols]] directly. Better yet, convert your data to this form <|Map[#[[1, 2]] -> #[[2 ;;]] &]@ Map[data[[All, {1, #}]] &]@cols|>, then you don't need to specify PlotLegends at all. :) Commented Mar 16, 2017 at 13:38
• @rcollyer, thank you. In version 9, using data[[1, cols]] without the LineLegend wrapper gives only the markers without the lines in legend.
– kglr
Commented Mar 17, 2017 at 8:07
• In v9, it looks like it isn't picking up the Joined -> True which it does in later versions, but you can swap in ListLinePlot and the default legend is LineLegend. So, the list form would work. Also, if you are using v9, neither Associations nor the operator form of Map are present. So, transformF won't work unless you have v10. Commented Mar 17, 2017 at 12:23
• Joined was formerly PlotJoined in earlier versions Commented Mar 17, 2017 at 14:33
• @george2079 in v9, where PlotLegends was implemented, PlotJoined is no longer listed among the options for ListPlot nor does it work. Although, it was still listed as a symbol. Commented Mar 17, 2017 at 19:47

If I understand the question he has an "x" column and a bunch of "y" columns:

make an example:

header = {"x", "sin", "cos"};
x = Range[0, 2 Pi, 1/100];
values = Transpose@{x, Sin[x], Cos[x]};


{header, data} = Through[{First, Rest}@Import["example.xlsx"][[1]]];
{x, y} = Through[{First, Rest}@Transpose[data]];
ListPlot[Transpose[{x, #}] & /@ y, Joined -> True,


sometimes its convenient to actually copy the x vals and associate with each y column:

ydata = Transpose[{x, #}] & /@ y;

ListPlot[ydata, Joined -> True, PlotLegends -> ynames ]